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