| |
 |
|
How the Optimizer
Chooses a Full-Table Scan
Oracle Tips by
Burleson
|
There are several factors that influence the
behavior of Oracle SQL with respect to invoking a full-table scan.
These factors center around the choice of optimizer_mode and
the state of dictionary statistics when using the CBO.
Rule-based Optimizer Mode
The rule-based Oracle optimizer is less
sophisticated than the cost-based optimizer and does not have any
information about the internal characteristics of the data inside the
tables specified in the query. Hence, the RBO will always use an index
if it detects that an index is available.
The Cost-Based Optimizer Modes
The cost-based optimizer modes, FIRST_ROWS and
ALL_ROWS, have information about the data inside the tables. When the
CBO estimates that the cost of a full-table scan is less than that of
an index access, it may sometimes choose to perform a full-table scan
even if indexes are present. The CBO uses the following factors to
determine whether to perform a full-table scan:
-
The db_block_size Larger
blocks will allow for the access of more rows in a single disk I/O.
-
Multiblock I/O The number of
blocks that can be read in a multiblock I/O depends on the setting
of the db_file_multiblock_read_count initialization
parameter.
-
Index statistics The CBO
evaluates the selectivity of the index and the depth of the index
tree. It then computes the number of I/O operations that are
required to service the query via the index.
-
The high-water mark for the table
The high-water mark for the table determines the amount of data
that is stored in the table. In cases where a significant number of
rows have been deleted from a table, the high-water mark remains at
the original level, and a full-table scan will often read many
“dead” blocks below the high-water mark.
While in Oracle7 and Oracle8 the CBO would often
falsely favor a full-table scan, in Oracle8i the CBO has been
improved to the point that it will make an intelligent choice. I must
also note that there are differences in the CBO optimizer modes and
the propensity to invoke a full-table scan.
ALL_ROWS Optimizer Mode
This is a cost-based optimizer mode that
ensures that the overall query time is minimized, even if it takes
longer to receive the first row. This usually involves choosing a
parallel full-table scan over a full-index scan. Because the ALL_ROWS
mode favors full-table scans, the ALL_ROWS mode is best suited for
batch-oriented queries where intermediate rows are not required for
viewing
FIRST_ROWS Optimizer Mode
The FIRST_ROWS optimizer mode is designed to
return rows as quickly as possible and will only perform a full-table
scan when no indexes are available to service access to the table.
When the CBO Chooses a False Full-Table Scan
As I noted, the CBO will sometimes make a false
choice in favor of a full-table scan, especially in Oracle7 and
Oracle8. This problem occurs when the following conditions are true:
-
High-water
mark too high When a significant number of deletes have taken
place within a table, the high-water mark may be far higher than the
actual number of populated blocks. Hence, the CBO will often wrongly
invoke a full-table scan, relying on the high-water mark.
-
Wrong optimizer_mode If the
optimizer_mode is set to ALL_ROWS or CHOOSE, the SQL optimizer
may favor full-table scans. If you want fast OLTP optimization, be
sure to set your optimizer_mode to FIRST_ROWS.
-
Poor statistics If tables have grown
significantly and the tables have not been reanalyzed to repopulate
statistics, then you may get false full-table scans because the CBO
thinks the tables are still small.
-
Skewed indexes If a
candidate index in a query has skewed values, then the CBO might
wrongly choose a full-table scan. For example, consider a query that
asks for rows where region=southern. You have an index on the
region column, but only one percent of the entries are for the
southern region. In the absence of column histograms, the CBO does
not know that southern region has high selectivity, and so it
chooses a full-table scan.
Now that you see why the CBO may make a poor
decision, let’s explore methods for ensuring that we do not have
unwanted full-table scans.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
