Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: why rule based optimizer performs better than cost based?

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

From: Tim Gorman <tim_at_sagelogix.com>
Date: Mon, 28 Jun 2004 08:07:30 -0600
Message-ID: <BD058242.174ED%tim@sagelogix.com>


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_at_vicr.com 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_at_index.is]
> Sent: Friday, June 25, 2004 7:33 PM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Mon Jun 28 2004 - 09:04:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US