2016-11-15 dba日记，latch cach buffer chain诊断

Troubleshooting 'latch: cache buffers chains' Wait Contention (文档 ID 1342917.1)

In this Document

Purpose

Troubleshooting Steps

Worked example:

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

References

APPLIES TO:

Oracle Database - Personal Edition - Version 9.0.1.0 and later

Oracle Database - Standard Edition - Version 9.0.1.0 and later

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

PURPOSE

This article describes how to troubleshoot issues where there are significant waits for 'latch:

cache buffers chains'.

TROUBLESHOOTING STEPS

"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.

If you can identify a poor SQL and have identified a better plan, you can direct the optimizer

to use this plan using the following article:

Document 1400903.1 Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql /

coe_load_sql_profile.sql (shipped with SQLT)

Further information can be found in:

Document 1476044.1 Resolving Issues Where Waits for 'latch: cache buffers chains' Seen Due to

Poorly Tuned SQL

Document 390374.1 Oracle Performance Diagnostic Guide (OPDG)

Document 163424.1 How To Identify a Hot Block Within The Database Buffer Cache.

Document 62172.1 Understanding and Tuning Buffer Cache and DBWR

2016/11/15 文档显示

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrlstate=

s3tk8cdy3_9 2/3

Worked example:

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

2016/11/15 文档显示

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrlstate=

s3tk8cdy3_9 3/3

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.

REFERENCES

NOTE:62172.1 - Understanding and Tuning Buffer Cache and DBWR

NOTE:163424.1 - How To Identify a Hot Block Within The Database Buffer Cache.

NOTE:390374.1 - Oracle Performance Diagnostic Guide (OPDG)