Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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:

  • Index cluster

  • Manual resequencing of rows

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter