RE: CBC Latch contention in 10g
- From: Smiley John - IL <SMILEYJ@xxxxxxxx>
- To: 'Jonathan Lewis' <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Oct 2004 09:57:54 -0500
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@xxxxxxxxxxxxxxxxxx]
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
----- Original Message -----
From: "Smiley John - IL" <SMILEYJ@xxxxxxxx>
To: "'Oracle-L (E-mail)'" <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
