Tuning Oracle Full-table Scans

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 dba_tables.degree 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.

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:

  SELECT
     obj      object,
     dbarfil  file#,
     dbablk   block#,
     tch      touches
  FROM
     x$bh
  WHERE
     tch > 10
  ORDER BY
     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.

n 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:

buf_keep_pool.sql:

set pages 999
set lines 92

spool keep_syn.lst

drop table t1;

create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;

select
   'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   s.owner = t1.owner
and
   s.segment_type = t1.object_type
and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
   buffer_pool <> 'KEEP'
and
   object_type in ('TABLE','INDEX') 
group by
   s.segment_type,
   t1.owner,
   s.segment_name
having
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;

spool off;

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 2007, 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.