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 






Create a proper development environment for Oracle SQL

In order to provide a consistent SQL execution environment in Oracle there are a number of environmental issues that should be addressed. By ignoring these issues you invite instability, acknowledged by ever-changing SQL execution plans, resulting in poor database query performance.

Successful use of Oracle's Cost-Based Optimizer (CBO) is heavily dependent upon environmental stability that can be ensured by following a few simple guidelines:

  • Ensure static execution plans—By using stored outlines (optimizer plan stability) or by adding detailed hints to SQL queries you can lock-in the SQL execution plan ensuring consistency.
  • Manage CBO statistics effectively—Gather high-quality statistics in your production environment using Oracle's dbms_stats package. Migrate those statistics to your test environment to ensure that the CBO derives equivalent execution plans in both test and production.
  • Only reanalyze statistics when necessary—A common mistake made by many Oracle DBAs is the too frequent reanalyzing of the database schema. It's important to remember that the purpose of re-gathering schema statistics is to change the SQL execution plans for your queries. If you are already satisfied with current query performance, reanalyzing a schema could cause significant performance degradation and ruin the tuning efforts of the development staff. Very few Oracle installations are dynamic enough to require frequent schema reanalysis.
  • Rarely change CBO parameters—Changing the Optimizer initialization parameters can be risky because a single parameter change could have an adverse influence on the performance of the entire database. Changing vital parameters on a production system should only be done after careful evaluation and testing.
  • Require developers to tune their SQL—Uns-avvy developers falsely assume that their singular goal is to compose SQL statements that deliver correct query results. On the other hand, a shrewd developer realizes that formulating the SQL is only half the job and takes steps to ensure that SQL accesses the database in an optimal manner. To enforce compliance across the board, successful organizations require a formal review of the execution plan for all new SQL before production migration can occur.

Let's explore each of these issues in more detail.

Ensure static execution plans

Using stored outlines (optimizer plan stability) is an effective way to ensure that SQL execution plans don't change. Setting up your environment to create and use stored outlines is a fairly simplistic process, but is beyond the scope of this article. Once the stored outline environment is established you can create stored outlines using the following syntax:

[FROM [PUBLIC|PRIVATE] source_outline]
[FOR CATEGORY category_name]

See your Oracle documentation for details on establishing a stored outline environment and creating the stored outline.

Manage CBO statistics effectively

Using the Oracle dbms_stats package, provides much better optimizer statistics than the older ANALYZE TABLE methodology. It is also rumored that future versions of the optimizer will require that statistics be collected using dbms_stats. The Oracle9i version of dbms_stats provides parameters that allow Oracle to determine which objects require new statistics and the percentage of data to be analyzed.

Here is an example of an execution of the dbms_stats package:

execute dbms_stats.gather_schema_stats (
   ownname              => 'SCOTT',
   estimate_percent     => DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt           => 'FOR ALL COLUMNS SIZE AUTO',
   degree               => DBMS_STATS.DEFAULT_DEGREE,
   cascade              => TRUE);

Only reanalyze statistics when necessary

Unless you manage an extraordinary environment, the fundamental nature of a production database rarely changes. In other words, large tables will remain large, small tables will remain small, and index columns will rarely change distribution, cardinality, and skew. Periodic gathering of schema statistics should only be considered if your database matches the following criteria:

  • Scientific data analysis—It is a common practice for scientific systems to load experimental data, analyze that data, produce reports, and then truncate the tables and reload a new set of data for analysis. When you are responsible for administering these types of systems, it may be prudent to reanalyze the schema each time the database is reloaded with new data.
  • Highly volatile tables—In these environments, the size of tables and the characteristics of index columns change dramatically. If, for example, you have a table that has 100 rows one day and 100,000 rows a few days later, then you probably should consider a periodic reanalysis of the schema statistics.

Rarely change CBO parameters

It's important to emphasize the fact that changing the CBO parameters in a production database environment, without careful evaluation, can be risky. A single parameter change could have an adverse influence on the performance of the entire database. Do not change vital initialization parameters such as optimizer_mode, optimizer_index_caching, and optimizer_index_cost_adj on a production system without careful assessment and testing.

Require SQL tuning prior to implementation

Once you realize how crucial efficient execution plans are to query performance, it is amazing to discover how many Oracle installations don't even consider reviewing the execution plans of their production queries. Administrators of those installations may incorrectly assume that the CBO is intelligent enough to always provide the optimal execution plan, no matter how the SQL is coded.

Considering that SQL is a declarative language, queries yielding equivalent results can be written in many ways, each with a different execution plan. The example queries in Listing A all return the correct results, but please observe the differences in the execution plans.

Listing A
-- Form one using non-correlated subquery
  book_key not in (select book_key from sales);
Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)           
   1    0   FILTER                                                             
   2    1     TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)           
   3    1     TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)          
-- Form two using outer join
  book  b,
  sales  s
  b.book_key = s.book_key(+) 
  quantity is null;
Execution Plan
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)       
1  0 FILTER            
2  1   FILTER     
3  2     HASH JOIN (OUTER)                                              
4  3      TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5  3      TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)      
-- Form three using correlated subquery
  book_title not in (
                  book.book_key = sales.book_key
                  quantity > 0);
Execution Plan
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)           
1  0  FILTER                                                             
2  1   TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)           
3  1   FILTER                                                           
4  3     NESTED LOOPS (Cost=6 Card=1 Bytes=82)                          
5  4       TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)         
6  4       TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7  6         INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)     

From these examples, you observe that the proper coding of a query has a dramatic influence on its execution plan. Shrewd developers know the most efficient way to code SQL to produce optimal execution plans. Perceptively managed Oracle installations provide training to their developers to enhance their knowledge of the formulation of efficient queries.

Some suggested techniques for raising the awareness of the importance of SQL tuning and to assist developers in tuning their queries include the following:

  • Job evaluation objectives—It may be prudent to include SQL evaluation as a performance criterion for Oracle developers. The best developer is not necessarily the one producing the most lines of SQL code in the least amount of time. Instead, an effective developer is the one who writes SQL that performs optimally.
  • Strict Management—Require that all SQL that is migrating into production first undergo a formal evaluation of the execution plan to verify that the SQL has been optimally tuned.
  • Training—Provide training to developers regarding the use of autotrace and the TKPROF utility and how to interpret the SQL execution results presented by those utilities. Oracle University has excellent classes on CBO optimization techniques.

Optimal execution

Efficient SQL coding is important in the creation of an optimal execution plan by the CBO. Maintaining a stable SQL environment using the guidelines presented in this article ensures that the SQL you create, tune, and test in your test environment will perform exactly the same when migrated into production.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.




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.