Re: What to do? - When optimizer hints cannot be added

From: Kyle <kyle_at_gadgets.co.nz.nospam>
Date: Sun, 31 Mar 2002 09:15:36 +1200
Message-ID: <lUpp8.475$Og6.56997@news02.tsnz.net>

Cheers!
Kyle

"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 - 15:15:36 CST