FreeLists / oracle-l / Re: optimizer_ ???

["Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>]

----- Original Message ----- 
From: "Peter Alteheld" <palteheld@xxxxxxxx>
To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>; "Niall 
Litchfield" <niall.litchfield@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>; <palteheld@xxxxxxxx>
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