Library cache lock during concurrent dbms_stats

From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 26 Jul 2005 20:57:51 -0700 (PDT)

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 
 
--
//www.freelists.org/webpage/oracle-l

Library cache lock during concurrent dbms_stats

Other related posts: