Home » RDBMS Server » Performance Tuning » performance hurt after dropping the parititons (oracle 10.2.0.3 and solaris 10)
performance hurt after dropping the parititons [message #382491] Thu, 22 January 2009 11:32 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

we are keeping 13 months data on production and before 13 months on reporting database. after copying data from prod to reporting database, we are dropping data on production.

after dropping the partition on prod we are getting massive performance hit.

i am using this following script

alter table xxxx drop partition xxx_0208 update global indexes;


how to find out where is the problem and rectify this problem

after dropping the partition what are the precaution i need to take care.
i am checking the object status, index status (local/global) on the is table.
stats are update for the current partition. and i ran manual stats collection for entire table.

please kindly help me to find out the problem.
Re: performance hurt after dropping the parititons [message #382498 is a reply to message #382491] Thu, 22 January 2009 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: performance hurt after dropping the parititons [message #382845 is a reply to message #382498] Sun, 25 January 2009 16:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Possibly the global indexes are fragmented and need to be rebuilt.

That wouldn't make performance worse though, it would just mean that dropping partitions wouldn't make it any better.

Ross Leishman
Re: performance hurt after dropping the parititons [message #382950 is a reply to message #382491] Mon, 26 January 2009 11:01 Go to previous messageGo to next message
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
Junior Member
Has the explain plan changed after dropping the partitions?

I assume that you have a testing/staging database somewhere that you can reproduce the circumstances with.
Re: performance hurt after dropping the parititons [message #383189 is a reply to message #382950] Tue, 27 January 2009 11:05 Go to previous message
sonumalhi
Messages: 62
Registered: April 2008
Member
Dropping the partitions might have changed Global statistics on partitoned table which could have changed the query plan.

Consider gathering Global stats for the table after dropping partitions.
Previous Topic: Stale Stats for Partitoined table
Next Topic: Hash cluster with small (tiny) rows ?
Goto Forum:
  


Current Time: Sun Dec 11 08:31:31 CST 2016

Total time taken to generate the page: 0.10281 seconds