Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO - hash join vs nested loops

Re: CBO - hash join vs nested loops

From: Jonathan Lewis <>
Date: Mon, 5 Dec 2005 15:03:32 -0000
Message-ID: <010301c5f9ad$0f0a5140$6902a8c0@Primary>

You have to be careful about 'tuning' the OIC - there is no one value that is correct for everything that you do; there is only a representative value which may be quite truthful for a significant number of your most important queries.

Ideally, you need at least one OIC per index - and even then you have to remember that the OIC is only relevant to nested loops and in-list iterators.

Not only that, tweaking the OIC helps to randomise the response time of your queries, because it increases the significance of the rounding errors that the optimizer arithmetic introduces.

Your problem is a good example of this - with one value of OIC, the join changed from hash to NL, but did the NL the wrong way round because the OIC was not sufficiently tweaked. Tweak the OIC to get this index working in this case, and some other code will start using the wrong index because it can't tell the difference between one which WAS quite good and one which WAS pretty awful.

The first STRATEGIC step is to get the system statistics set so that the optimizer has a truthful image about the time cost of multiblock reads, and the CPU cost of doing tablescans. Then worry about whether some realistic setting of OIC and OICA can give Oracle some reasonably truthful information about index and table caching.


Jonathan Lewis The Co-operative Oracle Users' FAQ Cost Based Oracle: Fundamentals Public Appearances - schedule updated 29th Nov 2005

> I think the correct approach is to tune immediately the OIC, but for that
> I have to verify the behaviour of all the problematic statements with the
> new settings.
> I repeat, I have an issue that looks very similar on more than one 9ir2
> instance.

Received on Mon Dec 05 2005 - 09:40:36 CST

Original text of this message