RE: Drop Index takes longer in Prod than in Test

  • From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
  • To: <mwf@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 13 Sep 2006 11:32:52 -0700

Thanks Mark.
Version is 9207 Oracle on HPUX. 
TS is LMT but not ASSM.
The indexes are partitioned, the indexes in question are the foreign key
indexes on the Fact Table.
Please let me know what other info would be helpful.
Thanks for the suggestion but I can't take the TS offline, other loads
are occurring concurrently.

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Wednesday, September 13, 2006 11:17 AM
To: Khemmanivanh, Somckit; oracle-l@xxxxxxxxxxxxx
Subject: RE: Drop Index takes longer in Prod than in Test

We'll need version information and whether the tablespaces holding the
indexes are dictionary managed or locally managed.


Are the indexes partitioned or indexes of partitions, etc., etc..


It may be possible for you to speed things up by taking the index
tablespaces offline and doing an offline drop including contents, if the
problem is the pecimal freeing of index extents from a dictionary
managed tablespace.  If the load process you cannot touch does not
specify extent allocations, you may be able to speed things up by
changing the space defaults on the index tablespace(s) to use very few
extents (because the drop can be slow in adding extents back to the free
space on dictionary managed tablespaces.) You also may benefit from
moving to locally managed tablespaces.


But these are all wild guesses without knowing a lot more.


Good luck,





From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khemmanivanh,
Sent: Wednesday, September 13, 2006 1:17 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Drop Index takes longer in Prod than in Test




We have a DW nightly load that drops indexes as part of the load. 


The index drops can sometimes take minutes in prod -- whereas as test of
the same drop, in the test environment, takes seconds. Now minutes might
not seem like much, but there are many indexes so the times add up.


I can't easily enable tracing on the Oracle side (prod server,
multi-tiered env, etc...) so I'm looking for some tips for where/what
else I can look for.


Oh, the other constraint is that I can't modify the DW load process --
so I'm restricted solely to looking at the Oracle side.


Your help is much appreciated.


Thank you!

Other related posts: