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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

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