Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tuning full-table scan tips

Oracle Tips by Burleson Consulting

June 7, 2015


Oracle SQL tuning is one of the most important areas of Oracle optimization. This article explains how one can tune Oracle Full-table Scans.  As a review, basic SQL tuning involves the resolution of these common execution access issues:

  • Sub-optimal table join order - Identification of the best driving table is critical to SQL performance. In an n-way table join, Oracle must join the tables together in a way to minimize the intermediate result sets. Improper table join order is most often due to sub-optimal CBO statistics, especially missing histogram statistics.
  • Sub-optimal table join methods - This is usually the choice between nested loop joins and hash joins. For situations where you have a large pga_aggregate_target or hash_area_size (pre-Oracle9i), certain joins operations may run far faster with hash joins.
  • Unnecessary large-table full-table scans (LT-FTS) - The CBO may decide to perform a full-table scan when an index scan will retrieve the data with far less disk I/O. While not all large-table full-table scans are unnecessary, the LT FTS is common where Oracle's Cost-based SQL optimizer makes a sub-optimal access decision. When any of these conditions are true, the optimizer may make poor decisions about the choice between a full-table scan and an index scan, causing huge amount of unnecessary disk I/O.
  • Improper System parameters - Incorrect values for CBO initialization parameters (optimizer_index_cost_adj, optimizer_index_caching)
  • Sub-optimal CBO statistics - This is usually the result of not analyzing with dbms_stats, taking a too-small sample size, or failing to place histograms on skewed columns.
  • Missing indexes - If the WHERE clause of the query does not match an index, the CBO may perform an unnecessary FTS.
  • Incorrect object parameters - Setting a table to a too-high value for will make the CBO friendlier to full-table scans.

One important aspect of Oracle SQL tuning is the identification and inspection of full-table scans. However, Oracle distinguishes between the types of scans depending on the size of the table.

The popular Ion tool is the easiest way to analyze Oracle table behavior (average CPU cost per table access shown above)  and Ion allows you to spot hidden table-related performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use.

Large table, small table

There are two types of full-table scans, those against small tables STR-FTS and large-tables LT-FTS. Let's start by exploring the threshold between large and small tables.

Remember, by small, we refer to the number of data blocks, not the number of rows. For example, consider a table where dba_tables.avg_row_len = 32 and db_block_size = 32k. In this case, a 3,000 row table would fit comfortably into only 4 data blocks. In a case like this, it is almost fastest simply incur 4 physical gets and load the entire table into the data buffer.

In Oracle7 and Oracle8, the size of small tables was defined by the small_table_threshold parameter, but in Oracle8i, the definition of a small table was changed to be any tables whose total data blocks is greater than 2% of the number of blocks defined by db_cache_size. (You can still change it with the now-hidden parameter _small_table_threshold). Because the definition of a small table scales with the size of the SGA, a 200 block table may be considered small on one database and large in another. Small-table full-table scans (ST-FTS) are often the best solution for small tables.

FTS Blocks in the Data Buffers

It is important to know that the behavior of data locks read via FTS are different from data blocks that are accessed via an index:

If the number of incoming FTS data blocks exceeds the 'empty' RAM blocks in db_cache_size, Oracle will recycle through the empty blocks, only caching the last n blocks that fit into the data cache. This is a common technique for ensuring that rows from an LT-FTS do not flush cached blocks from the buffer for other tasks.

Once inside the RAM buffer, data blocks from an FTS are not 'touched' when the data block are re-referenced by another task. Index-accessed data blocks are very different. Blocks that enter the data buffer from an index scan are pinged to the most-recently-used (MRU) and of the data buffer each timer they are re-referenced internally, this behavior can be viewed by writing a dictionary query that displays the hot blocks (those with a touch count greater than 10) within the buffer, like this:

   obj object,
   dbarfil file#,
   dbablk block#,
   tch touches
   tch > 10
   tch desc;

Given this difference in behavior, it follows that small-table full-table scans (ST-FTS) should be cached inside the Oracle KEEP pool to prevent excessive disk I/O.

Tuning Large-table Full-table scans

The rule for evaluative and tuning LT-FTS is simple. We evaluate the query and see if index access would result in less physical reads than the existing full-table scan. This usually involves timing the execution speed for the query (with the set timing on command in SQL*Plus) and timing the query with different index access plans:

Creating a function-based index - One common technique is to match the WHERE clause of the query with a function-based index.

Using index hints - If the CBO does not have enough statistical information about an index, you can force the CBO (temporarily) to use the index by adding an index hint to the query.

Once the fastest execution plan is derived, the tuning professional will enforce the execution plan by creating schema statistics to ensure that the CBO will always use the best index access.

Tuning Small-table Full-table Scans

The problem with ST-FTS occurs when a popular table is referenced. Because the FTS data blocks are not touched (pinged to the MRU end of the buffer), ST-FTS rows age quickly from the buffer, requiring Oracle to re-read them, over and over again.

In Oracle9i and beyond hidden parameter called _adaptive_direct_read that ensures that small table scans are cached. However, it is still a good idea to identify these small tables yourself and cache them in your KEEP pool.

The keep pool is a wonderful resource for ensuring that an object always resides in the data buffer RAM, and this is one of the few ways to guarantee 10% caching.

Now that we see the benefit of caching frequently-referenced table and indexes, we see how the KEEP pool is most important to small objects that are read into the data buffers via full-table scans. For most objects in an OTLP system, aces is performed via an index, and only small tables will have full-table scans.

Also, remember that frequently-referenced data blocks accessed via an index will tend to remain in the data buffer without using the KEEP pool because they are pinged to the MRU end of the buffer every time they are referenced.

You can query the v$sql_plan table will quickly locate candidates for caching in the KEEP pool by running this query from my code depot:


set pages 999
set lines 92

spool keep_syn.lst

drop table t1;

create table t1 as
   o.owner owner,
   o.object_name object_name,
   o.subobject_name subobject_name,
   o.object_type object_type,
c   ount(distinct file# || block#) num_blocks
   dba_objects o,
   v$bh bh
   o.data_object_id = bh.objd
   o.owner not in ('SYS','SYSTEM')

See code depot for full script
   bh.status != 'free'
group by
order by
   count(distinct file# || block#) desc

   'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
   dba_segments s
   s.segment_name = t1.object_name
   s.owner = t1.owner
   s.segment_type = t1.object_type
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
   buffer_pool <> 'KEEP'
See code depot for full script
   object_type in ('TABLE','INDEX')
group by
(   sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80

spool off;


Oracle 11g changes to table scan thresholds

The behavior of direct path reads changed in Oracle 11g release 2.  Before 11gr2, full table scan access path read all the blocks within a table (or a index fast full scan) into the buffer cache unless either the "_serial_direct_read" hidden parameter is set to "true" or the table/index has default parallelism set.  In sum, in 11g release 2 and beyond, Oracle will automatically decide whether to use direct path reads (thereby bypassing he buffer cache) with full table scans.

The hidden parameter "_small_table_threshold" defines the number of blocks to consider a table as being "small".   Any table having more than 5 times the number of blocks in "_small_table_threshold" (if you leave it at default value) will automatically use direct path reads for serial full table scans (FTS).

Future Solutions

Until solid-state storage with nanosecond access speeds become affordable (It is becoming affordable quickly, and it's now only about $1k per Gig), most Oracle DBA's must work hard to reduce unnecessary disk I/O by carefully managing their data buffer pools and tuning their SQL statements.

Many vendors are offering RAM-SAN storage that provides data access thousands of times faster than disk. By 2015, this type of solid-state storage will make the data buffers obsolete, and all frequently-referenced data will reside on high-speed chips instead of disk. Until that time, the Oracle tuning professional must watch both large-table and small-table full-table scans and take the appropriate action to reduce disk I/O.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster