Later, you change the OPTIMIZER_FEATURES_ENABLE parameter value to less than 11.2.0.4 (for example, you downgraded Oracle Database and set the OPTIMIZER_FEATURES_ENABLE parameter to a smaller value). The OPTIMIZER_FEATURES_ENABLE parameter is set to a value of 11.2.0.4. The tables have CHAR or NCHAR data type columns. Gathering statistics for tables that have all of the following scenarios can also cause suboptimal plans: When you collect statistics, set the NO_INVALIDATE parameter to FALSE so that the existing cursors (with suboptimal plans) are not shared when SQL statements are executed again.
This workaround eliminates the need for the patch for bug 18255105. Instead of using the GATHER_TABLE_STATS procedure on the production system, gather statistics on a test system, export the statistics to a user statistics table, and then import the statistics into the production system.
This patch also helps if you are using automatic statistics gathering or if you are using manual statistics gathering (with either the GATHER AUTO or GATHER STALE option) to gather statistics on the problematic tables.Īnother workaround is to find the tables that have CHAR or NCHAR data type columns that have histogram statistics (using the DBA_TAB_COL_STATISTICS view) and execute the GATHER_TABLE_STATS procedure on them. For CHAR or NCHAR data type columns that have histogram statistics, this patch marks them as stale.
One workaround for this issue is to apply the patch for bug 18255105. Section 2.13, "Oracle Real Application Clusters"Ģ.1.1 Upgrading to Release 11.2.0.4 Generates Suboptimal Plans for CHAR or NCHAR Data Type ColumnsĪfter upgrading to Oracle Database 11g release 2 (11.2.0.4), the optimizer generates suboptimal plans for CHAR or NCHAR data type columns that have histogram statistics and when the OPTIMIZER_FEATURES_ENABLE parameter is set to a value of 11.2.0.4 (which is the default value in Oracle Database 11g release 2 (11.2.0.4)). Section 2.10, "Oracle Grid Infrastructure for a Cluster" Section 2.9, "Oracle Automatic Storage Management (Oracle ASM)" Section 2.8, "Oracle Application Express" Section 2.3, "Deprecated and Desupported Features for Oracle Database" Section 2.2, "Features Not Available or Restricted in 11.2.0.4"
Section 2.1, "Compatibility, Upgrading, Downgrading, and Installation" This section of the Readme contains the following sub-sections: Note: If you are on Oracle Database 11g release 2 (11.2.0.4), then this is the Readme section that you need to read.