Re: Partition Drops is effecting performance adversely...

Ravi,

Have you examined the explain plan for your major queries before and after dropping partitions and/or re-running stats. If the explain plan isn't changing, then you probably are not experiencing some statistics related optimizer change.

If partition pruning is limiting your query range to recent partitions, then dropping older partitions does not make the table data being queried smaller. For example, if the monthly updates work on the last 45 days of data and the queries all have good partition pruning explain plans (see Note:179518.1 on Metalink), then the size or amount of online data that is older than 45 days does not impact the monthly updates. Are the recent partitions growing larger?

Regards,
Margaret Received on Fri Sep 30 2005 - 13:55:49 CDT