Resequencing Oracle Table Rows for High Performance 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 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 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:

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 dictionary.

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:

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 resequencing.

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.