Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Scheduling Oracle index rebuilding

Oracle Tips by Burleson Consulting
August 9, 2009 Updated October, 2010


Important update:  The rules for identification of candidates for index rebuilding are dependent on many factors, and Please see my updated notes on index rebuilding

The rules for identification of candidates for index coalescing/rebuilding depend on your specific index state.  See MOSC notes 989186.1, 122008.1, 989093.1 for Oracle’s suggestions on when to coalesce/rebuild indexes. Also see my updated notes on index rebuilding and note that this DBA has demonstrated the characteristics indexes that benefits from scheduled oracle index rebuilding.


While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout and other cases where it has been necessary to schedule a rebuild of problematic production indexes. 

Here is a brief investigation into this concept of rebuilding indexes on a schedule. 

Rebuilding indexes on a scheduled basis

I am a production DBA, and I was trained to do whatever possible during scheduled downtime to improve database performance.  While rebuilding indexes only improves SQL performance for high-DML systems, and even then, only under certain conditions, my end-users say that they notice a definite improvement in response time after weekend rebuilds of tables and indexes. 

Even though Oracle RAC provides 24x7 availability, there are thousands of production databases that site idle on weekends when the end-user community is not working.

But now I hear foreign people making bizarre statements that because their testing on a PC shows case single case where rebuilding does not help, making faulty over-generalizations by claiming that index rebuilding is largely a waste of time and that index rebuilding rarely improves query performance.  The truth, as it is with most Oracle answers is "it depends". 

I was taught that the DBA should do everything that they could during a maintenance window to improve performance and most production DBA's don't understand why foreigners would condemn the practice of rebuilding indexes on a schedule.

While the benefits of rebuilding fragmented objects is so self-evident that it does not warrant explanation, we needs to address these dangerous Oracle myths about rebuilding indexes during scheduled downtime.

Rebuilding indexes on a schedule is a DBA best practice

Rebuilding indexes in a schedule is a DBA best practice because the cost of rebuilding indexes is zero:

  • You can schedule a job to rebuild and index (and address errors) in just a few minutes.  Because DBA's are on a salary, the DBA cost is negligible.

  • During a weekly maintenance window, the server sits idle.  Because hardware depreciates rapidly, the cost of rebuilding indexes is zero.

Every Sunday afternoon when the end-user are enjoying a day of rest and the batch jobs and backups are all done, the SA and DBA step-in to do their weekly maintenance:

  • SA - The SA will apply patches, adjust kernel parms, optimizer the disk I/O subsystem and add hardware.

  • DBA - The DBA will apply patches, reanalyze optimizer statistics, add histograms and reorganize tables and rebuild indexes.

It's wasteful for any DBA to not use the downtime windows to perform database administration tasks, and because the costs for rebuilding indexes are zero, it does not really matter that not all indexes see a performance benefit.

There are foreign "Oracle scientists", who say that because rebuild an Oracle indexes does not always help, ergo it is a waste of time, failing to grasp the fact that (for systems with a downtime window), index rebuilding is a cost-free activity and therefore, the amount of the benefit is incidental.  Even if only 30% of the Oracle indexes benefit from a  scheduled index rebuild, then it's worth doing. 

I think that this myth that "index rebuilding is a waste of time" is dangerous for several reasons:

  • It robs the DBA of a proven effective tuning tool - Even if only 30% of the Oracle indexes benefit from a scheduled index rebuild, then it's still worth doing.

  • It puts a DBA at-risk - If a DBA manager caught a DBA trying to justify rebuild an index (a zero-cost activity) they could get fired.  Only an inept DBA would waste company time justifying a risk free, zero expense activity.

Why rebuilding indexes helps SQL performance

It's quite true that rebuilding indexes does not always help, but there are many times when rebuilding an index does help performance, especially for indexes that experience high DML activity and have multiple freelists and experience index range scans or index fast-full scans.  Because many tasks compete from many freelists to get blocks as the index grows, the physical structure of the index data blocks becomes scrambled.

In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin.  In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost.

Undoing the myths

However, there are myths that suggest that Oracle indexes rarely require rebuilding, mostly promulgated by people who have not seen the massive index fragmentation on indexes with dozens of freelists and hundreds of updates per minute.  Some of the myths are over-generalized rules of thumb, that don't account for the impact of frequent concurrent updates on Oracle index structure:

  • Myth - Indexes "rarely" require rebuilding - Neophytes claim that 99% of index rebuilding is a waste of time, a naive statements that does not consider the massive fragmentation that occurs during heavy concurrent DML loads. Some DBA's who don't understand that a single negative test cannot be generalized will create invalid single-user "test cases", which they cite as "proof" that indexes will never benefit from a regularly scheduled rebuilding!

  • Myth- There are costs and risks for for rebuild indexes - For an untrained, reckless or inept DBA, there are indeed dangers in scheduling index rebuilds.  However, in the hands of a trained DBA, index rebuilding is 100% safe and cost free during a scheduled maintenance downtime.

  • Myth - Oracle indexes never become unbalanced - By common definition, un-balanced refers to the freelist un-link behavior and the way that Oracle performs a "logical delete" on leaf nodes as rows are erased.  It's well established that indexes with massive delete activity become "sparse" and this effects runtime SQL performance.

A more accurate rule of thumb for scheduled index rebuilding would be:

"Systems with indexes that experience multiple concurrent update activity and frequent index range scans of full-scans will frequently see a performance benefit from a regularly scheduled rebuilding."

Keeping dangerous techniques out of the hands of inept DBAs

If I were advising an untrained, inept or reckless DBA, I would say they they should not rebuild indexes because the risk outweighs the benefits.  This issue of inept DBA is a real problem in the Corporate world:

  • I once was called in to a shop with severe DML performance problems. The DBA was grossly untrained and had read an article on the benefits of indexing. Because he was not too smart, all he took from the article was that "indexes are good" and started building indexes on every column of every table! Then he learned about multi-column indexes and things got real ugly.

  • A fellow in the Netherlands once accused me of nearly costing him his job! He ran one of my scripts without knowing what it was doing, not understanding that it's not a good idea to do a "alter index xxx validate structure" on his live production database! Of course, he blamed his own ineptitude on my script, that's how these people work!

When I was a college professor we had the saying: "When I get an "A" it's because I'm smart. When I get a "C" it's because you are a bad professor". 

End user testimonials on scheduled index rebuilding

Let's take a closer look at scheduling index rebuilding.  Some vendor products such as SAP recommend that key indexes be rebuilt on a scheduled basis.

Oracle experts agree.  In this discussion, Oracle ACE DBA Hemant K Chitale describes an issue on his system where his solution was rebuilding a specific problematic index on a regular basis.  However. Hemant notes that scheduling an index rebuild was a rare case for him and the root cause for the scheduled rebuild was an unresolved Oracle bug:

"I do not recommend rebuilding of ALL indexes weekly. This was a specific table (and I have identified only three such likely tables in a database of thousands of tables) that may require  . . . The behavior described in Oracle Bug 6447841 matches what I have seen."

Oracle DBA Jim Spath has shown that regular rebuilding of high DML SAP indexes should be scheduled on a regular basis:

“This index should be reorganized on a regular basis. . .

What is happening here is the index is becoming unbalanced through constant inserts and deletes.

Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops.

Each deleted row continues to occupy space in the index segment until a rebuild is done.”


 

 

  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.