Hi, Kyte,
I read your book expert one to one, I liked it. It is a great book.
I also read your detailed explanation of steps to do the plan_stability.
I have a question, how much performance shall I lose if I set the database
to catch all the sql statement and the outlines, say for one month, so I can
get a good feel that what index in my database are useless and can be
dropped.
In your book, you mentioned that the performace got hurt only when the sql
statement entered the database the first time, I guess the the overhead
should not be too high, is it?
Thanks
"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
news:a84nl703kr_at_drn.newsguy.com...
> In article <0kbp8.385$Og6.43195_at_news02.tsnz.net>, "Kyle" says...
> >
> >Never used Plan Stability. Doesn't the statement thrown at you by the
3rd
> >party app have to match character for character to the one stored in the
> >outline - hints and all? If it does, I can't see this working.....as
the
> >hint would have to be in the 3rd party generated statement.
> >
> >Would be interested in an answer from someone who has used outlines.
> >
> >Thanks,
> >Kyle
> >
>
> I outline exactly how to do this in my book if you are interested.. In
short,
> you would
>
> o use an on logon trigger to enable SQL statement capturing (save the
generated
> plans into the dictionary)
>
> o after you capture the SQL query as submitted by 3rd party app, you would
work
> on getting the "plan" better. I go through a couple of ways to do this in
8i
> including
> - work in a schema that has hinted views named the same as the base
tables to
> get a "good" plan for that sql query
> - setting up the environment "special" for that good plan (eg:
sort_area_size,
> etc)
>
> o save that better plan
>
> o remove all of the queries you don't want to "fix" from the outline
tables
>
> o change the on logon trigger to USE the saved plans instead of generating
the
> saved plans
>
>
> In 9i -- it gets much easier. OEM has a GUI "query plan editor" that'll
let you
> edit the saved plan without having to "fake it out". steps would be the
same --
> logon trigger to capture the sql/plans -- edit plan -- logon trigger to
use the
> plans.
>
>
> >
> >
> >"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message
> >news:ud6xmsf5b.fsf_at_rcn.com...
> >> On Fri, 29 Mar 2002, 1443131_at_usenetplanet.com wrote:
> >> >
> >> >
> >> > Let's say you have tables that are already designed well for optimal
> >> > performance (table partitions, indexes, materialized views, and so
> >> > on). Also, let's say there are some queries that are doing full
> >> > tablescans very badly and you know what hints to add to make them
work
> >> > better. However, there are some situations where you cannot change
> >> > the queries: such as when the queries are coming from third-party
> >> > applications. What can we do in such case? Thank you in advance.
> >>
> >> I would guess that Optimizer Plan Stability would allow for this. (My
> >> take on this feature is that you create a plan in another instance that
> >> fits the plan you want and then you export that to the instance you are
> >> working on). I haven't used it so maybe the experts can comment?
> >> --
> >> Galen deForest Boyer
> >> Sweet dreams and flying machines in pieces on the ground.
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Sat Mar 30 2002 - 16:52:19 CST
