Cost Control: Inside the Oracle Optimizer

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

So let's start by examining the CBO optimizer modes and the Oracle
parameters that influence the CBO.

PART 1 - CBO Parameters

The CBO is influenced by many configuration settings. Your settings for

important CBO parameters can have a dramatic impact of CBO
performance, and this is the place to start when exploring the CBO.
Let's start by choosing your CBO optimizer_mode and then examine other
important CBO parameters.

The CBO and optimizer modes. In Oracle9i Database there are four
optimizer modes, all determined by the value of the optimizer_mode
parameter: rule, choose, all_rows, and first_rows. The rule and choose modes
reflect the obsolete rule-based optimizer, so we will focus on the CBO
modes here.

The optimizer mode can be set at the system-wide level, for an

individual session, or for a specific SQL statement:

alter system set optimizer_mode=first_rows_10;

alter session set optimizer_goal = all_rows;
select /*+ first_rows(100) */ from student;
We need to start by defining what is the "best" execution plan for a
SQL statement. Is the best execution plan the one that begins to return
rows the fastest, or is the best execution plan the one that executes
with the smallest amount of computing resources? Of course, the
answer depends on the processing needs of your database.
Let's take a simple example. Assume the following query:
select customer_name
from
customer
where
region = 'south'
order by
customer_name;
If the best execution plan is the one that starts to return rows the
fastest, a concatenated index on region and customer_name could be used
to immediately start delivering table rows in their proper order, even
though excess I/O will be required to read the nonadjacent data blocks
(see Figure 1).

 
Let's assume that this execution plan starts delivering results in .0001
seconds and requires 10,000 db_block_gets. But what if your goal is to
minimize computing resources? If this SQL is inside a batch  program,
then it is not important to start returning rows quickly, and a different
execution plan would take      fewer resources. In this example, a
parallel full-table scan followed by a back-end sort will require less      
machine resources and less I/O because blocks do not have to be
reread to pull the data in sorted order (see Figure 2). In this example,
we expect the result to take longer to deliver (no rows until the sort is
complete), but we will see far less I/O because blocks will not have to
be reaccessed to deliver the rows in presorted order.
Let's assume that this execution plan delivers the result in 10 seconds
with 5,000 db_block_gets.

 
Oracle offers several optimizer modes that allow you to choose your
definition of the "best" execution plan for you:
 optimizer_mode=first_rows_ This CBO mode will return rows as soon as
possible, even if the overall query runs longer or consumes more computing
resources than other plans. The first_rows optimizer_mode usually involves
choosing an index scan over a full-table scan because index access    will return
rows quickly. Because the first_rows mode favors index scans over full-table
scans, the first_rows mode is more appropriate for OLTP systems where the end
user needs to see small result sets as quickly as possible.

 optimizer_mode=all_rows_ This CBO mode ensures that the overall

computing resources are minimized, even if no rows are available until the entire
query has completed. The all_rows access method often favors a parallel full-table
scan over a full-index scan, and sorting over presorted retrieval via an index.
Because the all_rows mode favors full-table scans, it is best suited for data
warehouses, decision-support systems, and batch-oriented databases where
intermediate rows are not required for  real-time viewing.

 optimizer_mode=first_rows_n This Oracle9i Database optimizer mode

enhancement optimizes queries for a small, expected return set. The values are
first_rows_1, first_rows_10, first_rows_100, and first_rows_1000. The CBO uses
the n in first_rows_n as an important driver in determining cardinalities for query
result sets. By telling the CBO, a priori, that we only expect a certain number of
rows back from the query, the CBO will be able to make a better decision about
whether to use an index to access the table rows.

 Optimizer_mode=rule The rule-based optimizer (RBO) is the archaic

optimizer mode from the earliest releases of Oracle Database. The rule-based
optimizer has not been updated in nearly a decade and is not recommended for
production use because the RBO does not support any new features of  Oracle
since 1994 (such as bitmap indexes, table partitions, and function-based indexes).

While the optimizer_mode is the single most important factor in invoking

the cost-based optimizer, there are other parameters that influence
the CBO behavior. Let's take a quick look at these parameters.
Oracle parameters that influence the CBO. While the optimizer_mode
parameter governs the global       behavior of the CBO, there are many
other Oracle parameters that have a great impact on CBO behavior.
Because of the power of the CBO, Oracle provides several system-level
parameters that can adjust the       overall behavior of the CBO. These
adjustment parameters generally involve the choice of using an
index      versus doing a full-table scan, and the CBO's choice of table
join methods.
However, Oracle does not recommend changing the default values for
many of these CBO setting because        the changes can affect the
execution plans for thousands of SQL statements. Here are the major
optimizer  parameters:
 optimizer_index_cost_adj_ This parameter alters the costing algorithm for
access paths involving indexes. The smaller the value, the lower the cost of index
access.

 optimizer_index_caching_ This parameter tells Oracle how much of your

index is likely to be in the RAM data buffer cache. The setting for
optimizer_index_caching affects the CBO's decision to use an index for a table
join (nested loops) or to favor a full-table scan.

 db_file_multiblock_read_count_ When this parameter is set to a high value,

the CBO       recognizes that scattered (multiblock) reads may be less expensive
than sequential reads. This makes       the CBO friendlier to full-table scans.

 parallel_automatic_tuning_ When set to "on", this parameter parallelizes full-

table scans .     Because parallel full-table scans are very fast, the CBO will give a
higher cost to index access and be friendlier to full-table scans.

 hash_area_size (if not using pga_aggregate_target) _ The setting for

hash_area_size  parameter governs the propensity of the CBO to favor hash joins
over nested loop and sort merge table joins.

 sort_area_size (if not using pga_aggregate_target) _ The sort_area_size

influences the    CBO when deciding whether to perform an index access or a sort
of the result set. The higher the value for sort_area_size, the more likely that a
sort will be performed in RAM, and the more likely that the CBO will favor a sort
over presorted index retrieval.

The idea optimizer settings depend on your environment and are

heavily influenced by your system's costs for scattered disk reads
versus sequential disk reads. Listing 1 contains a great script you can
use to measure these I/O costs on your database.
LISTING 1: optimizer_index_cost_adj.sql
col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;
Now that we understand the CBO parameters, let's look at how we can
help the CBO make good execution-plan decisions by providing the
CBO with information about our schema.

PART 2 - CBO Statistics

The most important key to success with the CBO is to carefully define
and manage your statistics. In order for the CBO to make an intelligent
decision about the best execution plan for your SQL, it must
have information about the table and indexes that participate in the
query. When the CBO knows the size of the tables and the distribution,
cardinality, and selectivity of column values, the CBO can make an
informed decision and almost always generates the best execution
plan.

Let's examine the following areas of CBO statistics and see how to
gather top-quality statistics for the CBO and how to create an
appropriate CBO environment for your database.

Getting top-quality statistics for the CBO. The choices of executions

plans made by the CBO are only as good as the statistics available to
it. The old-fashioned analyze table and dbms_utility methods for generating
CBO statistics are obsolete and somewhat dangerous to SQL
performance. As we may know, the CBO uses object statistics to
choose the best execution plan for all SQL statements.
The dbms_stats utility does a far better job in estimating statistics,
especially for large partitioned tables, and the better statistics result in
faster SQL execution plans. Here is a sample execution of dbms_stats
with the OPTIONS clause:

exec dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 34 -
)
There are several values for the OPTIONS parameter that we need to
know about:
 GATHER_ reanalyzes the whole schema
 
 GATHER EMPTY_ only analyzes tables that have no existing statistics
 
 GATHER STALE_ only reanalyzes tables with more than 10 percent modifications
(inserts, updates,   deletes)
 
 GATHER AUTO_ will reanalyze objects that currently have no statistics and objects
with stale statistics.  Using GATHER AUTO is like combining GATHER STALE and
GATHER EMPTY.

Note that both GATHER STALE and GATHER AUTO require monitoring. If you
issue the ALTER TABLE XXX MONITORING command, Oracle tracks changed
tables with the dba_tab_modifications view. Below we see that the exact
number of inserts, updates and deletes are tracked since the last
analysis of statistics:
SQL> desc dba_tab_modifications;

Name Type
--------------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
The most interesting of these options is the GATHER STALE option.
Because all statistics will become stale   quickly in a robust OLTP
database, we must remember the rule for GATHER STALE is > 10% row
change   (based on num_rows at statistics collection time). Hence,
almost every table except read-only tables will be reanalyzed with the
GATHER STALE option, making the GATHER STALE option best for systems
that are       largely read-only. For example, if only five percent of the
database tables get significant updates, then only        five percent of
the tables will be reanalyzed with the GATHER STALE option.
Automating sample size with dbms_stats.The better the quality of the
statistics, the better the job that the    CBO will do when determining
your execution plans. Unfortunately, doing a complete analysis on a
large  database could take days, and most shops must sample your
database to get CBO statistics. The goal is to take a large enough
sample of the database to provide top-quality data for the CBO.
Now that we see how the dbms_stats option works, let's see how to
specify an adequate sample size for dbms_stats.
In earlier releases, the DBA had to guess what percentage of the
database provided the best sample size and sometimes underanalyzed
the schema. Starting with Oracle9i Database, the estimate_percent
argument is a great way to allow Oracle's dbms_stats to automatically
estimate the "best" percentage of a segment to sample when
gathering statistics:
estimate_percent => dbms_stats.auto_sample_size
After collecting automatic sample sizes, you can verify the accuracy of
the automatic statistics sampling by    looking at the sample_size column
on any of these data dictionary views:
 DBA_ALL_TABLES
 DBA_INDEXES
 DBA_IND_PARTITIONS
 DBA_IND_SUBPARTITIONS
 DBA_OBJECT_TABLES
 DBA_PART_COL_STATISTICS
 DBA_SUBPART_COL_STATISTICS
 DBA_TABLES
 DBA_TAB_COLS
 DBA_TAB_COLUMNS
 DBA_TAB_COL_STATISTICS
 DBA_TAB_PARTITIONS
 DBA_TAB_SUBPARTITIONS

Note that Oracle generally chooses a sample_size from 5 to 20 percent

when using automatic sampling, depending on the size of the tables
and the distribution of column values. Remember, the better the
quality of  your statistics, the better the decision of the CBO.
Now that we understand the value of CBO statistics, let's look at ways
that the CBO statistics are managed in a successful Oracle shop.

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.

PART 4 - Considering the CBO

While we have gone into great detail on the CBO, there is always more
to learn as the CBO becomes more powerful (and complex) with each
new release of Oracle. The main points of this article include
general guidelines for adjusting the behavior of the CBO:
 The DBA can control the overall behavior of the CBO with several Oracle
parameters, but they should only be changed under limited circumstances.
 
 The CBO relies on statistics to determine the optimal execution plan for SQL
statements, and statistics should always be collected with the dbms_stats
package.
 
 An important job of the Oracle DBA is the collection and management of
statistics for the CBO. CBO statistics can be collected, stored, and migrated to
other related instances to ensure consistency of execution plans.
 
 Re-analyzing schema statistics without exporting the old statistics (using
export_system_stats) is dangerous because the execution plans for thousands of
SQL statements may change, and you cannot     get back to previous SQL
performance. Re-analyze a schema only when there are significant changes to the
data.

In Part 1 of the series, we discussed the basic mechanisms of Oracle's

cost-based SQL optimizer for making the best decisions about the
access paths to data. In this concluding installment, we'll address the
use of histograms, external costing features, SQL hints for changing
execution plans, and techniques for locating and tuning suboptimal
SQL.
Using Histograms
In some cases, the distribution of values within a column of a table will
affect the optimizer's decision to use an index vs. perform a full-table
scan. This scenario occurs when the value with a where clause has a
disproportional amount of values, making a full-table scan cheaper
than index access.
A column histogram should only be created when we have data skew
exists or is suspected. In the real world,   that happens rarely, and one
of the most common mistakes with the optimizer is the unnecessary
introduction of histograms into optimizer statistics. The histograms
signals the optimizer that the column is not linearly distributed, and
the optimizer will peek into the literal value in the SQL where clause
and compare that value to the histogram buckets in the histogram
statistics (see Figure 3).

 
Many Oracle professionals misunderstand the purpose of histograms.
While they are used to make a yes-or-no decision about the use of an
index to access the table, histograms are most commonly used to
predict the size of the intermediate result set from a multi-way table
join.
For example, assume that we have a five-way table join whose result
set will be only 10 rows. Oracle will want to join the tables together in
such a way as to make the result set (cardinality) of the first join as
small as possible. By carrying less baggage in the intermediate result
sets, the query will run faster. To minimize intermediate results, the
optimizer attempts to estimate the cardinality of each result set during
the parse phase of SQL execution. Having histograms on skewed
column will greatly aid the optimizer in making a proper decision.
(Remember, you can create a histogram even if the column does not
have an index and does not participate as a join key.)
Because a complex schema might have tens of thousands of columns,
it is impractical to evaluate each column for skew and thus Oracle
provides an automated method for building histograms as part of the
dbms_stats utility. By using the method_opt=>'for all columns size skewonly'
option of dbms_stats, you can direct Oracle to automatically create
histograms for those columns whose values are heavily skewed. We'll
take a look at this option in more detail later.
As a general rule, histograms are used to predict the cardinality and
the number of rows returned in the result set. For example, assume
that we have a product_type index and 70% of the values are for the
HARDWARE type. Whenever SQL with where product_type='HARDWARE'is
specified, a full-table scan is the fastest execution plan, while a query
with where product_type='SOFTWARE' would be fastest using index access.
Because histograms add additional overhead to the parsing phase of
SQL, you should avoid them unless they are required for a faster
optimizer execution plan. But there are several conditions where
creating histograms is advised:
 
 When the column is referenced in a query — Remember, there is no point in
creating histograms if the queries do not reference the column. This mistake is
common, and many DBAs will create histograms on a skewed column, even
though it is not referenced by any queries.
 
 When there is a significant skew in the distribution of columns values —This
skew should be sufficiently significant that the value in the WHERE clause will
make the optimizer choose a different execution plan.
 
 When the column values cause an incorrect assumption — If the optimizer
makes an incorrect guess about the size of an intermediate result set it may choose
a sub-optimal table join method. Adding a histogram to this column will often
provide the information required for the optimizer to use the best join method.

So how do we find those columns that are appropriate for histograms?

One exciting feature of dbms_stats is the ability to automatically look for
columns that should have histograms, and create the histograms.
Again, remember that multi-bucket histograms add a huge parsing
overhead to SQL statements, and histograms should only be used
when the SQL will choose a different execution plan based upon the
column value.
To aid in intelligent histogram generation, Oracle uses the method_opt
parameter of dbms_stats. There are also important new options within
the method_opt clause, namely skewonly, repeat and auto.
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
Let's take a close look at each method option.
The first is the "skewonly" option, which is very time-intensive because
it examines the distribution of values for every column within every
index. If dbms_stats discovers an index with columns that are unevenly
distributed, it will create histograms for that index to aid the cost-
based SQL optimizer in making a decision about index vs. full-table
scan access. For example, if an index has one column that is in 50% of
the rows, a full-table scan is faster than and index scan to retrieve
these rows.
Histograms are also used with SQL that has bind variables and SQL
with cursor_sharing enabled. In these cases, the optimizer determines if
the column value could affect the execution plan, and if so,
replaced the bind variable with a literal and performs a hard parse:
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/

The auto option is used when monitoring is implemented (alter table xxx
monitoring;) and creates histograms based upon data distribution and
the manner in which the column is accessed by the application (e.g.
the workload on the column as determined by monitoring). Using
method_opt=>'auto'is similar to using the gather auto in the option
parameter of dbms_stats:
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
/

PART 5 - Selectivity, Clustering, and Histograms

It is important to remember that the optimizer has knowledge of many

important characteristics of column data within tables, most notably
the selectivity of a column values and the clustering factor for the
column.

For example, here we see a query using a column value to filer the
result set:

select
customer_name
from
customer
where
customer_state = 'Rhode Island';
In this example, the choice to use an index versus a full-table scan is
influenced by the proportion of customers in Rhode Island. If there are
a super-small proportion of customers in Rhode Island and the values
are clustered on the data blocks, then an index scan might be the
fastest execution plan for this query.
Many Oracle developers are perplexed when the optimizer chooses a
full-table scan when they are only retrieving a small number of rows,
not realizing that the optimizer is considering the clustering of the
column values within the table.
Oracle provides a column called clustering_factor in the dba_indexes view
that tells the optimizer how      synchronized the table rows are with
the index. When the clustering factor is close to the number of
data blocks, the table rows are synchronized with the index.
The selectivity of a column value, the db_block_size, the avg_row_len and
the cardinality all work together in helping the optimizer decide
whether to use and index versus using a full-table scan. If a
data column has high selectivity and a low clustering_factor, then an
index scan is usually the fastest execution method
(see Figure 4).

 
In cases where most of the SQL references a column with a high
clustering_factor, a large db_block_size and a small avg_row_len, the DBA will
sometimes periodically re-sequence the table rows or use a single-
table cluster to maintain row order. This approach places all adjacent
rows in the same data block, removing the full-table scan and making
the query up to 30x faster.
Conversely, a high clustering_factor, where the value approaches the
number of rows in the table (num_rows), indicates that the rows are not
in the same sequence as the index, and additional I/O will be  required
for index range scans. As the clustering_factor approaches the number of
rows in the table, the rows are out of sync with the index.
However, even if a column has high selectivity, a high clustering_factor
and small avg_row_len will indicates that the column values are
randomly distributed across the table, and additional I/O will be
required to fetch the rows. In these cases, an index range scan would
cause a huge amount of unnecessary I/O (see Figure 5); a full-table
scan would be far more efficient.
 
In sum, the clustering_factor, db_block_size and avg_row_len all influence the
optimizer's decision about performing a full-table scan versus an index
range scan, and it is important to understand how these statistics are
used by the optimizer.
As we have noted, the optimizer improves with each new release, and
the latest enhancement with Oracle Database 10g is the consideration
of external influences when determining an execution plan. Oracle
calls this feature external costing and includes both CPU and I/O cost
estimates.

PART 6 - External Costing with the Optimizer

Starting in Oracle9i Database and continuing in Oracle Database 10g,

the optimizer has been enhanced to consider external influences when
determining the best execution plan. Because the Oracle Database
does not run in a vacuum, the optimizer must be able to factor-in the
costs of external disk I/O and the cost of CPU cycles for each SQL
operation. This process is especially critical for queries running all_rows  
optimization, where minimizing server resources is a primary goal.

 CPU_COST — The optimizer can now estimate the number of machine cycles
required for an operation, and factors this cost into the execution plan calculation.
The CPU costs associated with  servicing an Oracle query depends upon the
current server configuration (which Oracle cannot see). In Oracle Database 10g,
CPU costing is the default behavior because of the importance of considering the
CPU costs associated with each SQL execution phase—thus, the savvy Oracle
professional should turn on CPU costing with dbms_stats.get_system_stats.
CPU costs are generally not important unless the entire Oracle instance is using
excessive CPU resources.
 
 IO_COST — The optimizer had been enhanced to estimate the number of
physical block reads required for an operation. The I/O cost is proportional to the
number of physical data blocks read by the operation. However, the optimizer has
no a priori knowledge of the data buffer contents and cannot distinguish between
a logical read (in-buffer) and a physical read. Because of this shortcoming, the
optimizer cannot know if the data blocks are already in the RAM data buffers.

According to the Oracle documentation, the I/O and CPU costs are
evaluated as follows:
 
Cost = (#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed ) / sreadtim

where:

#SRDs - number of single block reads

#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles *)

sreadtim - single block read time

mreadtim - multi block read time
cpuspeed - CPU cycles per second
Note that the costs are a function of the number of reads and the
relative read times, plus the CPU cost estimate for the query. Also note
the external costing does not consider the number of data blocks that
reside in the RAM data buffers, but a future release of the optimizer is
likely to consider this factor.
Here we see that Oracle uses the both the CPU and I/O cost
estimations in evaluating the execution plans. This equation becomes
even more complex when we factor-in parallel query where many
concurrent processes are servicing the query.
The best benefit for using CPU costing is for all_rows execution plans
where costs is more important than with first_rows optimization.
Next, let's look at how the optimizer is influenced by statistics. In order
to make an intelligent decision about the best execution plan, the
optimizer must use information about all of the data objects that are
involved in the query. Because you control how the statistics are
collected, this aspect of optimizer tuning is a critical.

PART 7 - Using Hints to Change Execution Plans

As the optimizer becomes more sophisticated with each release,

Oracle provides an increasing number of methods for changing the
execution plans for your SQL. The most common use for Oracle hints is
as a debugging tool. You can use the hints to determine the optimal
execution plan, and then work backward, adjusting the statistics to
make the vanilla SQL simulate the hinted query.

Using Oracle hints can be very complicated and Oracle developers only
use hints as a last resort, preferring to alter the statistics to change the
execution plan. Oracle contains more than 124 hints, and many of
them are not found in the Oracle documentation. (See Listing 2)

Listing 2: Documented Oracle Hints:

ALL_ROWS INDEX_SS_ASC PARALLEL

AND_EQUAL INDEX_SS_DESC PARALLEL_INDEX
ANTIJOIN INLINE PIV_GB
APPEND LEADING PIV_SSF
BITMAP LIKE_EXPAND PQ_DISTRIBUTE
BUFFER LOCAL_INDEXESMATERIALIZE PQ_MAP
BYPASS_RECURSIVE_CHECK MERGE PQ_NOMAP
BYPASS_UJVC MERGE_AJ PUSH_PRED
CACHE MERGE_SJ PUSH_SUBQ
CACHE_CB MV_MERGE REMOTE_MAPPED
CACHE_TEMP_TABLE NESTED_TABLE_GET_REFS RESTORE_AS_INTERVALS
CARDINALITY NESTED_TABLE_SET_REFS REWRITE
CHOOSE NESTED_TABLE_SET_SETID RULE
CIV_GB NL_AJ SAVE_AS_INTERVALS
COLLECTIONS_GET_REFS NL_SJ SCN_ASCENDING
CPU_COSTING NO_ACCESS SELECTIVITY
CUBE_GB NO_BUFFER SEMIJOIN
CURSOR_SHARING_EXACT NO_EXPAND SEMIJOIN_DRIVER
DEREF_NO_REWRITE NO_EXPAND_GSET_TO_UNION SKIP_EXT_OPTIMIZER
DML_UPDATE NO_FACT SQLLDR
DOMAIN_INDEX_NO_SORT NO_FILTERING STAR
DOMAIN_INDEX_SORT NO_INDEX STAR_TRANSFORMATION
DRIVING_SITE NO_MERGE SWAP_JOIN_INPUTS
DYNAMIC_SAMPLING NO_MONITORING SYS_DL_CURSOR
DYNAMIC_SAMPLING_EST_CDN NO_ORDER_ROLLUPS SYS_PARALLEL_TXN
EXPAND_GSET_TO_UNION NO_PRUNE_GSETS SYS_RID_ORDER
FACT NO_PUSH_PRED TIV_GB
FIRST_ROWS NO_PUSH_SUBQ TIV_SSF
FORCE_SAMPLE_BLOCK NO_QKN_BUFF UNNEST
FULL NO_SEMIJOIN USE_ANTI
GBY_CONC_ROLLUP NO_STATS_GSETS USE_CONCAT
GLOBAL_TABLE_HINTS NO_UNNEST USE_HASH
HASH NOAPPEND USE_MERGE
HASH_AJ NOCACHE USE_NL
HASH_SJ NOCPU_COSTING USE_SEMI
HWM_BROKERED NOPARALLEL USE_TTT_FOR_GSETS
IGNORE_ON_CLAUSE NOPARALLEL_INDEX
IGNORE_WHERE_CLAUSE NOREWRITE
INDEX_ASC OR_EXPAND
INDEX_COMBINE ORDERED
INDEX_DESC ORDERED_PREDICATES
INDEX_FFS OVERFLOW_NOMOVE
INDEX_JOIN
INDEX_RRS
INDEX_SS

Undocumented Hints:
BYPASS_RECURSIVE_CHECK IGNORE_ON_CLAUSE OVERFLOW_NOMOVE
BYPASS_UJVC IGNORE_WHERE_CLAUSE PIV_GB
CACHE_CB INDEX_RRS PIV_SSF
CACHE_TEMP_TABLE INDEX_SS PQ_MAP
CIV_GB INDEX_SS_ASC PQ_NOMAP
COLLECTIONS_GET_REFS INDEX_SS_DESC REMOTE_MAPPED
CUBE_GB LIKE_EXPAND RESTORE_AS_INTERVALS
CURSOR_SHARING_EXACT LOCAL_INDEXES SAVE_AS_INTERVALS
DEREF_NO_REWRITE MV_MERGE SCN_ASCENDING
DML_UPDATE NESTED_TABLE_GET_REFS SKIP_EXT_OPTIMIZER
DOMAIN_INDEX_NO_SORT NESTED_TABLE_SET_REFS SQLLDR  
DOMAIN_INDEX_SORT NESTED_TABLE_SET_SETID SYS_DL_CURSOR
DYNAMIC_SAMPLING NO_EXPAND_GSET_TO_UNION SYS_PARALLEL_TXN
DYNAMIC_SAMPLING_EST_CDN NO_FACT SYS_RID_ORDER
EXPAND_GSET_TO_UNION NO_FILTERING TIV_GB
FORCE_SAMPLE_BLOCK NO_ORDER_ROLLUPS TIV_SSF
GBY_CONC_ROLLUP NO_PRUNE_GSETS UNNEST
GLOBAL_TABLE_HINTS NO_STATS_GSETS USE_TTT_FOR_GSETS
HWM_BROKERED NO_UNNEST
NOCPU_COSTING
 

Let's take a quick look at how hints are used to alter optimizer
execution plans: A optimizer hint is an optimizer directive placed inside
comments inside your SQL statement and used in those rare cases
where the optimizer makes an incorrect decision about the execution
plan. Because hints are inside comments, it is important to ensure that
the hint name is spelled correctly and that the hint is appropriate to
the query.

For example, the following hint is invalid because first_rows access and
parallel access are mutually exclusive. That's because parallel always
assumes a full-table scan and first_rows favors index access.

-- An invalid hint
select /*+ first_rows parallel(emp,8)*/
emp_name
from
emp
order by
ename;
 
Some Oracle professionals will place hints together to reinforce their
wishes. For example, if we have an SMP server with eight or more
CPUs, we may want to use Oracle Parallel Query to speed-up legitimate
full-table scans. When using parallel query, we seldom want to turn-on
parallelism at the table level (alter table customer parallel 35;)
because the setting of parallelism for a table influences the optimizer,
causing the optimizer to see full-table scan is inexpensive. Hence,
most Oracle professionals specify parallel query on a   query-by-query
basis, combining the full hint with the parallel hint to ensure a fast
parallel full-table scan:
 
-- A valid hint
select /*+ full parallel(emp,35)*/
emp_name
from
emp
order by
ename;
 
Now that we have the general concept of hints, let's take a look at one
of the most important hints for optimizer tuning.
The ordered hint determines the driving table for the query execution
and also specifies the order that tables are joined together. The ordered
hint requests that the tables should be joined in the order that they are
specified in the from clause, with the first table in the from clause
specifying the driving table. Using the ordered hint can save a huge
amount of parse time and speed SQL execution because you are telling
the optimizer the best order to join the tables.
For example, the following query uses the ordered hint to join the
tables in their specified order in the from clause. In this example, we
further refine the execution plan by specifying that the emp to dept
join use a hash join and the sal to bonus join use a nested loop join:
 
select
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
from
emp,
dept,
sal,
bon
where . . .
 
Of course, the ordered hint is most commonly used in data warehouse
queries or in SQL that joins more than five tables.
Next let's look at another last resort, the adjustment of Oracle
parameters. Oracle does not recommend changing any of these
parameters, except as a last resort. However, it is interesting to see
how these parameters change the way that the optimizer determines
execution plans.

Part 8 - Locating Sub-optimal SQL

While complex queries may have extremely complex execution plans,

most Oracle professionals must tune SQL with the following problems:
 Sub-optimal index access to a table — This problem occurs when the optimizer
cannot find an index or the most restrictive where clause in the SQL is not
matched with an index. When the optimizer cannot find an appropriate index to
access table rows, the optimizer will always invoke a full-table scan, reading
every row in the table. Hence, a large-table full-table scan might indicate a sub-
optimal SQL statement that can be tuned by adding an index that matches the
where clause of the query.
 
 Sub-optimal join methods — The optimizer has many join methods available
including a merge join, a nested loop join, hash join and a star join. To choose the
right join method, the optimizer must guess at the size of the intermediate result
sets from multi-way table joins. To make this guess, the optimizer has incomplete
information. Even if histograms are present, the optimizer cannot know for certain
the exact number of rows returned from a join. The most common remedy is to
use hints to change the join (use_nl, use_hash) or re-analyze the statistics on the
target tables.

Let's examine how the v$sql_plan view can help us locate SQL tuning
opportunities. When searching for tuning opportunities, we start by
interrogating the v$sql_plan view to find these large-table full-table
scans as shown in Listing 3.
Listing 3:
--*****************************************************
-- Object Access script report
--
-- (c) 2003 by Donald K. Burleson
-- This is freeware, Never to be sold
--*****************************************************

column nbr_FTS  format 999,999

column num_rows format 999,999,999
column blocks   format 999,999
column owner    format a14;
column name     format a24;
column ch       format a1;

set heading on;

set feedback on;
set echo off;
set pages 999;

ttitle 'full table scans and counts|  |The "K" indicates that the table
is in the KEEP Pool (Oracle8).'
select
   p.owner,
   p.name,
   t.num_rows,
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
from
   dba_tables   t,
   dba_segments s,
   v$sqlarea    a,
   (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   a.address = p.address
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM')
having
   sum(a.executions) > 9
group by
   p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(a.executions) desc;
Then, we extract the corresponding SQL and see if the full-table scan is
warranted or due to a missing index.
How can we locate small tables that are subject to full-table scans?
One method is to search the SQL that is currently in the library cache.
Oracle can then generate a report that lists all the full-table scans in
the database at that time. The script in Listing 3 examines the
execution plans from v$sql_plan and reports on the frequency of full-
table scans.
The report (see Listing 4) has the following columns:
 
 OWNER — The schema owner for the table
 NAME — The table name from dba_tables
 NUM_ROWS — The number of rows in the table as of the last compute statistics
from dba_tables
 C (Oracle7 only) — An Oracle7 column that displays Y if the table is cached, N if
it is not cached
 K (Oracle8 and later only) — Displays "K" if the table is assigned to the KEEP
pool
 BLOCKS — Number of blocks in the table as defined in dba_segments
 NBR_FTS — The number of full-table scans against the table (for SQL currently
in the library cache).

Listing 4: Full table scans and counts:

OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
---------- -------------------- ------------ - - -------- --------
APPLSYS FND_CONC_RELEASE_DISJS 39 N K 2 98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2 98,864
APPLSYS FND_CONC_RELEASE_STATES 1NK 2 98,864
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262 52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0NK 22 50,174
APPLSYS FND_CONC_REL_DISJ_MEMBER 39 N K 2 50,174
APPLSYS FND_FILE_TEMP 0N 22 48,611
APPLSYS FND_RUN_REQUESTS 99 N 32 48,606
INV MTL_PARAMETERS 6NK 6 21,478
APPLSYS FND_PRODUCT_GROUPS 1N 2 12,555
APPLSYS FND_CONCURRENT_QUEUES_TL 13 N K 10 12,257
AP AP_SYSTEM_PARAMETERS_ALL 1NK 6 4,521
 
This report gives information about two tuning areas:
 
 Tables & indexes for the KEEP pool—SQL speed may benefit from placing
small tables (and associated indexes) that have frequent full-table scans in the
KEEP pool. The report above shows full-table scans on both large and small
tables. Examining this report, we can quickly identify possible candidates for the
KEEP pool by selecting the tables with less than 50 blocks that have no "K"
designation. Assigning a table, partition or index to the KEEP pool is easy, and
objects can be added or removed at-will with alter system commands:
 alter table CUSTOMER storage (buffer_pool KEEP);
 
 Possible missing indexes — Large-table full-table scans can sometimes indicate
a missing index. Oracle function-based indexes are especially useful for this
purpose because any where clause can be matched with a function-based index.
For example, here is a function-based index that uses the substr and to_char BIFs:
 create index
 fbi_book
 on book
 (
 substr(book_key,1,3)
 ||
 to_char(book_retail_price)
 );
In summary, the information contained in the v$sql_plan is a great way
to perform system-wide SQL tuning.