FAQ
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

Search Discussions

  • Cary Millsap at Oct 19, 2004 at 3:50 pm
    I believe Steve Adams once told me that the shareable latch feature is =
    not
    implemented on HP-UX.

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary

    SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
    Hotsos Symposium 2005: March 6-10 Dallas
    Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =

    On Behalf Of Smiley John - IL
    Sent: Tuesday, October 19, 2004 3:31 PM
    To: 'Oracle-L (E-mail)'
    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.=20

    John Smiley

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Oct 21, 2004 at 7:57 am
    ����

    It's CPU dependent. The older CPU's did not
    have an atomic CAS (compare and swap)
    instruction. In some cases this left Oracle using
    exclusive latches, in others Oracle worked around
    the problem by using a latch to emulate CAS...
    the 'cas latch' - which was subsequently joined
    by the 'rm cas latch'. You'd have to have a 3 or 4
    year old CPU for this to be a problem though.

    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: "Cary Millsap"
    To: "'Oracle-L (E-mail)'"
    Sent: Tuesday, October 19, 2004 9:41 PM
    Subject: RE: CBC Latch contention in 10g

    I believe Steve Adams once told me that the shareable latch feature is =
    not
    implemented on HP-UX.

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary

    SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
    Hotsos Symposium 2005: March 6-10 Dallas
    Visit www.hotsos.com for schedule details...
  • Tanel Põder at Oct 19, 2004 at 5:04 pm

    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).
    Which platform are you on? HP-UX?

    Did you have 9i running on the same box before without problems?

    What are the values for 'shared hash latch upgrades%' statistics?

    Tanel.
  • Jonathan Lewis at Oct 21, 2004 at 7:56 am
    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"
    To: "'Oracle-L (E-mail)'"
    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
  • Smiley John - IL at Oct 21, 2004 at 9:53 am
    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
    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"
    To: "'Oracle-L (E-mail)'"
    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
  • Jonathan Lewis at Oct 21, 2004 at 11:33 am
    I don't know if there is any published material
    on this - I think I got the important bit of it from
    Steve Adams' masterclass.

    It's likely that the sharable cache latch used some
    of the time is better than never having it at all - and
    Oracle does keep finding new ways to (a) reduce
    the latch acquisition, and (b) switch to single latch
    reads anyway, so scalability does keep improving.

    Where did you see a fanfare about sharable latches,
    I didn't spot it. I'd be interested to see how it was
    presented.

    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"
    To: "'Jonathan Lewis'"
    Cc:
    Sent: Thursday, October 21, 2004 3:57 PM
    Subject: RE: CBC Latch contention in 10g

    : 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.
  • Smiley John - IL at Oct 21, 2004 at 1:22 pm
    Perhaps fanfare was too strong a word. I was greatly encouraged by what I
    read about CBC latch sharing for reads in Cary's "Why You Should Focus on
    LIOs Instead of PIOs" and Richmond's "Oracle Wait Interface:..."

    Regards,

    John Smiley

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jonathan Lewis
    Sent: Thursday, October 21, 2004 11:38 AM
    To: oracle-l_at_freelists.org
    Subject: Re: CBC Latch contention in 10g

    I don't know if there is any published material
    on this - I think I got the important bit of it from
    Steve Adams' masterclass.

    It's likely that the sharable cache latch used some
    of the time is better than never having it at all - and
    Oracle does keep finding new ways to (a) reduce
    the latch acquisition, and (b) switch to single latch
    reads anyway, so scalability does keep improving.

    Where did you see a fanfare about sharable latches,
    I didn't spot it. I'd be interested to see how it was
    presented.

    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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 19, '04 at 3:27p
activeOct 21, '04 at 1:22p
posts8
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase