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: analyze a BIG IOT table caused other session blocked with library cache lock

Re: analyze a BIG IOT table caused other session blocked with library cache lock

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 07 Oct 2005 08:22:29 -0500
Message-ID: <43467695.7060703@jcpenney.com>

 ('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.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 07 2005 - 08:25:53 CDT

Original text of this message

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