| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10053 trace for sql fired from pl/sql (stored code)
Brandon,
Please find some answers/comments in-line
> I'm not sure I understand your reasoning there. Not
> sure why you find stored outlines to be so
> costly/difficult - they're actually pretty simple
> and effective. Also not sure what either solution
> has to do with having to identify all the
> problematic SQL - that is something you should do
> regardless of how you decide tune/stabilize them.
I think the keyword is "maintenance". With databases to support per DBA ratio being high, we don't try to identify every problematic statement. We are more reactive - user complains, we might take a look. For critical systems - yes, for the rest - not really. In some apps there are maybe a dozen of statements to look out for, in some just a couple. That’s today. In a couple of days a new month begins and the whole situation might change. This is due to the nature of the business - when we start a new season, all 3 "season" partitions are mostly empty (in most of the apps). On average across apps - about 80% of the code will hit the current season partitions (we mostly partition monthly). Due to partition pruning - "partition range single" on a table (or on a local index) usually works well at the beginning. As data accumulates, plans switch to something more narrowly targeted. Pretty much automatically that is (with some rare exceptions) with periodic stats gathering.
I think stored outlines while not difficult to implement (unless literal code – we have that in some apps and games with cursor_sharing), are not cheap as far as maintenance is concerned. All I was looking for is for a plan to stay the same between stats gathering (on some apps weekly). That is if it works well on Monday - it'll stay the same all week long. Next week today's plan might be suboptimal, but in general I 'd much prefer for the cbo to develop a new (hopefully better) one (yes I know about Dave Ensor’s paradox), than manually changing the outlines in every app.
> If your goal is stability, then why are you
> concerned about the plan being able to change with a
> stats update - seems like you'd want to test that in
> a test environment first and then update your stored
> outline if appropriate.
The concern is not the change of plans when stats change, it's the change of plans during a day/week/month BETWEEN stats recollections.
>
> Regards,
> Brandon
Hope this makes sense.
Thanks,
Boris Dali.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 28 2005 - 19:21:00 CST
 |
 |