Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bill.

Asked: September 26, 2008 - 4:34 pm UTC

Last updated: September 26, 2008 - 4:44 pm UTC

Version: 10.2.0.2

Viewed 1000+ times

You Asked

Hello,

I just became aware of hints last month and haven't found much documentation on them. Most seem to advise against using them even when doing a search here and finding this topic.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061

Then I went to the last session at Open World yesterday and saw them in many of your queries. Why was that? Feel free to point me somewhere if you've answered this before or can point me to some additional documentation on the subject.

Thank you,
Bill

and Tom said...

When I ran my queries - I was demonstrating a concept - you are probably referring to when I was showing the clustering factor - what is it, how it works, what it means.

I stated "the clustering factor is a measure of how many IO's it would take to read every indexed record from the table via the index". In order to *show* that, I had to a) show the clustering factor from the dictionary and then b) read every indexed record from the table via that index in order to c) show the direct correlation between the two.

Now, to read every record from the table via a given index (especially an index that has a 'bad' clustering factor), is something the optimizer would not normally do on it's own. So, I used a hint to force it's hand - to make the optimizer read every row via the index.

As you saw, the resulting query performance was horrible - we did the maximum IO possible, the optimizer would NEVER use that plan - but I needed to use that plan to demonstrate the concept.


I like to say there are two kinds of hints - good hints and bad hints.

Good hints give the optimizer MORE INFORMATION - things like first_rows_n, that is a good hint, it tells the optimizer to optimize for initial response time. all_rows - that is a good hint, it tells the optimizer to optimize for total throughput.


Bad hints tell the optimizer what to do, use this index, process the tables in this order, use this join technique. I do not like to use those in real code. I use them in *examples* to show how something works - but not to tune with - in general.



So, yesterday - when you saw me doing the hints, it was to demonstrate how clustering factors work, what they mean and why Oracle will stop using your index in a range scan after a fairly small set of rows if the clustering factor is "bad"

The example in question was similar to this one:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968#14135826006483



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.