| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Waits on cache buffers chains latch
Hi Bruce
You need to find what blocks are hanging from the particular latch. You could get this information by joining x$bh and v$session_wait tables while the sessions are waiting for this latch.
select distinct ts#, file#, dbarfil,dbablk from x$bh
where hladdr in
(select p1raw from v$session_wait where to_char(p2)=66
and lower(event) like 'latch free%'
)
/
(or) You could use the other method since you know the child latch#.
select distinct ts#, file#, dbarfil,dbablk from x$bh
where hladdr in
(select addr from v$latch_children where latch#=66 and child#=242)
From thie file#, dbarilf and dbablk you could find the segment that the latch is protecting. Please note here 66 is the latch# for "cache buffers chains" latch and check that is correct from v$latch in your environment.
I would check the SQL statements also and make sure that they are not doing any full table scans. If you seen any waits for 'Db file scattered read' events in the v$session_wait then that could cause secondary cache buffers chains latch contention.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au>
Sent by: root_at_fatcity.com
06/21/01 01:15 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Waits on cache buffers chains latch
Hi,
Our database is experiencing a very large number of waits on the cache
buffers chains latch.
I know the child latch# is 242 (details below).
We are on Oracle 8.1.5.1.1 on NT4.
The problems seem to have started appearing after starting large scale use
of Global Temporary tables (GTT).
Our temp tablespace was of type temporary but a suggestion from Oracle was
to change this to Permanent (due to GTT related bugs in 815).
This was done and the database was restarted but the waits are still occurring.
What else should I try to look for?
Thanks,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au
Our top waits in general are:
SQL> @system_times
EVENT TIME_WAITED
---------------------------------------------------------------- -----------PX Idle Wait 581928737 PX Deq: Execution Msg 278990599 CPU used by this session 3812597 latch free 202949 db file sequential read 200926 SQL*Net more data to client 73342 db file scattered read 59797 enqueue 19755
Using Ixora's latch_sleeps script I get the following output:
SQL> @latch_sleeps
LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING
LEVEL
------------------------------------- ----------- ---------- -------------
13 rows selected.
and from latch_gets.sql
LATCH TYPE SIMPLE GETS SPIN GETS SLEEP GETS
------------------------------ ------------------ -------------------- --------------------
Virtually all of these waits are on the 1 child latch:
LATCH TYPE SLEEPS LATCHES
------------------------------------------------ --------------------
The child latch in question is child# 242 (from querying v$latch_children).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: Bruce.Reardon_at_comalco.riotinto.com.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jun 21 2001 - 14:42:31 CDT
 |
 |