Library cache lock during concurrent dbms_stats

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 26 Jul 2005 20:57:51 -0700 (PDT)
Message-ID: <20050727035751.61694.qmail@web31205.mail.mud.yahoo.com>

We have a table that has roughly 1200 subpartitions
(size 240GB - 175M rows). When I made a copy of this
table last week (for some partitioning changes), the table and indexes got built in ~8 Hrs, but the dbms_stats.gather_table_stats took ~ 36 Hrs.

At that time I used the above pkg with
(granularity=>'SUBPARTITION, degree=>4), and ran the
command one-by-one for each 1200 subpartitions (hence this much time taken).

I tried creating 2 scripts containing different set of subpartitions, and executed them as shell background jobs. I noticed the 2nd script's dbms_stats waited on event 'library cache lock' and the session it waited on was the 1st script's session.

My question:

When I run 'dbms_stats.gather_table_stats' that analyzes 2 different subpartitions in 2 separate sessions, why does the 2nd one wait on 'library cache lock', and proceeds only when the 1st one finishes?

I thought until now, that I could use multiple jobs to analyze different sub-partitions concurrently.

Any other idea to perform the above quicker would be much appreciated, since we need to do this activity one more time.

Thanks,
Deepak                 

---

Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs  

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

Received on Tue Jul 26 2005 - 22:59:50 CDT