Re: Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?

On 16 Jan 2003 07:29:36 -0800, basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote:

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

...

IMO, your estimate will be always to high. Part of the index accesses can be resolved in buffer, so a sequential read will not be necessary in those cases. This will speed up the average index access time considerably. Every access in a full table scan on the other hand will be needing other blocks than the previous access, so only a very small part of a full table scan can be resolved in buffer and will therefore not be needing a scattered read.

Apart from this: what does OPTIMIZER_INDEX_COST_ADJ=100 mean? Does the optimizer compare the access of a record? of a block? of an index access to a full table scan access?

My experience so far is that if I have a performance problem in Oracle 8i and it is an issue of FTS versus indexes, the optimizer always erroneously chooses FTS before indexes, and performance is enhanced by lowering OPTIMIZER_INDEX_COST_ADJ.

Jaap. Received on Sat Jan 18 2003 - 04:04:50 CST