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: Drop Index takes longer in Prod than in Test

RE: Drop Index takes longer in Prod than in Test

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 13 Sep 2006 14:16:35 -0400
Message-ID: <007f01c6d760$c0430dd0$0c00a8c0@Thing1>


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,  

mwf  


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khemmanivanh, Somckit
Sent: Wednesday, September 13, 2006 1:17 PM To: oracle-l_at_freelists.org
Subject: Drop Index takes longer in Prod than in Test  

Hi,  

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!

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 13 2006 - 13:16:35 CDT

Original text of this message

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