Re: are hints still necessary in 9i?

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)

> Granted, in systems that have very predictable changes in workload (OLTP
> by day, OLAP by night) you may be able to deal with this by changing
> system settings out on a scheduled basis. However, my experience is
> that systems tend to be much more mixed load than this in reality. In
> fact, my recent clients seem to be heading more towards near real-time
> warehousing where ETL might run many times during the day (perhaps even
> non-stop). In that type of environment, I just don't see how you can
> get by without using hints and often a lot of them.
>
> The only conclusion I could come to is that he's suggesting that it's
> better for some reason to not have hints and just live with some portion
> of your queries running sub optimally (perhaps severely so). This
> leaves me wondering what his strong objection to using hints is based
> on. Maybe others can shed some light on this.

i do not believe in hints as the first plan of attack. I see systems where developers code queries with HINTS from the beginning -- arg.

>
> Sure, In systems that have rapidly changing data characteristics (tables
> growing and shrinking radically) where the optimal plan really is
> changing over time, you're almost certainly better off letting the
> optimizer try and adapt to that then trying to code for it using hints.
> However, I've never worked on a system like that. They typically
> either start with a very representative relative volume of data between
> tables or they very quickly get there. I rarely ever see a queries plan
> need to change over time. Maybe I'm just lucky in that regard.
>
> To a much lesser degree, I can see how one could argue that hints will
> get in the way of future enhancements to the optimizer that might lead
> to a better plan being excluded. Although, any major database upgrade
> should be accompanied by a performance analysis, part of which should be
> evaluating those queries that you've hinted to see how they might be
> handled under the new version. Sure, in most organizations that's
> probably not going to happen but at least performance shouldn't be any
> worse than they were living with before.
>
> Well, I've gone and done it now. Questioning Kyte is certain to lead to
> a string of flames. ;) I'm open to having my mind changed on this
> though so give it a shot.

I hope that would not be the case -- really.

I consider having to hint a query with a "bad" hint a bug, one i'd like to get fixed -- or least discover WHY. Received on Thu Oct 09 2003 - 10:29:48 CDT