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)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 06/21/01 01:15 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
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
------------------------------------- ----------- ---------- -------------
-----
cache buffers chains 12642171 0.39% 804
1
library cache 7122 0.00% 48564
5
Checkpoint queue latch 7034 0.03% 24370
7
session allocation 346 0.04% 762
5
parallel query stats 198 4.31% 0
8
messages 113 0.01% 1499
8
shared pool 88 0.00% 320
7
cache buffers lru chain 41 0.00% 20765
3
process queue reference 14 0.00% 276
4
query server freelists 2 0.01% 10
6
multiblock read objects 1 0.00% 6
3
redo writing 1 0.00% 165
5
parallel query alloc buffer 1 0.00% 206
6
13 rows selected.
and from latch_gets.sql
LATCH TYPE SIMPLE GETS SPIN GETS
SLEEP GETS
------------------------------ ------------------ --------------------
--------------------
cache buffers chains 3149562975 97.08% 82063196 2.53%
12562449 0.39%
Virtually all of these waits are on the 1 child latch:
LATCH TYPE SLEEPS
LATCHES
------------------------------------------------ --------------------
----------
cache buffers chains 0
951
1 to 2
61
3 to 5
5
10 to 13
3
131
1
652
1
4802
1
12596539
1
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
