This article has the following sections:

• Part 1 - Using SQL CBO Parameters
• Part 2 - Using CBO statistics
• Part 3 - Proper SQL development environment
• Part 4 - Using histograms to tune SQL
• Part 5 - Clustering and SQL tuning
• Part 6 - External costs and SQL execution
• Part 7 - Using hints to tune SQL
• Part 8 - Locating sub-optimal SQL

PART 3 - Proper Development Environment

Many infrastructure issues must be addressed in order to avoid surprises with SQL optimization. Shops that       do not create this infrastructure are plagued by constantly changing SQL execution plans and poor database performance.

The key to success with the CBO is stability and ensuring your success with the CBO involves several      important infrastructure issues.

• Reanalyze statistics only when necessary. One of the most common mistakes made by Oracle DBAs is to frequently re-analyze the schema. Remember, the sole purpose of doing that is to change the execution plans for your SQL, and if it ain't broke, don't fix it. If you are satisfied with your current SQL performance, re-analyzing a schema could cause significant performance problems and undo the tuning efforts of the development staff. In practice, very few shops are sufficiently dynamic to require periodic schema re-analysis.
   
• Force developers to tune their SQL. Many developers falsely assume that their sole goal is to write SQL statements that deliver the correct data from Oracle. In reality, formulating the SQL is only half their job. Successful Oracle shops always require that developers ensure that their SQL accesses the database in an optimal fashion and require migration forms that include the execution plan for all new   SQL.

   

• Carefully manage CBO statistics. Successful Oracle shops carefully manage the CBO statistics to ensure that the CBO works the same in their test and production environments. A savvy DBA will collect high-quality statistics and migrate their production statistics into their test environments. This approach ensures that all SQL migrating into production has the same execution plan as it did in the test database.

   

• Rarely change CBO parameters. The CBO parameters are very dangerous because a single parameter change could adversely affect the performance of an entire enterprise. Changes to critical CBO parameters such as optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching should only be made after careful system testing.

   

• Ensure static execution plans. Nobody like surprises, and successful CBO shops lock down SQL execution plans by carefully controlling CBO statistics, using stored outlines optimizer plan stability, or adding detailed hints to their SQL.

Let's take a closer look at these issues.

Re-analyze statistics only when necessary. It is very rare for the fundamental nature of a schema to change; large tables remain large, and index columns rarely change distribution, cardinality, and skew. You should only consider periodically re-analyzing your total schema statistics if your database matches these criteria:

• Data-analysis databases. Many scientific systems load experimental data, analyze the data, produce reports, and then truncate and reload a new set of experiments. For these types of systems it may be necessary to re-analyze the schema each time the database is reloaded.

   

• Highly volatile databases. In these rare cases, the size of table and the characteristics of index column data changes radically. For example, if you have a table that has 100 rows one week and 10,000 rows the next week, then you may want to consider a periodic reanalysis of statistics.

Force developers to tune their SQL. It is amazing how many Oracle shops do not consider their SQL execution plans. They assume that because the CBO is intelligent and sophisticated it will always provide the best execution plan, no matter what.

Because SQL is a declarative language, a query can be written in many different ways, each with a different execution plan. For example, all of the following SQL queries give the correct answer, but with widely varying execution plans:

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

As we can see, the proper formulation of the query has a dramatic impact on the execution plan for the SQL. Savvy Oracle developers know the most efficient way to code Oracle SQL for optimal execution plans, and savvy Oracle shops train their developers to formulate efficient SQL.

Some techniques for assisting developers in tuning their SQL include:

• Training them to use the autotrace and TKPROF utilities and to interpret SQL execution results. Oracle University has several excellent classes on CBO optimization techniques.

   

• Forcing all SQL that is migrating into production to have verification that the SQL has been tuned.

   

• Making performance an evaluation criterion. Instead of noting that the best developer is the developer who writes SQL the fastest, add the mandate that a good developer also writes SQL that performs efficiently.

Carefully manage CBO statistics. Because the CBO relies on information about database objects, it is imperative that the CBO has the best possible statistics and that the same excellent statistics be used in the production, test, QA, and development instances.

It is an important job of the Oracle DBA to properly gather and distribute statistics for the CBO. The goal of the DBA is to keep the most accurate production statistics for the current processing. In some cases, there may be more than one set of optimal statistics. For example, the best statistics for OLTP processing may not be the best statistics for the data warehouse processing that occurs each evening. In this case, the DBA will keep two sets of statistics and import them into the schema when processing modes change.

Exporting CBO statistics is done with the export_system_stats procedure in the dbms_stats package. In this example we export the current CBO statistics into a table called stats_table_oltp:

dbms_stats.export_system_Stats('stats_table_oltp');

When captured, we can move the table to other instances and use the import_system_stats procedure in dbms_stats to overlay the CBO statistics when processing modes change:

dbms_stats.import_system_stats('stats_table_oltp'); 
dbms_stats.import_system_stats('stats_table_dss');

Change CBO parameters only rarely. Many Oracle shops change the fundamental characteristics of their CBO by changing the global CBO parameters. Especially dangerous are changes to optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching, and these changes should only be made when a sound reason exists. Other CBO parameters such as hash_area_size and sort_area_size are less dangerous and can be set at the individual session level to change the CBO evaluates a query.

Ensure static execution plans. Remember, re-analyzing a schema could cause thousands of SQL statements to change execution plans. Many Oracle shops have implemented standards that require that all SQL, when tested and approved in their test environment, function identically in production.

The only way to achieve this mandate is to migrate the statistics that were used in SQL testing into the production environment when the SQL is migrated. However, the DBA must ensure that a migration of statistics from test into production does not adversely affect the execution plans of other SQL that touch the target table. Hence, the DBA will carefully manage the CBO statistics, ensuring that no SQL changes execution plans after it is migrated into production.

Our tuning tools:

Our Ion tool is the easiest way to analyze Oracle performance and Ion allows you to spot hidden performance trends.

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.