RE: Drop Index takes longer in Prod than in Test

  • From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
  • To: "Tanel Poder" <tanel.poder.003@xxxxxxx>, <mwf@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 14 Sep 2006 10:00:52 -0700

 
Tanel, I think you found it.
 
Here's a 12 minute statspack report's top Timed Events during the time
period of the drop index slowdown.
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
CPU time                                                        1,094
39.63
rdbms ipc reply                                     8,549         927
33.59
enqueue                                               772         404
14.63
db file sequential read                           133,359         204
7.38
log file parallel write                            22,222          55
2.00
-------------------------------------------------------------

Could the rdbms ipc reply event be the checkpointing you're referring
to? What do you make of the high number of enqueue waits? Could some
other process be blocking the drop?
 
I will script up something to capture the v$session_wait info.

Thanks! 

________________________________

From: Tanel Poder [mailto:tanel.poder.003@xxxxxxx] 
Sent: Thursday, September 14, 2006 7:39 AM
To: Khemmanivanh, Somckit; mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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@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: