How to deal with Latch: Cache Buffers Chains for the SQL issued by AQ (Doc ID 1419133.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms


SQL issued by Oracle AQ mechanism encountering 'latch : cache buffers chains" wait. When generated an ASH reports, it shows the following:

Top User Events DB/Inst: AWP1/awp1


Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 44.72 9.08
latch: cache buffers chains Concurrency 22.37 4.54
buffer busy waits Concurrency 3.56 0.72
log file sync Commit 1.90 0.39
library cache: mutex X Concurrency 1.47 0.30

select /*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, t
ab.delay, tab.expiration, tab.retry_count, tab.exception_qschema, tab.except
ion_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_in
fo, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address,
tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data from "CY
GENT_ADMIN"."CTM_Q" tab where q_name = :1 and (state = :2 ) order by q_name,
state, enq_time, step_no, chain_no, local_order_no for update skip locked




Top SQL with Top Events DB/Inst: AWP1/awp1

Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
2xm2qg99ubw8h 2311681738 4879 27.10
latch: cache buffers chains 21.63 TABLE ACCESS - BY INDEX ROWID 9.44
select /*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, t
ab.delay, tab.expiration, tab.retry_count, tab.exception_qschema, tab.except
ion_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_in
fo, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address,


Please be aware that you can generate a performance report with a performance tool you prefer.


The following tools can be used :
ADDM, STATSPACK, AWR Report, OPDG, LTOM or ASH (Note 438452.1).



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms