Re: Partition Drops is effecting performance adversely...

RaviAlluru wrote:
> 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

If your query plans are not changing due to the partition drops then the next course of action would be to run 10046 traces at level 12 on the sessions running the query, pass those trace file through tkprof and start looking at the wait events. Something is causing this performance issue, and this is probably the best way to try and discover what that may be:

alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited; alter session set events '10046 trace name context forever, level 12';

[run your query]

alter session set events '10046 trace name context off';

Find the resulting trace files in $ORACLE_BASE/admin/<sid>/udump. Once processed with tkprof you should be able to see what Oracle is doing during this query, and for what it is waiting.

David Fitzjarrell Received on Mon Oct 03 2005 - 16:45:16 CDT