Re: Partition Drops is effecting performance adversely...

Thanks Magaret,

   for your prompt and informative feedback. Partition pruning has been our biggest obstacle. We noticed (even before archiving), if we go against more than one partion (weekly partitions) at one time , the queries or batch jobs get stuck . They simply dont return. The Explain Plans , show that we are going against 1 or 2 partitions, and the cost is low, but for some reason the queries dont return .

   So if we sont do partition pruning , and do a simple query spanning all partitions the explain plans do show that we are spanning a range of partitions, cost seems to be higher, but the queries return fairly quickly. We are using 9.2.0.4 Oracle Enterprise version on Solaris 8 OS.

   Just a little bit more information on our app. Its a telecom billing app. Our Bill Cycles go against the following days 8,16 ,23 and 29th of every month. whereas the table partitions are off by a litte, 7.14,21 etc.

   So we need to go against at least 4-5 weekly partitions for every bill cycle to be able to bill the clients for each cycle.

   As soon as we span more than one partition the optimizer goes crazy. So we found for some of the long running jos or queries we just went wiyth no partition range. The performance was a lot better.

   Keeping the above in mind when we drop or chop off a bunch of old partitions, with Global stats not being run, is it effecting the optimizer?

   Shouldnt it be smart enough to go just by the incremental partition stats and know that the old partitions no longer exist and the Global stats are not good any more?

Please advise
Thanks
Ravi Received on Fri Sep 30 2005 - 16:35:49 CDT