Re: CBO - hash join vs nested loops

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 5 Dec 2005 15:03:32 -0000


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@xxxxxxxxx>
To: <lnd@xxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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.





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


Other related posts: