DBA Notes: 2011/10/11

Cheng Li

Buffer Cache Chain (Hot Block)

From AWR reports, we found a huge number of cache buffers chain as following:

                                         Pct    Avg   Wait                 Pct

                                    Get    Get   Slps   Time       NoWait NoWait

Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss

------------------------ -------------- ------ ------ ------ ------------ ------

cache buffer handles          4,563,672    0.0    0.0      0            0    N/A

cache buffers chains      1,304,165,784    0.4    0.0      0   33,177,665    0.0

cache buffers lru chain       6,000,558    0.1    0.0      0   34,014,998    0.0

cache table scan latch           85,013    0.0             0       85,013    0.0

cas latch                            58    0.0             0            0    N/A

The latch: cache buffers chains Oracle metric are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

But, there is no Wait time. So, there is no contention for this latch.

If it is listed in wait event, we can perform. following action to resolve:

1)      Tune SQL for hot block

2)      Change nest loop to hash join

3)      Use ASM instead of MSM

Reference:

http://www.dba-oracle.com/m_latch_cache_buffers_chains.htm

http://www.cernatis.com/index.php?option=com_content&view=article&id=92

select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';

LATCH# NAME GETS MISSES SLEEPS
---------- ------------------------------ ---------- ---------- ----------