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.
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:
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.
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:
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,
c ount(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')
See
code depot for full script
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'
See code depot for full script
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;
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!
|
|
|
|
| |
|
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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
