latch: cache buffers chains

[w***@yahoo.com]

Oracle 10g 10.2.0.2 (64 bit)
2 -node RAC

I am seeing only ONE session waiting on a CBC latch. Followed the
metalink docs to identify the hot block without luck. See below.

select count(*), event from gv$session_wait where event like '%latch%'
group by event order by 1 desc;

COUNT(*) EVENT
---------- -----------------------------------
1 latch free


SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM gv
$session_wait WHERE event = 'latch: cache buffers chains' GROUP BY p1,
p2, p3;

NUM_WAITERS FILE# BLK# CLASS
----------- ---------- ---------- ----------
1 2629049624 122 0

SELECT owner,segment_name,segment_type FROM dba_extents WHERE
file_id=2629086424 and 122 BETWEEN block_id AND block_id + blocks;

no rows selected.

Any ideas to determine the root cause.
What does CLASS=0 mean?
File=2629049624 does not exit.

Any help/pointers would be appreciated.

Thanks
wagen

[w***@yahoo.com]

Not sure if I am hitting Bug 4742607 - "cache buffer chains" latch
contention from concurrent index range scans fixed in 10.2.0.3.

[w***@yahoo.com]

Any one experience a similar problem?

[w***@yahoo.com]

Any one experience ONE session waiting on a CBC latch? what could be
the potential cause?

Thanks
wagen

[Robert Klemme]

Post by w***@yahoo.com
Any one experience ONE session waiting on a CBC latch? what could be
the potential cause?

If one task holds a lock the next task will be waiting for the lock. I
don't understand what you find so strange about this.

robert

[Brian Peasland]

Post by w***@yahoo.com
Oracle 10g 10.2.0.2 (64 bit)
2 -node RAC
I am seeing only ONE session waiting on a CBC latch. Followed the
metalink docs to identify the hot block without luck. See below.
select count(*), event from gv$session_wait where event like '%latch%'
group by event order by 1 desc;
COUNT(*) EVENT
---------- -----------------------------------
1 latch free
SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM gv
$session_wait WHERE event = 'latch: cache buffers chains' GROUP BY p1,
p2, p3;
NUM_WAITERS FILE# BLK# CLASS
----------- ---------- ---------- ----------
1 2629049624 122 0
SELECT owner,segment_name,segment_type FROM dba_extents WHERE
file_id=2629086424 and 122 BETWEEN block_id AND block_id + blocks;
no rows selected.
Any ideas to determine the root cause.
What does CLASS=0 mean?
File=2629049624 does not exit.
Any help/pointers would be appreciated.
Thanks
wagen

Are you sure you have the proper descriptions for the 3 parameters? I
get the following:

SQL> select name,parameter1,parameter2,parameter3
2 from v$event_name where name like 'latch:%';

NAME
----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
latch: cache buffers chains
address
number
tries

So P1 is not FILE# as suggested, but the address. P3 is the number of
tries for the latch.

Is this the only wait event your session is hung up on? Also
experiencing 'buffer busy waits' by chance?


Cheers,
Brian
--
===================================================================

Brian Peasland
***@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[Charles Hooper]

Post by w***@yahoo.com
Oracle 10g 10.2.0.2 (64 bit)
2 -node RAC
I am seeing only ONE session waiting on a CBC latch. Followed the
metalink docs to identify the hot block without luck. See below.
select count(*), event from gv$session_wait where event like '%latch%'
group by event order by 1 desc;
COUNT(*) EVENT
---------- -----------------------------------
1 latch free
SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM gv
$session_wait WHERE event = 'latch: cache buffers chains' GROUP BY p1,
p2, p3;
NUM_WAITERS FILE# BLK# CLASS
----------- ---------- ---------- ----------
1 2629049624 122 0
SELECT owner,segment_name,segment_type FROM dba_extents WHERE
file_id=2629086424 and 122 BETWEEN block_id AND block_id + blocks;
no rows selected.
Any ideas to determine the root cause.
What does CLASS=0 mean?
File=2629049624 does not exit.
Any help/pointers would be appreciated.
Thanks
wagen

I don't believe that you are interpretting the P1, P2, and P3
parameters correctly for this wait event - the meaning of those
parameters depends on the type of wait event. For latch related
events in V$SESSION_WAIT, I believe that P1 represents the address of
the latch, P2 represents the latch number, and P3 represents the
number of times the process has slept waiting for the latch.

I also had problems with the cache buffers chains latch for one of my
SQL statements that ran in less than 10 seconds on Oracle 8.1.7.3, but
was taking more than 12 minutes on Oracle 10.2.0.2. I was joining a
table to an inline view that contained another inline view - the
inline views should have been driving the table, but Oracle preferred
to try forcing the table to drive the nested inline views, resulting
in a terrible Cartesian Merge join. Fixing the query required using
an ORDERED hint, although a LEADING hint also would have worked. With
the hint in place, the SQL statement executed in less than two
seconds.

You might want to see if this is the case with the session. Turn on a
10046 trace for the session without wait events, but with bind
variables (pass in a value of 4 when enabling the trace). When the
SQL statement finishes, and the client closes the cursor, Oracle will
output the execution plan in the row source lines. Check those lines
to determine if a poor execution plan was selected.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Charles Hooper]

Post by w***@yahoo.com
Oracle 10g 10.2.0.2 (64 bit)
2 -node RAC
I am seeing only ONE session waiting on a CBC latch. Followed the
metalink docs to identify the hot block without luck. See below.
select count(*), event from gv$session_wait where event like '%latch%'
group by event order by 1 desc;
COUNT(*) EVENT
---------- -----------------------------------
1 latch free
SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS FROM gv
$session_wait WHERE event = 'latch: cache buffers chains' GROUP BY p1,
p2, p3;
NUM_WAITERS FILE# BLK# CLASS
----------- ---------- ---------- ----------
1 2629049624 122 0
SELECT owner,segment_name,segment_type FROM dba_extents WHERE
file_id=2629086424 and 122 BETWEEN block_id AND block_id + blocks;
no rows selected.
Any ideas to determine the root cause.
What does CLASS=0 mean?
File=2629049624 does not exit.
Any help/pointers would be appreciated.
Thanks
wagen

I don't believe that you are interpreting the P1, P2, and P3
parameters correctly for this wait event - the meaning of those
parameters depends on the type of wait event. For latch related
events in V$SESSION_WAIT, I believe that P1 represents the address of
the latch, P2 represents the latch number, and P3 represents the
number of times the process has slept waiting for the latch.

I also had problems with the cache buffers chains latch for one of my
SQL statements that ran in less than 10 seconds on Oracle 8.1.7.3, but
was taking more than 12 minutes on Oracle 10.2.0.2. I was joining a
table to an inline view that contained another inline view - the
inline views should have been driving the table, but Oracle preferred
to try forcing the table to drive the nested inline views, resulting
in a terrible Cartesian Merge join. Fixing the query required using
an ORDERED hint, although a LEADING hint also would have worked. With
the hint in place, the SQL statement executed in less than two
seconds.

You might want to see if this is the case with the session. Turn on a
10046 trace for the session without wait events, but with bind
variables (pass in a value of 4 when enabling the trace). When the
SQL statement finishes, and the client closes the cursor, Oracle will
output the execution plan in the row source lines. Check those lines
to determine if a poor execution plan was selected.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.