Re: Partition Drops is effecting performance adversely...

"RaviAlluru" <ravi.alluru_at_gmail.com> wrote in message news:1128116149.704127.133900_at_f14g2000cwb.googlegroups.com...
> 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
>

Ravi,

When the optimizer can tell that your query is to be run against a specific partition, then it uses the statistics from that partition to optimize the query.

When it does not know which partition, or when the query will cover multiple partitions, then the table-level stats are used.

If you have been dropping partitions, and adding partitions without doing something to make the table-level statistics stay reasonably up to date, then the optimizer will probably be using totally inappropriate statistics to estimate how many rows you want, and the best way to get them.

For example - as a billing app, the data for the most recent 4 week will have a date value in the September 2005 range, and there will be a lot of it. If you haven't regenerated table level statistics for 3 months, the 'event date' may have a recorded 'high value' - of June 2005, with the result that the optimizer will decide that there is virtually no data with a September data, and that the optimum execution path is therefore some unexpected indexed access path - which happens to run forever to pick up the millions of rows you want.

You don't HAVE to use the gather_xxx_stats calls to get reasonable statistics. If you have a good idea of the critical numbers, you can simply write them through the 'set_xxx_stats' calls. So you could run a quick program that reads the partition level stats, generates some reasonable table level stats - based on rules that you have to decide - and then writes these back to the database.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005