Determining the Block
Oracle Tips by
Remember, when considering the total number of
blocks accessed, you must also consider the index block accesses, and
we generally add a 20 percent overhead for block access. The generally
recognized threshold for block access is about 40 percent, meaning
that any query that accesses over 40 percent of the table blocks will
be faster with a nonparallel full-table scan.
We also have to account for Oracle parallel
query, which speeds the full-table scan by 60 percent for each
parallel query slave process. This 60 percent gain is based on the
number of CPUs on your database server and the distribution of your
data blocks across disk devices, but 60 percent provides a good
For example, if a full-table scan has a
parallel degree of 16, the full-table scan will run about 60 percent
of 16, or about 10 times faster than a nonparallel full-table scan.
When the starting block access threshold for a
table is 40 percent, we must reduce this percentage based on our
optimal degree of parallelism for the table. For details on computing
the optimal degree of parallelism for a table, see Chapter 10. The
goal is to compare the estimated number of blocks retrieved by the
index range scan with our threshold.
Concluding the Estimate
In our example, we retrieved about 60 blocks of
our 1000-block table. This is about 17 percent of the table blocks,
and it is safe to assume that the index range scan would be faster
than a full-table scan, unless we have a very large database server
with dozens of CPUs, and we can get a high degree of full-table scan
performance improvements with Oracle parallel query.
As you can see from the preceding section,
empirically estimating the benefit of an index range scan versus a
full-table scan depends on many factors. Hence, most SQL tuning
professionals dispense with the equations except to get a general idea
of the threshold.
In the real world, the only way to verify that
an index outperforms a full-table scan is to set timing on and test
the execution speed in SQL*Plus. There is no substitute for timing
Time the Query with the New Index
When all is said and done, the only real
measure is the execution speed of the query. To make the test
realistic, many people will restart the Oracle test database
immediately preceding the query to ensure that the data buffer caching
does not affect the query response time.
Predict the Benefit for the Whole Database
One additional step that is commonly performed
is predicting the overall benefit to the database. To do this, you
begin by going to the historical STATSPACK tables and re-explaining
all of the SQL from the stats$sql_summary table. Once in your
plan table, you can run the access_report.sql script to get the
table access report for each snapshot period.
One perplexing problem is that it is not easy
to predict how often an index is used. By modifying the access.sql
script from the Web site for this book, you can explain all of the SQL
for a specified STATSPACK snapshot and see how often your index would
be been used at that time. This allows your DBA to weigh the costs of
the index storage with the speed improvements resulting from the new
From the report, you should see a reduction in
full-table scans against the target table. In addition, you will also
see your new index being used in the index range scan report.
Next, let explore methods for removing
time-consuming SQL disk sorts by adding indexes.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.