Re: are hints still necessary in 9i?

on 10/9/2003 8:29 AM Thomas Kyte said the following:

>Richard Kuhler <noone_at_nowhere.com> wrote in message news:<KUYgb.20756$th6.2546_at_twister.socal.rr.com>...
>
>
>>Ryan Gaffuri wrote:
>>
>>
>>>Tom Kyte swears that if you properly analyze your table, the CBO is at
>>>the point where you never need hints. I find I still need them
>>>'occasionally', though not very often.
>>>
>>>anyone else have opinions? anyone who beta tested 10g see an
>>>improvement in the CBO? IT tends to get a little better with each
>>>release.
>>>
>>>
>>This is literally the _only_ thing that I've ever read by Kyte that I
>>was puzzled by. I can't help but believe that it's just hyperbole.
>>This seems especially true in your typical Data Warehouse environment.
>>Invariably, there is some third party business intelligence tool that
>>submits adhoc queries that you just can't tune directly. The natural
>>response is to configure the system level optimizer settings to handle
>>this class of queries as efficiently as possible. In my experience,
>>there is almost always another set of applications that perform queries
>>with significantly different characteristics (more 'OLTP' like) that
>>suffer from this. I have seen no more effective way to deal with this
>>than to use hints on these queries. In fact, given that I'm forced to
>>tune to the third party application, I find myself equally forced to use
>>far more hints elsewhere. Ok, maybe that's a bit of hyperbole itself,
>>by far more I mean maybe 1 out of ever 20 queries might need hinting
>>with the more complex ones being far more likely to need help.
>>
>>
>>
>I guess I'm coming down on the side of "don't use hints until your
>back is in the corner."
>
>I'm not against all hints either -- there are hints that give the
>optimizer INFORMATION with which it can make better decisions with --
>first_rows, all_rows, driving_size, dynamic sampling, cursor sharing
>-- etc. These are "good hints". they are not doing the job for the
>optimizer, they are giving it useful information.
>
>Most of the times -- these hints are all that are needed -- or letting
>the oltp apps do their alter sessions and the other things have their
>sessions (eg: the entire system need not run with the same optimizer_*
>settings)
>
>

Forgive me for not exactly sticking with the subject that goes "are hint necessary in *9i*". In 10g, we have "SQL Profiles" which exactly is a set of such information which Tom calls "good hints", that guides the optimizer to do the right thing rather than forcing the optimizer to make a particular decision. Even better, one can create SQL Profiles for any SQL, no matter they are from packaged apps or custom apps without modifying the actual SQL statement. Simply, starting from 10g, I see no future for any type of hints - no matter whether they are the "good" ones, or the "bad" ones or the "ugly".

Thanks,
-V Received on Thu Oct 09 2003 - 14:35:51 CDT