Re: Oracle HINTS and performance

In message <DCq1pC.2yJ_at_syacus.acus.oz.au> - brenton_at_syacus.acus.oz.au (Brenton Camac) writes:

:> For queries that selected 0.005% of the search, the hint ran between 0-10%
:>slower according to the timed statistics given by oracle's sqltrace. For large
:>size queries (80%), the hint implementation performed even worse; around
:>100-300% slower. But I think the later results are not good representative
:>tests since using indexed searches for these circumstances is not efficient.

Did you use EXPLAIN PLAN to check how it is executing the statement before and after?

:>Q. Is anyone running with the INDEX_DESC HINT , or any other HINT?
:> If so, what improvements were noted.

I use hints occasionally. I had an example to today where I wanted a table ordered by its primary key. Without a HINT, explain plan tells me it would have done a full scan. I wanted the first few rows only. By using a FIRST_ROWS hint, explain plan told me it would use the index. Consequently, I got the first few rows very quickly.

Explain plan is very useful!

:>Q. Has anyone experimented with this feature, and conducted timed performance
:> tests?

Yes. And our checked explain plan.

:> What results were achieved?

Sometimes better, sometimes not.

:> Did the HINT perform faster than the
:> non-HINT equivalent, and under what conditions?

Yes, for the results I wanted.

+---------------------------
| Simon Thompson