FAQ
hi, all,
We have a case, when analyze a big and busy IOT table, it took around 2
hours (analylze table xxx estimate statistics sample 5 percent), and later
other sessions were blocked with library cache ping wait event. The analyze
session is still running and that session was waitfinf for db file
sequential read. Can't find other thing special.
Oralce version is 9.2.0.5 <http://9.2.0.5> and OS is solaris 8. Anyone have
experience like that?

Search Discussions

  • Riyaj Shamsudeen at Oct 7, 2005 at 8:25 am
    ('binary' encoding is not supported, stored as-is)

    Zhu

    Analyze will break the parse locks on dependent LC objects and of
    course, it will need to take library cache pin for that LC object before
    breaking the parse locks. If another session is executing that object
    and running for longer period, then the LC pin won't be available for
    the analyze process.

    Since analyze will try to acquire the LC pins in an incompatible
    mode, other sessions trying to access that same LC object will queue
    behind it. Right after breaking the parse locks, other sessions can
    access that LC object triggering an automatic reparse. Recommendation is
    not to analyze the table when there is extensive activity in that table.

    Thanks


    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    zhu chao wrote:
    hi, all,
    We have a case, when analyze a big and busy IOT table, it took
    around 2 hours (analylze table xxx estimate statistics sample 5
    percent), and later other sessions were blocked with library cache
    ping wait event. The analyze session is still running and that session
    was waitfinf for db file sequential read. Can't find other thing
    special.
    Oralce version is 9.2.0.5 <http://9.2.0.5> and OS is solaris 8.
    Anyone have experience like that?
    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.
  • Zhu chao at Oct 8, 2005 at 2:37 am
    From the Tar I filed to oracle:
    ORACLE ANSER:

    Sessions should not be waiting on a analyze statement. This is why I
    requested the above information to verify
    the conditions of an existing bug.

    According to some doc, analyze did:
    1. All the stats have been gathered .
    We acquire share locks for the row cache in this stage
    2. Put them into the dictionary.
    we drop the share locks from the row cache, and start acquiring the
    exclusive locks.
    But this exclusive lock is only for library cache.
    IF other session are reparsing that time, it could be blocked. But in my
    case, the analyze session seems still doing the first step(from wait event,
    it is doing db file sequential read.
    Using DBMS_STATS basicly should do the similar thing for oracle. But Yes,
    we can collect the statistics into a state table, and then find sometime to
    import the statistics. This maybe is a workaround.

    We were already doing the analyze in off-peak time, but there are still
    heavy transaction/query on the tables during the time...
    On 10/7/05, Riyaj Shamsudeen wrote:

    Zhu
    Analyze will break the parse locks on dependent LC objects and of course,
    it will need to take library cache pin for that LC object before breaking
    the parse locks. If another session is executing that object and running for
    longer period, then the LC pin won't be available for the analyze process.
    Since analyze will try to acquire the LC pins in an incompatible mode,
    other sessions trying to access that same LC object will queue behind it.
    Right after breaking the parse locks, other sessions can access that LC
    object triggering an automatic reparse. Recommendation is not to analyze the
    table when there is extensive activity in that table.

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney


    --
    Regards
    Zhu Chao
    www.cnoug.org <http://www.cnoug.org>

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 7, '05 at 5:12a
activeOct 8, '05 at 2:37a
posts3
users2
websiteoracle.com

2 users in discussion

Zhu chao: 2 posts Riyaj Shamsudeen: 1 post

People

Translate

site design / logo © 2022 Grokbase