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.
While it may be rare to rebuild an
Oracle index for performance reasons, there are some databases that will get
a measurable performance boost from rebuilding indexes. These
workloads have these characteristics:
-
High index fragmentation:
The SQL workload has lots of table DML causing lots of deleted
leaf blocks.
-
High index scan access
plans: The SQL workload is rich with index scans (index
fast-full scans and index range scans)
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.
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."
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
