RE: Drop Index takes longer in Prod than in Test

If all other things are equal, the problem could be that before dropping, Oracle needs to checkpoint to disk all the dirty buffers belonging to that segment to be dropped. And in production you'll probably have more dirty buffers outstanding than in test.  

Trace the drop operation in live if you can and see on what most of the time goes - if it's local write wait or rdbms IPC reply waiting for CKPT process (v$session_wait.parameter1 = CKPT's oracle PID).  

Or alternatively just make a lot of blocks dirty for the segments in test and see if the drop dime degrades accordingly.  

Tanel.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khemmanivanh, Somckit
Sent: Thursday, September 14, 2006 02:33 To: mwf_at_rsiz.com; oracle-l_at_freelists.org Subject: RE: Drop Index takes longer in Prod than in Test

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.  

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