Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?

From: SAP BASIS Consultant <basis_consultant_at_hotmail.com>
Date: 16 Jan 2003 07:29:36 -0800
Message-ID: <dd2036f3.0301160729.37609f97@posting.google.com>

Hello,

Thanks for those people who replied to my question regarding the default setting for OPTIMIZER_INDEX_COST_ADJ for Oracle v8.1.7/BW.

I would appreciate any feedback on the following logic for setting OPTIMIZER_INDEX_COST_ADJ from v$SYSTEM_EVENT data that I found after I posted the question. Would people agree with it?

On the Website 'www.evdbt.com' (Tim Gorman) has what I think is a great document on the CBO. (The Search For Intelligent Life In The CBO). He
suggests that the following query for find out the ratio between wait times for sequential reads (Index scans) and scattered reads (FTS) as an initial estimate
got OPTIMIZER_INDEX_COST_ADJ.

I checked V$SYSTEM_EVENT and the ratio average waits for seq. reads to scattered reads for about .333, so that I should (Initially at first) set
OPTIMIZER_INDEX_COST_ADJ to 33. (A bit lower than normally expected from a
DSS system, I think).

(i.e From the document..... )

for following query:

SELECT	EVENT,
		AVERAGE_WAIT
FROM	V$SYSTEM_EVENT
WHERE	EVENT LIKE ‘db file s%’;

..... The AVERAGE_WAIT column contains the average timing, in 1/100ths of a second, of these events:

EVENT                     AVERAGE_WAITS
========================= ==============
db file sequential reads       .33178629
db file scattered reads         2.190087

In this example, indexed scan I/O requests takes only 15% as long as each FULL table scan I/O request. So, set OPTIMIZER_INDEX_COST_ADJ to 15.

SELECT	EVENT,
		AVERAGE_WAIT
FROM	V$SYSTEM_EVENT
WHERE	EVENT LIKE ‘db file s%’;

Thanks,
BASIS Consultant

Noons <nsouto_at_optusnet.com.au.nospam> wrote in message news:<Xns93056B2B38Amineminemine_at_210.49.20.254>...

> basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote in
> news:dd2036f3.0301142051.6fb8fe62_at_posting.google.com and I quote:
>
> >
> > The 'Explain Plan' seems to indicate that Oracle prefers
> > FTS to using indexes to a greater extent than I have seen in the past.
> > (I don't have the plans with me).
>
> Are you running 8.1.7.4? If not, upgrade. The CBO has been
> patched on this one to a great extent and behaves a lot
> better. Don't believe the bullshit from SAP that their
> software is not "certified" with patch level 4.
>
> >
> > I remember that most people recommended that it be set to 5, 10,
> > etc.. (Though higher for a DSS system).
>
> I got good results with 40. And the other one,
> OPTIMISER_INDEX_CACHING, set to 80. But you'll
> have to experiment.
>
> Pick the statement off v$sqlarea, paste it into
> a script, go to SQL*Plus and run a few explain plans.
> Do ALTER SESSION SET etc... to try different settings of
> those two and see which combination works. Then give
> it a try system-wide, but be prepared in case things
> go "bump" with a lot of other queries.
>
>
> Best way to handle these things is to use the outlines
> or the DBMS_STATS package to control the behaviour
> for the involved tables. Rather than messing around
> with global settings.
>
>
> >
> > However, SAP recommended that for its BW system, the parameter
> > OPTIMIZER_INDEX_COST_ADJ be removed from the initSID.ora file.
>
>
> They recommend a lot of things that are plain wrong.
> Setting that parameter to 100 or taking it out is
> exactly the same: it's the default value...
>
>
> >
> > (ii) What is the generally recommended setting for
> > OPTIMIZER_INDEX_COST_ADJ
> > for a DSS?
>
> There ain't one. It's not a silver bullet.
Received on Thu Jan 16 2003 - 09:29:36 CST