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

In article <Dkrp8.1165$AE1.484_at_nwrddc03.gnilink.net>, "Joe says...
>
>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?
>

I did not observe it to be a significant factor.

If you use the on-logon trigger to enable/disable it -- it is trivial to turn on and off if you discover otherwise.

>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
>>
>
>

--
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