RE: Drop Index takes longer in Prod than in Test

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Thu, 14 Sep 2006 10:00:52 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B9022F1B6B@wafedixm10.corp.weyer.pri>

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_at_mail.ee] Sent: Thursday, September 14, 2006 7:39 AM To: Khemmanivanh, Somckit; mwf_at_rsiz.com; oracle-l_at_freelists.org 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_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

Received on Thu Sep 14 2006 - 12:00:52 CDT