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

CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE
   outline_name
[FROM [PUBLIC|PRIVATE] source_outline]
[FOR CATEGORY category_name]
ON
   sql_statement;

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

• 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 select   book_title from   book where   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 select   book_title from   book  b,   sales  s where   b.book_key = s.book_key(+)  and   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 select   book_title from   book where   book_title not in (                 select                 distinct                   book_title                 from                   book,                   sales                 where                   book.book_key = sales.book_key                 and                   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.