Question: I saved myself a heap of problem with
10g upgrade gotchas and I wonder what perils await me as I
prepare to migrate to Oracle 11g?
Answer: There are several 11g upgrade gotchas,
small surprises that can effect your database performance when you
migrate to 11g. First, MAKE SURE that you follow the
directions and capture all of your
10g SQL plans.
Also run dupgdiag.sql to check the
database integrity prior to migrating to 11g.
Cardinality feedback - This
user determined an issue with cardinality feedback causing poor
SQL performance and fixed it by setting
AMM Bug: See this
important AMM bug and the associated fix
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."
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
Optimizer statistics: For all indexes
with functions (function-based indexes) make sure to analyze
them with the new 11g
extended optimizer statistics.
Setting temp filesystem issues: There is
an issue with the tmpfs size, causing ORA-00845 error
even though you have created a swap partition/file greater than
your database memory_target (see AMM Bug). The default size of
tmpfs on Linux (2.4 kernel and above) is approximately half the
physical system memory. This limitation can be changed by
editing the /etc/fstab file, removing the "defaults" attribute
and specifying a maximum size for tmpfs.
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
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)
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",
- Resource Manager enables itself: The
11g resource manager now turns-on by default. Check
the value of the resource_manager_plan init.ora parameter.
If it is set to DEFAULT_PLAN you may want to change it to a more
appropriate value, or use the following command to disable the
alter system set resource_manager_plan='';
- IN-List costing:
There are many reports of shops
migrating to Oracle 11g who suddenly experience SQL slowdowns.
One of the parameters that Oracle technical support is
recommending is to set _optimizer_better_inlist_costing=true.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.