Oracle speaks on 10g migration tips

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 group:

> 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 exists.

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 dependent.

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 workload?

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):

optimizer_index_caching 

optimizer_index_cost_adj

optimizer_mode     

db_file_multiblock_read_count

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.