First, see my tips for troubleshooting a new 10g
migration, and my
Oracle 10g migration tuning tips.
This advice from Greg Rahn, of the Oracle Real-world tuning
> Will dbms_stats someday detect sub-optimal table join orders from a workload,
and create appropriate histograms?
If histograms exist, then they were either automatically created because the
columns met the criteria defined on page 10-11 of the document, or manually
created. If they were created automatically, then is probable they will
influence the plan for the better.
Sub-optimal join orders are generally the result of poor cardinality estimates.
Histograms are designed to help with cardinality estimates where data skew
Keeping statistics: What is the current "official" policy regarding statistics retention?
The old CW was that the DBA should collect a deep, representative sample, and
keep it, only re-analyzing when it's "a difference that makes a difference"?
I don't know if there is an "official" policy per se, but I will offer my
professional opinion based on experience. Start with the dbms_stats defaults.
Modify as necessary based on plan performance. Use dynamic sampling and/or
dbms_sqltune or hints/outlines where appropriate (probably in that order).
Understand the problem before attempting solutions.
There are a couple of cases that I would be mindful of:
1) Experience has shown that poor plans can be a result of under estimated NDV
with skewed data and DBMS_STATS.AUTO_SAMPLE_SIZE
(or too small of a sample). This has been addressed/enhanced in 11g. In 10g it
requires choosing a fixed sample size that yields an accurate enough NDV to get
the optimal plan(s). The sample size will vary case by case as it is data
2) Low/High value issues on recently populated data, specifically with
partitioned tables. If the partition granule size is small (say daily or
smaller) the default 10% stale might be too little. It may be best to gather
partition stats immediately after loading, or set them manually. It's better to
have stats that are an over estimate on the number of rows/values than an under
estimate. For example, its better to have a hash join on a small set of data
than a nested loops on a large set.
Bind peeking: Is cursor_sharing=similar now recommended?
What's your take on the necessary conditions for using it?
My opinion on cursor_sharing=similar is it is a compensation for poor database
programming. It's a band-aid. Fix the code. Use bind variables. That's proper
database coding basics and has been for many years.
Workloads: Is the SQL test case builder similar to the SQL Tuning Set and the 11g
I would say no. SQL Test Case Builder is used to package up a complete test case
(query, ddl, stats, plan, etc) so that the environment can be reproduced and the
problem statement can be debugged on another system.
Upgrade checklist: Is it acceptable to use SQL Profiles instead of plan stability to lock-down
SQL (in test) before migrating?
Stored outlines become obsolete in 10g, right?
SQL Profiles is a 10g feature, not 9i, so it wouldn't be part of a
capture-my-9i-plan strategy. If capturing a 9i plan is what the objective is,
then Stored Outline is the best way. SQL Profiles (dbms_sqltune) is a highly
recommended way to troubleshoot plans in 10g and newer.
Stored Outlines are in 10g, but it is recommended to use a SQL Profile. The
reasoning behind this is that a Stored Outline is a complete set of plan
directives - the plan will never change, even if the data does or a more optimal
plan exists (think new functionality, new heuristics, etc). Think of SQL
Profiles as a applying a compensation or "fudge factor" to the cardinality
calculation. It compares the estimated cardinality with the actual cardinality
and finds the appropriate adjustment constant to apply. This way if the data
size changes, the Optimizer might chose a new and better plan based on the
current data, just applying adjustment factor.
One last suggestion I would make is to unset any optimizer related parameters
that exist in your 9i init.ora, unless you have specific recommendations from
the application vendor. This includes (but is not limited to):
Do some house cleaning. Less is more. Give the 10g Optimizer the best chance to
choose optimal plans and get rid of the old parameter baggage.
The same goes for any events and undocumented/hidden/underscore parameters that
are set. Hidden parameters and events should only be used to temporarily
work around bugs under the guidance and direction of Oracle Support and
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.