Re: latch: cache buffers chains

On Apr 23, 10:53 am, wagen..._at_yahoo.com wrote:
> 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. Received on Tue Apr 24 2007 - 21:18:34 CDT