Re: optimizer_ ???
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Apr 2005 09:26:40 +0100
----- 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
