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

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