Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: CBC Latch contention in 10g

RE: CBC Latch contention in 10g

From: Smiley John - IL <SMILEYJ_at_tusc.com>
Date: Thu, 21 Oct 2004 09:57:54 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF168808709924B8A@tuscil_ex1>


Thanks Jonathan. You've given me something to think about. It had not occurred to me that the buffer header would have to be modified in order inspect the buffer for consistent read purposes and that the CBC latch would not be taken in a sharable mode. This puts some rather severe limitations on CBC latch sharing and makes me wonder why Oracle bothered to implement it. It would also seem to place some severe limits on scalability even in read-only environments. CBC latch sharing was announced with some fanfare, so I had expected it to be of more consequence.

I would like to understand this better. Any references you can provide would be appreciated. I've read most of the easily obtainable literature: Cary's Optimizing Oracle Performance, both of Tom Kyte's outstanding books, Richmond Shee's Oracle Wait Interface book, Steve Adam's Internals book, and your Practical Oracle 8i book to name a few. However, none of these delved deep enough into what exactly happens during a buffer get for me to latch onto the fact (pun intended) that the buffer header has to be modified if all I want to do is read the buffer. In general, there's a lot more going on under the covers that none of the published resources I've come across so far seem to address.

I'm also interested in seeing if it is possible to determine the length of each buffer cache hash chain. Since a single latch protects a number of hash buckets in 8i and above, there's no hash value in the block header, and I don't know the algorithm used to compute the hash. The best I can do at the moment is say how many blocks are protected by the latch. If I knew how long each individual hash chain was, I could determine if it makes sense to increase _db_block_hash_buckets and objectively measure the results if the change is made.

When determining the source of CBC latch contention, my current practice is to examine the block headers protected by the latch(s) being contended for, look at the touch counts repeatedly to see which blocks have a consistently high tch, get the SQL executed by the sessions experiencing CBC latch contention, and try to divine which sessions are contributing the most to the contention. This leaves a lot to be desired. What I'd really like is any way to positively identify the blocks a session is trying to access without having to guess at them from tch.

Conversely, it would be quite helpful to be able to map a hot block back to the sessions, and SQL, that generated the contention. So far as I know, there is no way to do this.

Regards,

John Smiley

-----Original Message-----

From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Thursday, October 21, 2004 7:56 AM To: Smiley John - IL
Subject: Re: CBC Latch contention in 10g

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

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 - 09:53:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US