Resequencing Oracle Table Rows for
Oracle Tips by Burleson Consulting
Experienced Oracle Remote 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
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 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
Remote 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.
Let’s explore how this works. Consider a SQL
query that retrieves 100 rows using an index:
last_name like 'B%';
This query will traverse the last_name_index, selecting
each row to obtain the rows. As Figure 10-20 shows, this query will
have at least 100 physical disk reads because the employee rows reside
on different data blocks.
Figure 10-98: 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
10-21, we 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 10-99: 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 Remote DBA_indexes and Remote DBA_tables views in the data
In the Remote DBA_indexes view, we 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.
Finding Queries with Index Range Scans
Using the access.sql script to explain
all of the SQL in your library cache, it is very easy to identify
tables that have a high amount of index range scans. See Chapter 11
for details on using the access.sql technique. Let’s examine
one of the reports from this script:
07 page 1
Index range scans and counts
OWNER TABLE_NAME INDEX_NAME TBL_BLOCKS
--------- ----------------- -------------------- ------------
READER ANNO_HIGHLIGHT HL_ISBN_SEQ_USER_IDX 8
READER ANNO_STICKY ST_ISBN_SEQ_USER_IDX 8
READER ANNO_DOG_EAR DE_ISBN_SEQ_ID_IDX 8
READER PAGE ISBN_VIS_FUNC_IDX
READER TOC_ENTRY ISBN_TOC_SEQ_IDX
In this report we see that all accesses are
ranked according to the number of index range scans, and we can
quickly identify the tables and indexes that we will use for
Next, let’s look at index resequencing and see
how it improves index performance.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.