Re: OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

Hi,

As I understand it, a sequential (index) read is a single block read, involving a seek-delay, and a rotational delay averaging half the rotation speed.

A multi-block (scattered, FTS) read incurs the seek delay and rotational delay, and then the read-write head just sits-there, sucking-in the table blocks as the cylinder rotates.

Hence, one would expect that the "average" wait time for a scattered read would be smaller, right?

Don Burleson . . . .

basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote in message news:<dd2036f3.0301192023.5e1cad59_at_posting.google.com>...
> 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 Mon Jan 20 2003 - 11:17:00 CST