RE: cache buffer chains

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 16 May 2002 18:08:24 -0800
Message-ID: <F001.00463168.20020516180824@fatcity.com>

Lee,

Firstly, I presume v$system_event shows a significant number and time waited for some latch related event.

Go to http://www.ixora.com.au/scripts/latches.htm and get a copy of latch_sleeps.sql and child_sleeps.sql

latch_sleeps will help confirm that the problem is the cache buffers chain latch. Then run child_sleeps.sql for that latch - see if the sleeps are evenly distributed or if they are bunched on a small number of latches.

Then you need to work out which buffer blocks database objects those latches are referring to.

Go to http://www.ixora.com.au/scripts/cache.htm and consider using hot_blocks.sql, hot_hash_latches.sql and blocks_on_hot_latches.sql.

I hope the above is helpful and that I have interpreted it correctly.

Also, from a Steve Adams posting:
"-----Original Message-----

Sent: Tuesday, 9 October 2001 1:35
To: Multiple recipients of list ORACLE-L

Hi Doug,

If you can catch sessions in V$SESSION_WAIT waiting for 'latch free' on a 'cache buffers chains' latch, you can join to V$SESSION to get the SQL statement address. Something like this ...

select
  s.sql_address
from

  sys.v_$latchname ln,
  sys.v_$session_wait sw,
  sys.v_$session s

where

  ln.name = 'cache buffers chains' and
  sw.p2 = ln.latch# and
  sw.event = 'latch free' and

  s.sid = sw.sid
/
"

Regards,
Bruce Reardon.

-----Original Message-----
Sent: Friday, 17 May 2002 3:12

All,
Oracle 8.0.5
Tru64 4.0f  

I was doing a stats pack analysis and noticed that we had "latch problems". I drilled in a bit further and it would appear that the issue was down to cache buffer chains.  

The Metalink article (I was flying blind here) states  

"To identify the heavily accessed buffer chain look at
the latch stats for this latch under <View:V$Latch_Children>"  

I did this and it came back with over 10000 rows !!!!  

Can someone give me a little guidance here ??  

Regards
 Lee

-- 
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 May 16 2002 - 21:08:24 CDT