Once your company has made a decision to use the cost-based SQL optimizer (CBO), you must make a
very important decision about your philosophy regarding SQL tuning.
To be successful in a migration to the CBO, you need to understand the
relationship between the CBO statistics (created with the Oracle ANALYZE
command) and the use of the new optimizer plan stability feature in Oracle8i.
Before undertaking a migration to the CBO, you must develop a philosophy
regarding CBO statistics and determine whether you want Oracle to dynamically
change SQL execution plans. This choice is heavily dependent upon the nature of
the Oracle database, and either choice may be optimal depending on the type of
processing.
Some Oracle professionals subscribe to the theory that for any SQL query, there
exists only one optimal execution plan. Once this optimal execution plan is
determined, it should be made persistent with optimizer plan stability. In
contrast, other shops want their SQL to change execution plans whenever there
has been a significant change to the CBO statistics.
Stable shops where the table statistics rarely change will want to employ
optimizer plan stability to make execution plans persistent, while shops where
the CBO statistics frequently change will tune their queries without optimizer
plan stability so that the run-time optimizer is free to choose the most
appropriate execution plan based on the CBO statistics.
The choice of SQL philosophy has a dramatic impact on your approach to SQL
tuning and statistics maintenance. You need to take the following areas into
consideration when making your decision:
- Table and index statistics—The dynamic philosophy relies heavily on the
table and index statistics, and these statistics must be recomputed each
time that a table has a significant change. In contrast, the static
philosophy does not rely on statistics.
- Optimizer plan stability—The dynamic shop does not use optimizer plan
stability because it wants the freedom for the execution plan to change
whenever there is a major change to the data inside the tables. Conversely,
the static shop relies on optimizer plan stability to make its tuning
changes permanent and to improve SQL execution time by avoiding reparsing of
SQL statements.
- Cursor sharing—The dynamic shop often has SQL that is generated by
ad-hoc query tools with hard-coded literal values embedded within the SQL.
As we know, hard-coded literal values make the SQL statements nonreusable
unless cursor_sharing=force is set in the Oracle initialization file.
Shops that are plagued with nonreusable SQL can adopt either the persistent
or the dynamic philosophy. To use optimizer plan stability with nonreusable
SQL, the DBA will set cursor_sharing=force and then extract the
transformed SQL from the library cache and use optimizer plan stability to
make the execution plan persistent.
Let's take a closer look at these competing SQL philosophies so that you can see
which one best fits your organization.
The persistent SQL philosophy
If your shop has relatively static tables and indexes, you may want to adopt the
persistent SQL philosophy that states that there exists only one optimal
execution plan for any SQL statement. Shops that subscribe to this philosophy
are characterized by stable applications that have been tuned to use host
variables (instead of literal values) in all SQL queries.
Persistent shops also have tables and indexes whose recomputed statistics rarely
change the execution plan for their SQL queries, regardless of how often the
statistics are recomputed. Many persistent shops have all of their SQL embedded
inside PL/SQL packages, and the applications will call their SQL using a
standard PL/SQL function of a stored procedure call. This insulates all of the
SQL from the application programs and ensures that all applications execute
identical SQL. It also ensures that all of the SQL has been properly tuned.
Choosing the persistent approach means that all tuned SQL will utilize
optimizer plan stability and that the CBO statistics will be used only for
ad-hoc queries and new queries that have not yet been tuned. Of course, there is
also a performance benefit to using optimizer plan stability because the SQL
statements are preparsed and ready to run. This approach is generally used in
shops where experience has found that the execution plans for SQL rarely change
after the CBO statistics have been reanalyzed.
The persistent SQL philosophy requires the DBA to write scripts to detect all
SQL statements that do not possess stored outlines and to tune these queries on
behalf of the developers. The persistent SQL philosophy also requires less
reliance on CBO statistics, and the DBA generally analyzes tables only when they
are first migrated into the production environment. Since optimizer plan
stability does not rely on statistics, the server overhead of periodically
recomputing statistics for the CBO is avoided.
The dynamic SQL philosophy
The dynamic SQL philosophy subscribes to the belief that Oracle SQL should
change execution plans in accordance with the changes to the CBO statistics.
Shops that subscribe to the dynamic SQL philosophy are characterized by highly
volatile environments where tables and indexes change radically and frequently.
These shops frequently reanalyze their CBO statistics and allow the CBO to
choose the execution plan based upon the current status of their CBO statistics.
A good example of a shop that uses the dynamic SQL philosophy would be one where
tables grow over a specified period of time and then are purged before new data
is reloaded. In these types of environments, the num_rows and avg_row_len for
the tables are frequently changing, as are the distributions of index values.
This change in statistics, in turn, causes the CBO to choose a different
execution plan for the SQL queries.
Decision-support environments and scientific
databases often adopt this philosophy because entirely new subsets of data are
loaded into tables, the data is analyzed, the tables truncated, and a wholly
different set of data is loaded into the table structures.
Another common characteristic of dynamic shops is that the SQL cannot be easily
tuned. Oracle databases that are accessed by casual users via ODBC and
third-party tools such as Crystal Reports or Microsoft Access are often forced
into the dynamic philosophy because the incoming SQL is always different.
However, it is very important to note that the use of third-party application
suites, such as SAP and PeopleSoft, does not always require the adoption of the
dynamic philosophy. The SQL from these types of application suites can be
captured in the library cache, and optimizer plan stability can be used to make
the execution plan persistent.
These shops require a very different approach to SQL tuning than persistent SQL
shops do. Each time new data is loaded or the data changes, the affected tables
and indexes must be reanalyzed. These shops often incorporate the dbms_stats
package directly into their load routines.
In Oracle, the DBA for
dynamic shops must be vigilant for changes to the distribution of index column
values. When column values for any index become skewed, the DBA must create
column histograms for the index so the optimizer can choose between a full-table
scan and an index range scan to service queries. Of course, these shops will
benefit greatly with the use of Oracle9i, where the database will
automatically create column histograms for index columns with skewed
distributions.
Conclusion
Many companies adopt a philosophy without completely realizing the ramifications
of their chosen approach. In practice, most shops begin with a dynamic
philosophy and then undertake to migrate to the static approach after experience
indicates that their execution plans rarely change after a reanalysis of the
tables and indexes.
The Oracle 8i feature of cursor_sharing and optimizer plan
stability are a godsend to many Oracle administrators and offer a proven method
to improve the tuning and persistence of SQL execution plans.
and include the URL for the page.
