| |
 |
|
Determining the
Threshold for a Full-Table Scan
Oracle Tips by
Burleson
|
When making the decision to change a full-table
scan to an index range scan, the primary concern is the speed of the
query. In some cases, the full-table scan may have more physical disk
I/Os, but the full-table scan will be faster because of a high degree
of parallelism.
In other cases, you need to consider the number
of rows retrieved as a function of the clustering of the rows in the
table. For example, if your table is clustered or you have manually
resequenced the rows into primary-key order, a great many adjacent
rows can be read in a single I/O and an index range scan will be
faster than a full-table scan for up to 40 percent of the table rows.
On the other hand, if your table is totally unordered, a request for
10 percent of the table rows may cause the majority of the table data
blocks to be read. Of course, you also need to consider the degree of
parallelism on the table and the setting for
db_file_multi_block_read_count init.ora parameter. Hence,
the general guideline for replacing an index range scan is:
-
For row-sequenced tables
Queries that retrieve less than 40 percent of the table rows should
use an index range scan. Conversely, queries that read more than 40
percent of the rows should use a full-table scan.
-
For unordered tables Queries
that retrieve less than 7 percent of the table should use an index
range scan. Conversely, queries that read more than 7 percent of the
table rows will probably be faster will a full-table scan.
Your mileage may vary, so it is always a good
idea to test the execution speed in SQL*Plus by issuing the set
timing on command.
Finding Full-Table Scans
The easiest way to find full-table scans in
your database is to use the access.sql script from
www.oraclepress.com. This script grabs all of the SQL in the library
cache and stores it in a table called sqltemp. From this table,
all of the SQL is explained into a single plan table. This plan table
is then queried to produce the report that follows.
Here we see a list of all the tables that
performed full-table scans, and the number of times that a full-table
scan was performed. Also note the C and K columns. The C column
indicates if an Oracle7 table is cached, and the K column indicates
whether the Oracle8 table is assigned to the KEEP pool. As you will
recall, small tables with full-table scans should be placed in the
KEEP pool.
Mon Jan
29 page 1
full table scans and counts
Note that "C" indicates in the table is cached.
OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
-------------- -------------------- ------------ - - -------- --------
SYS DUAL N 2 97,237
SYSTEM SQLPLUS_PRODUCT_PRO N K 2 16,178
DONALD PAGE 3,450,209 N 932,120 9,999
DONALD RWU_PAGE 434 N 8 7,355
DONALD PAGE_IMAGE 18,067 N 1,104 5,368
DONALD SUBSCRIPTION 476 N K 192 2,087
DONALD PRINT_PAGE_RANGE 10 N K 32 874
ARSD JANET_BOOKS 20 N 8 64
PERFSTAT STATS$TAB_STATS N 65 10
In the preceding report, you see several huge
tables that are performing full-table scans. If tables have less than
200 blocks and are doing legitimate full-table scans, we will want to
place them in the KEEP pool. The larger table full-table scans should
also be investigated, and the legitimate large-table full-table scans
should be parallelized with the alter table parallel degree nn
command.
An Oracle database invokes a large-table
full-table scan when it cannot service a query through indexes. If you
can identify large tables that experience excessive full-table scans,
you can take appropriate action to add indexes. This is especially
important when you migrate from Oracle7 to Oracle8, because Oracle8
offers indexes that have built-in functions. Another cause of a
full-table scan is when the cost-based optimizer decides that a
full-table scan will be faster than an index range scan. This occurs
most commonly with small tables, which are ideal for caching in
Oracle7 or placing in the KEEP pool in Oracle8. This full-table scan
report is critical for two types of SQL tuning:
-
For a small-table full-table scan, cache the
table by using the alter table xxx cache command, (where
xxx = table name), which will put the table rows at the most
recently used end of the data buffer, thereby reducing disk I/O for
the table. (Note that in Oracle8, you should place cached tables in
the KEEP pool.)
-
For a large-table full-table scan, you can
investigate the SQL statements to see if the use of indexes would
eliminate the full-table scan. Again, the original source for all
the SQL statements is in the SQLTEMP table. I will talk about the
process of finding and explaining the individual SQL statements in
the next section.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
