oracle数据库latch,关于Oracle数据库latch: cache buffers chains等待事件

关于Oracle数据库latch: cache buffers chains等待事件

latch: cache buffers chains等待事件的原理

当一个数据块读入到sga中时，该块的块头(buffer header)会放置在一个hash bucket的链表(hash chain)中。该内存结构由一系列cache buffers chains子latch保护(又名hash latch或者cbc latch)。对Buffer cache中的块，要select或者update、insert,delete等，都得先获得cache buffers chains子latch，以保证对chain的排他访问。若在过程中发生争用，就会等待latch:cache buffers chains事件。

latch: cache buffers chains等待事件产生原因

我们先看看Oracle官方对latch:cache buffers chains等待事件的说明：

latch: cache buffers chains：

"latch: cache buffers chains" contention is typically encountered because SQL statements read more buffers than they need to and multiple sessions are waiting to read the same block.

If you have high contention, you need to look at the statements that perform the most buffer gets and then look at their access paths to determine whether these are performing as efficiently as you would like.

Typical solutions are:-

•Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions.

•Check for suboptimal SQL (this is the most common cause of the events) - look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.

1. 低效率的SQL语句(主要体现在逻辑读过高)

在某些环境中，应用程序打开执行相同的低效率SQL语句的多个并发会话，这些SQL语句都设法得到相同的数据集，每次执行都带有高BUFFER_GETS(逻辑读取)的SQL语句是主要的原因。相反，较小的逻辑读意味着较少的latch get操作，从而减少锁存器争用并改善性能。注意v$sql中BUFFER_GETS/EXECUTIONS大的语句。

2.Hot block热点块

当多个会话重复访问一个或多个由同一个子cache buffers chains锁存器保护的块时，热块就会产生。当多个会话争用cache buffers chains子锁存器时，就会出现这个等待事件。有时就算调优了SQL，但多个会话同时执行此SQL，那怕只是扫描特定少数块，也是也会出现HOT BLOCK的。

如果是存在热点块，那么介绍两种找出热点块的方法。

找出热点块方法一：

--首先找出p1raw

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycodeselect p1, p1raw

from v$session_wait

where event = 'latch: cache buffers chains';

--再根据p1raw找到对象

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycodeSELECT /*+ RULE */

E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,

E.PARTITION_NAME,

E.EXTENT_ID EXTENT#,

X.DBABLK - E.BLOCK_ID + 1 BLOCK#,

X.TCH,

L.CHILD#

FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E

WHERE X.HLADDR = '00000003576EE324' --p1raw

AND E.FILE_ID = X.FILE#

AND X.HLADDR = L.ADDR

AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1

ORDER BY X.TCH DESC;

找出热点块方法二：

--直接找出热点块

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycodeSELECT OBJECT_NAME, SUBOBJECT_NAME

FROM DBA_OBJECTS

WHERE DATA_OBJECT_ID IN

(SELECT DATA_OBJECT_ID

FROM (SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH

FROM X$BH

WHERE HLADDR IN (SELECT ADDR

FROM (SELECT ADDR

FROM V$LATCH_CHILDREN

ORDER BY (GETS + MISSES + SLEEPS) DESC)

WHERE ROWNUM < 10)

ORDER BY TCH DESC)

WHERE ROWNUM < 10);

分析latch: cache buffers chains，查找逻辑读较多的SQL进行分析:

Problem: Database is slow and 'latch: cache buffers chains' is high in the waits in AWR.

Start with Top 5 Waits:

Top 5 Timed Events                                      Avg    %Total

~~~~~~~~~~~~~~~~~~                                      wait   Call

Event                          Waits        Time (s)    (ms)   Time   Wait Class

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

latch: cache buffers chains          74,642      35,421    475    6.1 Concurrenc

CPU time                                         11,422           2.0

log file sync                        34,890       1,748     50    0.3 Commit

latch free                            2,279         774    340    0.1 Other

db file parallel write               18,818         768     41    0.1 System I/O

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

High cache buffers chains latch indicates that there is likely to be something reading a lot of buffers. Typically the SQL with the most gets is likely to be that which is contending:

SQL ordered by Gets         DB/Inst:  Snaps: 1-2

-> Resources reported for PL/SQL code includes the resources used by all SQL

statements called by the code.

-> Total Buffer Gets:   265,126,882

-> Captured SQL account for   99.8% of Total

Gets                CPU      Elapsed

Buffer Gets    Executions   per Exec     %Total Time (s) Time (s)  SQL Id

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

256,763,367       19,052     13,477.0   96.8 ######## ######### a9nchgksux6x2

Module: JDBC Thin Client

SELECT * FROM SALES ....

1,974,516      987,056          2.0    0.7    80.31    110.94 ct6xwvwg3w0bv

SELECT COUNT(*) FROM ORDERS ....

The Query with SQL_ID a9nchgksux6x2 is reading 100x more buffers than the 2nd most 'hungry' statement and CPU and Elapsed are off the 'scale' of the report.  This is a prime candidate for the cause of the CBC latch issues.

You can also link this information to the Top  Segments by Logical Reads:

Segments by Logical Reads

-> Total Logical Reads:     265,126,882

-> Captured Segments account for   98.5% of Total

Tablespace                      Subobject  Obj.       Logical

Owner         Name    Object Name            Name     Type         Reads  %Total

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

DMSUSER    USERS      SALES                           TABLE  212,206,208   80.04

DMSUSER    USERS      SALES_PK                        INDEX   44,369,264   16.74

DMSUSER    USERS      SYS_C0012345                    INDEX    1,982,592     .75

DMSUSER    USERS      ORDERS_PK                       INDEX      842,304     .32

DMSUSER    USERS      INVOICES                        TABLE      147,488     .06

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

The top object read is SALES and the top SQL is a select from SALES which appears to correlate towards this being a potential problem select.

This SQL should be investigated to see if the Gets per Exec or the Executions figure per hour has changed in any way (comparison to previous reports would show this) and if so the reasons for that change investigated and resolved.

In this case the statement is reading > 10,000 buffers per execution and executing > 15,000 times

so both of these may need to be adjusted to get better performance.

Note: This is a simple example where there is a high likelihood that the 'biggest' query is the culprit but it is not always the 'Top' SQL that causes the problem. For example, contention may occur on a statement with a smaller total if it is only executed a small number of times so that  it may not appear as the top sql. It may still make millions of buffer gets, but will appear lower in the list because other sqls are performing many times, just not contending.

So, if the first SQL is not the culprit then look at the others.