Re: Removing OPTIMIZER_INDEX_COST_ADJ From Init<SYS>.ora, or Default Setting for DSS.

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.

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam