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

>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 <rshamsud_at_jcpenney.com> 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