While not all large-table full-table scans are problematic, a large-table
full-table scan is a common symptom of a SQL execution problem.
Large-table full-table scans in an explain plan (TABLE ACCESS FULL) should
always be examined to verify that it is not due to a database problem, such
as a missing index.
Also see Avoid
full table scan tips
Unnecessary large-table full-table scans "full-table scans" (FTS) are an
important symptom of sub-optimal SQL and cause unnecessary I/O that can drag
down an entire database.
The first step in validating a full-table scan is to evaluate the SQL
based on the number of rows returned by the query and your own guess about
the number of trips to the data blocks that would be needed to fetch the
rows.
Oracle says that if the query returns less than 40 percent of
the table rows in an ordered table or seven percent of the rows in an
unordered table, the query can be tuned to use an index in lieu of the
full-table scan, but in reality there is no fixed number because it depends
on many factors like the db_block_size and
db_file_multiblock_read_count.
This decision is partly based
on the index key value described by clustering_factor in the
dba_indexes
view. However, it is not a simple process.
The choice of a full
table scan vs. index access as the "best" access plan for a SQL statement
depends on many factors. The most common cause of unnecessary
full-table scans is a optimizer_mode that favors full-table scans (like
all_rows) or a missing index, especially a function-based indexes.
These factors are listed in their order of importance:
-
Sub-optimal
optimizer_mode: The all_rows mode minimizes computing resources
and favors full-table scans, while the first_rows_n mode favors
index access.
-
Missing indexes:
Missing indexes, (especially
missing function-based indexes) will cause in unnecessary large-table
full-table scans.
-
Bad CBO statistics:
Bad/stale optimizer
statistics (dbms_stats) will cause Oracle to mis-judge the
execution plan for a SQL query.
-
Missing CPU and disk statistics:
Missing
system statistics (dbms_stats.gather_system_stats) will prevent
the optimizer from making a smart decision about invoking multi-block
I/O.
- Histograms: For skewed data columns, missing
column histograms will influence the choice of index vs. full-table scan
access.
-
Index clustering:
The physical row
order on the data blocks is important to know when choosing an index,
and the clustering of the table rows to the desired index (as displayed
in dba_indexes clustering factor).
-
Sub-optimal setting for
db_file_multiblock_read_count:
The optimization of
db_file_multiblock_read_count has been automated in 10g release
2 and beyond to prevent errors.
-
Sub-optimal setting for
optimizer_index_cost_adj: This is a throttle that
makes index access more attractive to the optimizer, and lowering the
value of optimizer_index_cost_adj will cause the SQL optimizer
to favor indexes. WARNING - DO NOT
adjust this parameter until you have examined other "root cause" issues
like bad metadata statistics.
-
Sub-optimal setting for
optimizer_index_caching: The
optimizer_index_caching parameter is set by the DBA to help the
optimizer know, on average, how much of an index resides inside the data
buffer. The optimizer_index_caching parameter is a
percentage parameter with valid values between zero and 100.
-
Parallel query:
Turning on Oracle parallel query (OPQ) will tell the optimizer
that full-scans are less expensive, and it may change the execution
plans for hundreds of SQL statements. If you are on an SMP server
with dozens of CPU's a full-table scan can be parallelized for faster
execution. However, you should never read a data block unless it
is needed to get rows for your SQL result set. Remember, parallel
query only effects full-table scans, and it has no effect when
performing index access.
-
Block Size: The number of rows that resides on each
data block influences the choice between a full-table scan of an index
scan. This is especially true when you are using RAID with a
smaller stripe size, and reading a table with a 2k block size would take
significantly more I/O and reading a table with a 32k block size.
The most common tuning tool for addressing unnecessary full-table scans
is the addition of indexes, especially function-based indexes.
The
decision about removing a full-table scan should be based on a careful
examination of the amount of logical I/O (consistent gets) of the
index scan versus the costs of the full-table scan.
This decision should be made while factoring in the multi-block reads and
possible parallel full-table scan execution. In some cases, an unnecessary
full-table scan can be forced to use an index. This is done by adding
an index hint to the SQL statement.
and include the URL for the page.
