Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle speaks on 10g migration tips

Oracle Database Tips by Donald BurlesonMarch 4, 2015


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





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



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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.