Re: why rule based optimizer performs better than cost based?

  • From: Tim Gorman <tim@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 28 Jun 2004 08:07:30 -0600

Since O_I_C_A is supposed to represent the difference in I/O times between
indexed and FULL table scan, perhaps it is best not to remember fixed values
but instead query the database for actual values?  Nothing beats facts...

    select  event, average_time
    from    v$system_event
    where   event in ('db file sequential read',
                      'db file scattered read');

Then calculate a suitable ratio from the values returned in AVERAGE_TIME,
with "db file sequential read" as the numerator and "db file scattered read"
as the denominator.  Of course, a ratio value obtained from a single reading
could not be "gospel", as it's validity would depend on the length of time
that the database was up, what types of I/O had been performed since the
database was up.  It would be best to carefully sample these values over
time, rather than take a single reading and slap a new value on O_I_C_A.  As
Bob Vila says, "measure twice, cut once"...

The intent of the parameter is not to "bias" the CBO one way or the other,
which is the implication of a fixed value like "10" for OLTP and another
fixed value like "50" for DSS.  Instead, the intent of the parameter is to
inform the CBO about this one aspect of query performance which cannot be
otherwise gathered by the database.  You'd be shocked at the variance of
values to the query above that some systems show.  There are quite a few
systems whose O_I_C_A should rightly read "500" or so, not that I'd
recommend setting it to that value automatically.  Instead, first find out
the reason for the anomalous reading and verify that it is happening over
time...

In 9i and above, using DBMS_STATS.GATHER_SYSTEM_STATS gathers essentially
the same information implied by the above query on V$SYSTEM_EVENT, so that
the use of the O_I_C_A parameter can be superceded by the use of the
DBMS_STATS.GATHER_SYSTEM_STATS procedure.

As several people have stated earlier, it is probably best to leave O_I_C_A
alone, or at least show more circumspection in setting it from it's default.
What is more important is the O_I_C (optimizer_index_caching) parameter,
which absolutely must be set to a value like 90 to correct what is
essentially a flaw in the calculations by the CBO.  Again, this parameter is
not intended to "bias" the CBO toward or away from index usage, but rather
inform the CBO about the true nature of caching with regard to indexed I/O.

Just my $0.02...



on 6/28/04 7:22 AM, Goulet, Dick at DGoulet@xxxxxxxx wrote:

> Dan's recommendations, from his presentation are:
> 
> Optimizer_index_caching=3D90
> Optimizer_index_cost_adj=3D10 for OLTP systems
> Optimizer_index_cost_adj=3D50 for DSS
> 
> Now I don't have a database around here that I'd call either an OLTP or =
> DSS system.  Their mostly hybrids of the above, so for me
> 
> Optimizer_index_caching=3D90
> Optimizer_index_cost_adj=3D40
> 
> Seems to work the best.
> 
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
> 
> -----Original Message-----
> From: Gudmundur Josepsson [mailto:gbj@xxxxxxxx]
> Sent: Friday, June 25, 2004 7:33 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: why rule based optimizer performs better than cost based?
> 
> 
> Dick,
> 
> Does Dan recommend these values for every OLTP and DSS environments or =
> are
> these good values to start working from?  Do you know if he's published
> anything on this subject that's downloadable from somewhere?
> 
> Thanks,
> Gudmundur
> 
>> Mei,
>> 
>> Take a serious look at two init parameters:
>> 
>> Optimizer_index_caching
>> Optimizer_index_cost_adj
>> 
>> The default values are 0 and 100 respectively which inform the =
> optimizer =3D
>> that a) you will never find an index in the buffer cache and b) =
> scanning =3D
>> an index is just as expensive as a table.  If any of you are familiar =
> =3D
>> with Dan Hotka, he recently presented his "Index Advance Tuning" =
> seminar =3D
>> at the NOUG DBA SIG here in Boston.  His recommendation is to set =
> these =3D
>> to 90 and 30(OLTP) or 50(DSS) respectively.  I've tried it, it helps =
> and =3D
>> does not appear to have any unpleasant side effects.
>> 
>> Dick Goulet
>> Senior Oracle DBA
>> Oracle Certified 8i DBA
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: