-
Optimizer enhancements: Oracle says that the lack of
extended statistics is the #1 cause of poor plans in 11g. With
each release of Oracle we see improvements to the SQL optimizer,
and you can expect to see many plan improve, but some which may
get worse. Unless you like surprises, it's an Oracle best
practice to "freeze" SQL execution plans with SQL profiles or
Stored Outlines, and gradually introduce your SQL to the new
optimizer. If you want to revert to an older release of the SQL
optimizer you can enter "alter session set
optimizer_feature_enable=10.1.1."
-
Beware of bug 9842771.
This bug leads to wrong values in AUX_STATS$ for
sreadtim and
mreadtim by factor
1,000 therefore guiding the optimizer sometimes into the totally
wrong direction. The workaround is to overwrite these values
manually and divide them by 10,000. To make this change, use the
dbms_stats.set_system_stats procedure. See this
MOSC Note: 9842771.8 for the above bug for some further
information. This issue is fixed in Oracle Database 11.2.0.3 and
above.
-
SQL Plan Manager: The 10g
SQL profile approach is deprecated, and the 11g SQL plan
management uses only two parameters,
optimizer_capture_sql_plan_baselines and
optimizer_use_sql_plan_baselines.
-
Optimizer statistics: For
all indexes with functions (function-based indexes) make sure to
analyze them with the new 11g
extended optimizer statistics.
-
Double
check your SYSAUX tablespace status: If the SYSAUX
tablespace is offline in 11g, this can cause significant
performance impact because required needed SQL Profiles are not
accessible.
-
ORA-12853 in dbms_stats:
When moving to 11gr2, some users report this error from the
incorrect computation of dbms_stats.default_degree:
DBMS_STATS.GATHER_TABLE_STATS( OWNNAME =>
'owner_name', TABNAME=> 'table_name' ,METHOD_OPT=>'FOR ALL INDEXED
COLUMNS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
DEGREE => DBMS_STATS.DEFAULT_DEGREE,
CASCADE => TRUE, NO_INVALIDATE => TRUE);
ORA-12801: error signaled
in parallel query server P057, instance<instance_name> (2)
ORA-12853: insufficient
memory for PX buffers: current 958640K, max needed 11666304K
ORA-04031: unable to
allocate 65560 bytes of shared memory ("large pool","unknown
object","large pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS",
line 23828