Re: Oracle HINTS and performance

Brnton Gamac wrote at Thu, 3 Aug 1995 06:22:23 GMT
>
>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)
>--

Yes, a have experimented with HINT INDEX and HINT INDEX DESC feature. Results were varied. On most queries HINT give no performance boost, but on some queries (usually very complex one with numerous subquieries) hints increase performance dramattically - up to 10 time. But in almost all cases it is possible to alter slitly such queries to achieve same results without hints. But I don't found any rule for such alteration. So performance of queries with hints is more predictable.

Best wishes,

Alexander Alesinsky
JSV Investor
Kharkov Ukraine Received on Sun Aug 20 1995 - 00:00:00 CEST