Re: optimizer_ ???

• Original Message ----- From: "Peter Alteheld" <palteheld_at_yahoo.de> To: "Christian Antognini" <Christian.Antognini_at_trivadis.com>; "Niall Litchfield" <niall.litchfield_at_gmail.com> Cc: <oracle-l_at_freelists.org>; <palteheld_at_yahoo.de> Sent: Wednesday, April 27, 2005 9:02 AM Subject: RE: optimizer_ ???

Hi Chris,

Sure. What I, and maybe Niall too, am looking for are examples that show response time reduction by changing of execution plan due to the tweaking of oic or oica (and maybe even dfmrc) from default values to those which should be optimal.

How would you approach that? Is it experience, intuition or do you have any rules for finding such examples?

Peter

I think the inference you can draw from Chris's comments is that Oracle tends to over-cost nested loops compared to hash joins and sort merge joins, therefore:

    if you can see LOTS of examples where Oracle is doing     hash joins or sort merge joins when it seems reasonably     likely that nested loops would be more a better choice,     then you could consider tweaking these parameters.

    You can use the OICA to tell Oracle about hardware     response times - balancing whatever value of the     db_file_multiblock_read_count you choose so that

        tested time for one dbf_mbrc * oica / 100 =
        tested time for one single block read

    You can use the OIC to tell Oracle about your     application - if you have a good handle on the     actual caching of any large, critical indexes.     Alternatively for OIC if you know that the most important     and frequently used joins have a pattern of: "for each row     in table A, get N rows from table B" you could set the     OIC on the basis that the first row of the join will be a     physical index I/O and the next N-1 will be cached.

But in all cases, you have to be careful with OICA and OIC - see

    http://www.jlcomp.demon.co.uk/18_oica.html This describes the problem in terms of OICA, but the same thing applies to OIC, though less frequently because the impact of index leaf blocks on costs is usually less significant than the table block cost.

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/seminar.html Public Appearances - schedule updated April 5th 2005

--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 27 2005 - 04:30:53 CDT