Oracle HINTS and performance

Hi netters,

        I've been watching this group for a discussion about ORACLE7 HINTS feature. Since I haven't seen one, and have some questions / information of my own, I thought I might start the thread.

        I've just finished preliminary investigations into using the INDEX_DESC HINT feature. This included modifying part of our 4GL product to use the HINT in certain situations; for performance improvement. I've run some tests to measure performance changes, and the results were not what I expected.

        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.

        Before I get too many readers off-side (if I haven't already :) I am aware that it is early days for HINTS feature and further work will improve it. The objective of this discussion *is not* to run oracle down, but to exchange practical exprience and gain some wisdom on this subject. To this end,

Q. Is anyone running with the INDEX_DESC HINT , or any other HINT?

    If so, what improvements were noted.

Q. Has anyone experimented with this feature, and conducted timed performance

    tests? What results were achieved? Did the HINT perform faster than the     non-HINT equivalent, and under what conditions? Have any operational     characteristics emerged w.r.t HINTS feature?

Regards,

        Brenton
--

Brenton Camac                                     Australian Centre for
  Internet:  brenton_at_syacus.acus.oz.au            Unisys Software (ACUS)

Brenton Camac                                     Australian Centre for
  Internet:  brenton_at_syacus.acus.oz.au            Unisys Software (ACUS)