Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Waits on cache buffers chains latch

Re: Waits on cache buffers chains latch

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Thu, 21 Jun 2001 12:42:31 -0700
Message-ID: <F001.003328C8.20010621124713@fatcity.com>


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

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


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: 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US