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

> (i) If OPTIMIZER_INDEX_COST_ADJ is removed from the initSID.ora file,
> the optimizer will have to assume a specific value for it. What is it?
>

Exactly the same value as you have now - 100. This parameter sets index access cost adjustment percentage for the CBO, 100 causes CBO to cost index access at regular cost, 50 will cause it to cost them at 1/2 of regular cost. You may also increase this value if you feel that there are too many index access paths. Its valid values range is between 1 and 10000.

> (ii) What is the generally recommended setting for
> OPTIMIZER_INDEX_COST_ADJ for a DSS?

Well, there's no one-size-fits-all setting for this parameter. I think that for DSS it may be left at default value - they are supposed to process massive amounts of data and indexed access may just slow things down. However, you should experiment with different settings for this parameter, which you can do without the need for init.ora modifications and instance restarts - this parameter is session-modifiable. For queries with joins, I'd also look at HASH_JOIN_ENABLED and make sure it is TRUE - hash joins are the fastest (at the expense of extra PGA memory required for hash tables, controlled by HASH_AREA_SIZE.) Please note that I am not DWing expert and may be wrong here. But if I am, I'm sure someone in this NG will correct me. :)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.