Re: optmizer mode

If there has been a dramatic performance degradation after the tables were analyzed then the execution plans being chosen by the CBO are evidently different to those preferred by the RBO, and henve you can find out what the difference is by using the "set autotrace traceonly" command in SQL*Plus prior to executing the queries in both modes. typically my own experience has shown that the CBO is less aggressive in using indexes than the RBO, and this is partly because the default setting of "100" for the optimizer_index_cost_adj parameter is too high. Generally a value between 15 and 25 wil give you a better balance.

Read up on the supplied procedure DBMS_STATS.GATHER_SYSTEM_STATS(). This prompts Oracle to monitor the execution of SQL and to determine some benchmarks for the storage and CPU subsystems. Although not foolproof it does effectively tailor the index cost adjustment, and you can maintain different values for different operational environments
(eg. daytime OLTP ops, and nighttime batch ops).

If genweral system performance is improved by these measures then you can identify the less performant SQL in the application using "statspack", and eliminate those as problems through individual tuning
(re-write of SQL, adding/removing indexes etc)

I don't follow the issue with not being able to create new indexes, though. Received on Tue Feb 15 2005 - 10:19:42 CST