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 regathering 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—Unsavvy 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.

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.