Back in the day when Oracle used the
rule-based optimizer (up until 2015 in Oracle apps), the
rule-based optimizer would commonly choose an index with poor
Oracle SQL has metadata that
shows the selectivity of the index, and it's rare to see Oracle
choose a sub-optimal index. However, choosing a non
selective index does happen where SQL chooses the wrong index,
even in Oracle 11g.
If your goal for SQL tuning for fast
response time, we seek to get the rows that we want with a
minimum of data block touches. With respect to indexes, one
sure sign of Oracle choosing a "wrong" index (one with less that
optimal selectivity) is excessive db file sequential read,
the type of disk read that indicates index access.
Do do the fast fix from wrong index issues
If you report a sub-optimal index bugs to
Oracle technical support, resist the temptation to set your
optimizer_features_enable parameter. This is a lazy
"workaround" approach that fixes the issue, but does not address
the root cause issue that caused the optimizer to choose the
OTN thread shows an example when the Oracle optimizer in
10.2 chooses an incorrect index (10.2.0.4, AIX5.3, running
PeopleSoft SQL). In this case, the person says that Oracle
technical support fixed the wrong index problem by setting the
optimizer features back to Oracle 9,2:
"they suggested to do "alter session set
optimizer_features_enable='9.2.0';". From then, it is using the
good index (PSAJOB) and the query runs in few seconds."
Again, it's rare for the optimizer
to choose the wrong index, but it does happen, even on the
latest releases of Oracle. Sadly, this example highlights
a "workaround" approach whereby Oracle does not patch the issue
that caused the optimizer to choose the wrong index.
Instead, Oracle suggested that a fast fix
for the SQL using the wrong index was downgrading the
optimizer functionality to an earlier release.
Remember, Oracle statistics can tell you if
you are using a "wrong" index. One sure sign of a sub-optimal
index are cases where you see too many db file sequential
read events to justify fetching the row set.
Also, when you see Oracle use a wrong
index, be conscious of possible "missing
function based indexes.