Re: CBO - hash join vs nested loops

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.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

• Original Message ----- From: "Radoulov, Dimitre" <cichomitiko_at_gmail.com> To: <lnd_at_HNIT.is> Cc: <oracle-l_at_freelists.org> Sent: Monday, December 05, 2005 2:21 PM Subject: Re: CBO - hash join vs nested loops

>
> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l