BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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:

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 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:

Tue Nov 07                                                           page 1
                          Index range scans and counts

OWNER     TABLE_NAME        INDEX_NAME             TBL_BLOCKS    NBR_SCANS
--------- ----------------- -------------------- ------------ ------------
READER    ANNO_HIGHLIGHT    HL_ISBN_SEQ_USER_IDX            8       16,738
READER    ANNO_STICKY       ST_ISBN_SEQ_USER_IDX            8       15,900
READER    ANNO_DOG_EAR      DE_ISBN_SEQ_ID_IDX              8        1,191
READER    PAGE              ISBN_VIS_FUNC_IDX              24          813
READER    TOC_ENTRY         ISBN_TOC_SEQ_IDX                8          645

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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter