-
latch free wait event
Hi Folks
I am performing a health check on my database and the latch free event is significant..i investigated for various types of latch misses and the one that is signifcant seems to be cache buffers chain.The oracle docs says i need to find hot blocks..Now how do i find these hot blocks ?
regards
Hrishy
-
The "cache buffer chains" tells you that the application generates/looks for HOT BLOCKS. It is a symptom not a root cause. Many SQL statements content for the same blocks. You need to identify the SQL statements and tune them. Here, statspack can help you to identify TOP 10 SQL.
Tamil
-
Hi Tamil
Thanks for the response..but how do i identify which are the hot blocks..
regards
Hrishy
-
Enable 10046 event with level 12.
All the trace files are in the udump dir. You need to sort the trace files and grep p1 (file number) and p2 (block number).
The command is:
$ cd udump
$ grep 'cache buffers chains' ora_*.trc|sed -e 's/.*p1=/ file /' -e 's/ p2=/ block /' -e 's/ p3.*//' | sort | uniq -c |sort -nr|head 5
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|