Waits on cache buffers chains latch

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Wed, 20 Jun 2001 22:26:05 -0700
Message-ID: <F001.003312AB.20010620221524@fatcity.com>

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

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

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 - 00:26:05 CDT