RE: 10053 trace for sql fired from pl/sql (stored code)
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Dec 2005 13:21:00 -0500 (EST)
Brandon,
Please find some answers/comments in-line
--- "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
wrote:
> 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.
__________________________________________________________
Find your next car at http://autos.yahoo.ca
--
//www.freelists.org/webpage/oracle-l
Other related posts:
