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

Hello,

On an SAP BW (Business Warehouse, a data warehouse) system running AIX/Oracle v8.1.7, I was asked to check into the performance of some queries.

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).

I checked the initSID.ora file and, indeed, OPTIMIZER_INDEX_COST_ADJ is set to 100.

I remember that most people recommended that it be set to 5, 10, etc.. (Though higher for a DSS system).

However, SAP recommended that for its BW system, the parameter OPTIMIZER_INDEX_COST_ADJ be removed from the initSID.ora file.

I have a couple of questions:

(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?

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

Thanks,
BASIS Consultant Received on Tue Jan 14 2003 - 22:51:02 CST