latch free wait event
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 ?
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.
Thanks for the response..but how do i identify which are the hot blocks..
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