 |
|
Resequencing Table Rows
for Reducing I/O
Oracle Tips by
Burleson
|
Experienced Oracle DBAs know that I/O is the
single greatest component of response time and regularly work to
reduce I/O. Disk I/O is expensive because when Oracle retrieves a
block from a data file on disk, the reading process must wait for the
physical I/O operation to complete. Disk operations are 14,000 times
slower than a row’s access in the data buffers. Consequently, anything
you can do to minimize I/O—or reduce bottlenecks caused by contention
for files on disk—can greatly improve the performance of any Oracle
database.
If response times are lagging in your
high-transaction system, reducing disk I/O is the best way to bring
about quick improvement. And when you access tables in a transaction
system exclusively through range scans in primary-key indexes,
reorganizing the tables with the CTAS (create table as select) method
should be one of the first strategies you use to reduce I/O. By
physically sequencing the rows in the same order as the primary-key
index, this method can considerably speed up data retrieval.
Like disk load balancing, row resequencing is
easy, inexpensive, and relatively quick. With both techniques in your
DBA bag of tricks, you'll be well equipped to shorten response
times—often dramatically—in high-I/O systems.
In high-volume online transaction processing (OLTP)
environments in which data is accessed via a primary index,
resequencing table rows so that contiguous blocks follow the same
order as their primary index can actually reduce physical I/O and
improve response time during index-driven table queries. This
technique is useful only when the application selects multiple rows,
when using index range scans, or if the application issues multiple
requests for consecutive keys. Databases with random primary-key
unique accesses won't benefit from row resequencing.
Most Oracle professionals will run the
access.sql script from Chapter 5 to identify tables for possible
row resequencing. This is done by examining the index range scan
report to locate those tables whose table access is predominantly via
index range scans. Remember, resequencing table rows will not
adversely affect the performance of full-table scans or index unique
scans. To get the most benefit, the candidate table should be heavily
accessed using a single index key, and the index range scan report
will clearly indicate these tables.
Let’s explore how this works. Consider a SQL
query that retrieves 100 rows using an index:
select
salary
from
employee
where
last_name like ‘B%’;
This query will traverse the last_name_index,
selecting each row to obtain the rows. As Figure 6-4 shows, this query
will have at least 100 physical disk reads because the employee rows
reside on different data blocks.
Figure 4: An index
query on unsequenced rows
Now let’s examine the same query where the rows
are resequenced into the same order as the last_name_index. In
Figure 6-5, you see that the query can read all 100 employees with
only three disk I/Os (one for the index, and two for the data blocks),
resulting in a saving of over 97 block reads.
Figure 5: An index
query with sequenced rows
The degree to which resequencing improves
performance depends on how far out of sequence the rows are when you
begin and how many rows you will be accessing in sequence. You can
find out how well a table's rows match the index's sequence key by
looking at the dba_indexes and dba_tables views in the
data dictionary.
In the dba_indexes view, take a look at
the clustering_factor column. If the clustering factor—an
integer—roughly matches the number of blocks in the table, your table
is in sequence with the index order. However, if the clustering factor
is close to the number of rows in the table, it indicates that the
rows in the table are out of sequence with the index.
The benefits of row resequencing cannot be
underestimated. In large active tables with a large number of index
scans, row resequencing can triple the performance of queries.
There are two ways to re-sequence Oracle table
rows into a primary key order:
Both of these methods will result in improved
performance by reducing disk I/O, but they are implemented in very
different ways. Let’s take a look at each method.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
