Re: Odd behavior of function-based index after DB upgrade

From: Randolf Geist <info_at_www.sqltools-plusplus.org>
Date: Thu, 06 Oct 2011 09:20:57 +0200
Message-ID: <4E8D56D9.nailKI91CMGAU_at_sqltools-plusplus.org>



What you should be looking for first in such cases is to compare the costs of the plans when forcing the index usage via index hints (your plan posted didn't include the costs which you can add to the ALLSTATS LAST format by adding +COST to the format).

This way you can verify if the index will be considered at all by the CBO (if the index is rejected even when hinted) and what are the cost comparisons.

You should also pay close attention to the "Predicate Information" section of the plan which will reveal some more details about what kind of predicates will be applied by the optimizer.

One possible but unlikely scenario would be the usage of a non-default NLS_COMP / NLS_SORT setting after the upgrade causing Oracle to modify the actual predicates applied - again something that would be visible from the predicates section of the plan.

The plan posted is a bit mangled but seems a bit odd to me that you only get E-Rows for the two TABLE ACCESS FULL row sources - the E-Rows however seem to suggest that the available indexes ought to be used. In principle you could also be subject to bind variable peeking issues - check the output of DBMS_XPLAN.DISPLAY_CURSOR with the "+PEEKED_BINDS" option, but again this should show up as greater cardinality estimates if it was a problem.

The suggestion about the hidden columns is correct in principle but if you used the defaults to gather the table stats these hidden columns are supposed to have statistics gathered - you can verify this by looking at DBA_TAB_COL_STATISTICS or DBA_TAB_COLS.

Hope this helps,
Randolf

> We have recently upgraded one of our Oracle ERP (11i.10.2) systems
> database from 10.2.0.4 to 11.1.0.7. After the upgrade, we are seeing
> some odd behavior on a custom job that was working fine prior to the
> upgrade. Prior to the upgrade, the job was using a function-based index,
> however, it has stopped using that index after the upgrade.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 06 2011 - 02:20:57 CDT

Original text of this message