Hi All, It's version 11.2.0.4 of Oracle.
We are having a situation in which multiple queries suddenly changed the
execution path and deviated from the local indexed access path to the FTS
path(partition range all) for a daily range partitioned table. And then
after debugging into the issue we found that the COST of the index access
actually has been increased because of the increase in BLEVEL of the index.
And the BLEVEL has been increased from 2 to 3 in global level(dba_indexes)
because of one of the missing daily partitions. For e.g. 14th jun partition
was missing , so 15th jun partition now holds both 14th june and 15th jun
data, so its twice as bigger as a normal daily partition.
The missing day range partition caused the subsequent day partitions to
actually hold two partitions/days worth of data and thus the blevel of that
specific local index increased from 2 to -3 for that index partition. and
since then, in global level(dba_indexes) the blevel is staying as-3 for
that index, costing that index access higher and thus making plan change
for many queries.
My question is how should we handle such a situation ? Should we go back
and break the specific partition(15th Jun partition) into two daily range
partitions i.e. 14th and 15th (which i believe is going to take lot of time
and resources considering this table has ~8 different local indexes in it)
and gather stats again or is there any other way to just tweak the stats
for that one big partition such that the global index BLEVEl can be back to
-2 , and get this issue fixed?
Regards
Lok
