RE: Waits on cache buffers chains latch

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 21 Jun 2001 21:35:02 -0700
Message-ID: <F001.00333204.20010621204529@fatcity.com>

Hi Gaja and thanks also to Riyaj,

If I didn't make it clear before we were having a major performance problem.

A package call that should (ie "normally" / used to) take less than 1 second starting taking between 20 - 60 seconds.

I appreciate your comments on logical IO and will look into that and I also used Riyaj's SQL to look at the segments protected by that latch.

The solution was not reached systematically but rather another DBA in our company suggested gathering stats at a 40% estimate level (we normally gather stats via dbms_stats daily at 30% estimate).

After doing the 40% estimate, the transaction times dropped back down to sub-second.

So what changed - I imagine the CBO is choosing a different execution plan but this hasn't yet been investigated fully.

For your info - we hope to upgrade to 817 in the next 2 weeks and will then be going to using stale statistics monitoring.

Regards,
Bruce

-----Original Message-----
Sent: Friday, 22 June 2001 4:36

Hi Bruce,

Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the "wait events" in your database. If so, great. If not our discussion is purely theoritical, and I do sincerely hope that you don't look at percentages once and conclude that you have a problem. I am assuming that the "latch free" wait event for the cache buffers chains latch occurs frequently and shows up in V$SESSION_WAIT. Contention or waits for the "cache buffers chains latch" usually indicates that there is "too much logical I/O" that is being performed in your environment. Contrary to common knowledge, logical I/O is not 3 orders of magnitude faster than physical I/O in an Oracle environment, as there is a lot more that goes on when Oracle performs a logical I/O, than just "reading blocks from memory". So reducing logical I/O should also be one of the primary efforts one takes in tuning efforts.

The cache buffers chains latch is a scarce resource that needs to be acquired for performing logical I/O and can cause serious contention (which you are probably experiencing). This could be caused because of the use of GTT with tablespace type of "Permanent", as the blocks for temporary segment need to be processed via the database buffer cache, when the size of the data processed by the GTT (at the transaction or the session level) exceeds the size of SORT_AREA_SIZE. Your system may also be experiencing severe contention for the "ST enqueue" as a result of you changing your temporary tablespace to type "permanent", as there could be constant allocation and deallocation of temp segments, which requires the ST enqueue.

One solution to your problem is to upgrade to 8.1.7 (if possible and hoping that the bug is fixed) and flip your temporary tablespace back to type "temporary". Another option is to increase the size of SORT_AREA_SIZE, so that you reduce the frequency with which your sessions generate temporary segments. You also should look into the option of creating a "locally managed temporary tablespace" with the CREATE TEMPORARY TABLESPACE command which again will alleviate the contention for blocks in the database buffer cache.

Hope that helps,

Gaja

• "Reardon, Bruce (CALBBAY)" <Bruce.Reardon_at_comalco.riotinto.com.au> wrote:
  > 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).
  

  ---

Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101

-- 
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 - 23:35:02 CDT