OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

Hello,

Once again, thanks to those who replied.

I am still not sure why an average physical I/O read time for an indexed data is different from average physical I/O read time for an FTS. It is not a matter of the data being buffered as we are measuring physical I/O.

Any ideas (Or corrections to the above paragraph) will be appreciated.

Thanks,
BASIS Consultant

basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote in message news:<dd2036f3.0301160729.37609f97_at_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 &#8216;db file s%&#8217;;
>
> ..... 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 &#8216;db file s%&#8217;;
>
> 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 Sun Jan 19 2003 - 22:23:41 CST