Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Determining the Block Threshold Percentage
Oracle Tips by Burleson

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 starting point.

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 your query.

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 index.

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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter