RE: Drop Index takes longer in Prod than in Test
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: somckit.khemmanivanh@xxxxxxxxxxxxxxxx, mwf@xxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 14 Sep 2006 22:38:34 +0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Khemmanivanh, Somckit
Sent: Thursday, September 14, 2006 02:33
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
