Re: CBC Latch contention in 10g

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 21 Oct 2004 13:59:54 +0100
Message-ID: <002501c4b76d$dc652170$6702a8c0@Primary>

The CBC latches are shareable, but most of the time they are used exclusively. Although your session is 'only reading' the block, it is not 'only reading' the cache buffers chain.

Even a "no work consistent get" includes the steps:

    acquire latch
    find buffer-header
    modify buffer header by adding buffer pin to users' chain     drop latch
    read buffer
    acquire latch
    modify buffer header by removing buffer pin from users' chain     drop latch

I believe there are only a few special cases where the buffer is read whilst the latch is being held; and these will show up as "consistent get - examinations".

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th

Original Message ----- From: "Smiley John - IL" <SMILEYJ_at_tusc.com> To: "'Oracle-L (E-mail)'" <oracle-l_at_freelists.org> Sent: Tuesday, October 19, 2004 9:31 PM Subject: CBC Latch contention in 10g

I'm seeing large numbers of CBC latch waits when many sessions (20-40) run the same SELECT statement simultaneously (I can peek at v$session_wait and catch this many sessions waiting on latch free: cache buffers chains for the same sql_id).

CBC latch children have been sharable in read mode since 9i. I didn't see any DML taking place at the same time that would cause these latches to be taken in exclusive mode, so I'm wondering why they're blocking on the CBC latches. I've examined the CBC latch children with the most sleeps and examined the blocks on their hash chains and found exactly what I expected to find: hot blocks on some of the tables referenced in the queries. But if the latches are truly sharable for CR buffer gets, this shouldn't cause them to pile up on the latch. Assuming delayed block cleanout isn't the problem, what might explain this? I've searched MetaLink for latch related bugs in 10.1.0.2. No dice.

John Smiley
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 21 2004 - 07:56:25 CDT