Kerry Osborne's Oracle Blog

I got an email a few days ago . . .

Subject: Exadata / your blogs / you aren’t saying to put HINTS on everything, Are you…

. . . A peer of mine found a couple of your blogs listed below which discuss Profiles and Hints. The position he is inferring upon you is that you are stating unequivocally that we should create lots of indexes, add hints, create profiles and outlines on our Exadata machine. Having read your book and achieve actual results in line with that book, I know that pretty much the opposite is true. . . .

Here’s what I said in my reply (edited a bit to clean up typos and to be a bit more precise):

I do not advocate widespread usage of hints or hint based mechanisms (Outlines, Patches, Profiles). However, because I have written quite a bit about using these techniques, I fear I may have inadvertently left people with the impression that I think hints should be used everywhere. I do not. I view them as expedient ways to change plans or lock in specific plans that I want, but in most cases I use them as bandaids to temporarily cover up other problems. I haven’t really written a blog post specifically on my view of the proper usage of these techniques, so maybe I should do that. When I do presentations on hint based mechanisms to control execution plans, I always have a slide with a picture of a vial of morphine. I use that metaphor to caution people not to over use them. There is a presentation on my blog in the whitepapers/presentations section called “Controlling Execution Plans” (or something to that effect) that you can download which has that slide in it. Also I wrote a chapter on controlling execution plans in a book called Pro Oracle SQL that probably has some prose on when and why to use them (and not use them). But in general – I think they should only be used to give temporary relief until underlying problems can be fixed or in cases where the optimizer just can’t deal with a specific situation (like correlated columns for example) or in cases where you’re using a packaged app that has badly written code that you can’t change. The newest version of the optimizer (which should be released soon) actually stores plans and not just hints with Baselines, so I will leave out the case where you want to manage plans for stability sake.

Since you mentioned Exadata I’ll add that the issue of mixed workload DB’s on Exadata and when the optimizer uses indexes vs when it doesn’t is not straight forward. In general, the current version of the optimizer doesn’t know it’s running on Exadata so it tends to cost FTS’s too high, meaning it often picks indexes when a full scan would be faster. There are some things that can be done to push the optimizer towards full scans. Think of anything that pushes the optimizer in that direction – large db_file_multiblock_read_count for example. Also, System Stats has recently been updated to have an “Exadata” mode which basically just updates the MBRC setting to 128 which does the same sort of thing. If that’s not enough to get most of the statements behaving the way you want then exceptional measures can be taken such as making indexes invisible and using alter session for specific processing to enable the optimizer to see invisible indexes, or doing away with some indexes altogether. Hints (or hint based mechanisms) can be used as well. But my goal would be to get the optimizer to pick the right plan on it’s own in the vast majority of cases before resorting to the more extreme measures.

Hope this helps.

Here’s a copy of the slide I referenced in the email and have been using recently:

The analogy goes something like this: Morphine can provide near instantaneous relief of pain, but it doesn’t do anything to help eliminate whatever is causing the pain. Of course morphine also has some bad side affects, not the least of which is that it’s highly addictive.

Finally – I did a post a little while back about an issue with a single query that I resolved quickly with a profile (Tuning paramon.sql) . The point of the post was that an expedient and low risk solution to a problem is not always the correct long term solution. Here’s a quote from the post.

It’s import to note that the original 11g system I was working on was a production system that I was not comfortable gathering fixed object stats on without discussion and testing, while the bandaid of applying a SQL Profile or adding a hint to my query was a quick work around that allowed me to continue my work with little risk to the rest of the system. So try to use these techniques for what they are good for. Remember that bandaids only help with specific issues and do not resolve underlying problems, but they can provide very quick relief and provide you with a chance to resolve the underlying problem under less stressful conditions.

I hope my position on the topic is now clear. And I sincerely hope that my enthusiasm for hint based techniques has not caused anyone problems. As always, feel free to let me know what you think.