Re: are hints still necessary in 9i?

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.

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.

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.

--
Richard Kuhler