Re: OPTIMIZER_INDEX_COST_ADJ, Conceptual Idea

 Didn't Hotsos do this topic ad-infinitum recently ?

and do we assume no effect from disk buffer cache / raiding ...

hower one could postulate that it <might> take less time for a FTS 4 block rather than a sequential 1 block + rotate delay BUT

this would rarely be the case. one would need lots of testing to see the breakovers and where the good fruit is... on a wide range of hardware devices... then somehow get this good stuff into the optimizer... "Don Burleson" <don_at_burleson.cc> wrote in message news:998d28f7.0301200917.4470ef47_at_posting.google.com...
> 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:37:58 CST