Breaking two myths about rebuilding indexes in the Oracle Database

Myth #1: Rebuilding indexes regularly is a good thing to do.

Myth #2: Rebuilding indexes regularly is not a good thing to do.

As with most things “IT”, it depends.

You’ll read over and over that B-tree indexes will perform fine even as rows are deleted from them. I agree that in theory this makes sense, and the vast majority of the time this holds true. In reality though, I have had to deal with severe performance issues where rebuilding indexes fixed things. In these cases no changes had been made to the schemas or database, and they always just “popped up suddenly and out of nowhere.” I would dig down into the queries that were severely degraded and finding nothing obvious, I would try rebuilding the indexes (especially if height was over 4 or deleted leaf nodes was high). In many cases this would result in a sudden return to normal. In all cases, that I recall, it was an OLTP table with heavy inserts and deletes.  I’ve seen this happen from 7.x all the way to 11.x.

If you find that rebuilding saves you significant performance over and over then by all means automate it until you can partition or find another solution to the design. Don’t waste time rebuilding every index you have, just those that need it. How do you know which need it? The ones where it makes a difference – don’t do it purely because of the height or deleted leaf rows.

The data below is just a small snapshot, but it shows log information from the index rebuild log and the cleanup script log. You can see that the performance of the cleanup process is taking a full hour repeatedly and completing anywhere from 2,000 to 100,000 rows. Once the rebuilds start just after 0700 the performance begins to improve to 250,000 and over 350,000 when complete. After that it is regularly completing 50,000 to 70,000 in 5-10 minutes. I also had AWR reports showing that after the rebuilds several key queries on the table were performing in much shorter amounts of time (unfortunately I can’t find that data to share). If I monitor I will see this performance gradually degrade until the next rebuild cycle.

Cleanup Log

Status Start End Notes
Success 8/7/2012 19:10 8/7/2012 19:20 A total of 59688 rows were removed.
Success 8/7/2012 17:10 8/7/2012 17:23 A total of 74184 rows were removed.
Success 8/7/2012 15:10 8/7/2012 15:21 A total of 62464 rows were removed.
Success 8/7/2012 13:10 8/7/2012 13:15 A total of 45503 rows were removed.
Success 8/7/2012 11:10 8/7/2012 11:45 A total of 365686 rows were removed.
Warn 8/7/2012 9:10 8/7/2012 10:10 A total of 360000 rows were removed before the Max Run Time was exceeded.
Warn 8/7/2012 7:10 8/7/2012 8:10 A total of 250500 rows were removed before the Max Run Time was exceeded.
Warn 8/7/2012 5:10 8/7/2012 6:10 A total of 137000 rows were removed before the Max Run Time was exceeded.
Warn 8/7/2012 3:10 8/7/2012 4:10 A total of 70500 rows were removed before the Max Run Time was exceeded.
Warn 8/7/2012 1:10 8/7/2012 2:19 A total of 2000 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 23:10 8/7/2012 0:22 A total of 3500 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 21:10 8/6/2012 22:10 A total of 53500 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 19:10 8/6/2012 20:10 A total of 84500 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 17:10 8/6/2012 18:10 A total of 65500 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 15:10 8/6/2012 16:10 A total of 67000 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 13:10 8/6/2012 14:10 A total of 80500 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 11:10 8/6/2012 12:10 A total of 89000 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 9:10 8/6/2012 10:10 A total of 102000 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 7:10 8/6/2012 8:10 A total of 87000 rows were removed before the Max Run Time was exceeded.
Warn 8/6/2012 5:10 8/6/2012 6:10 A total of 31500 rows were removed before the Max Run Time was exceeded.

Index REBUILD Log

---> 2012/08/07 07:35:42 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_01.NULL rebuilt successfully
---> 2012/08/07 07:59:14 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_02.NULL rebuilt successfully
---> 2012/08/07 08:23:45 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_03.NULL rebuilt successfully
---> 2012/08/07 08:48:05 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_04.NULL rebuilt successfully
---> 2012/08/07 09:10:45 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_05.NULL rebuilt successfully
---> 2012/08/07 09:33:30 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_06.NULL rebuilt successfully
---> 2012/08/07 09:55:55 RDVRGW_CHIC.IDX_SCHEDULED_RECORDING_08.NULL rebuilt successfully
---> 2012/08/07 10:20:03 RDVRGW_CHIC.PK_SCHEDULED_RECORDING.NULL rebuilt successfully
---> 2012/08/07 10:41:35 RDVRGW_CHIC.SCHEDULED_RECORDING_IX7.NULL rebuilt successfully
---> 2012/08/07 11:03:59 RDVRGW_CHIC.SCHED_REC_ENDTM_IX.NULL rebuilt successfully

Checking the percentage of delete leaf rows

To analyze a table to determine the height and deleted leaf rows you can do the following. Be careful, this analyze command will lock the table so do not ever use this when your system is running. In fact, that sort of makes a point – things really should be bad for you to consider this.

SQL> analyze index settop_poll_pk validate structure;

SQL> select name, height, del_lf_rows, lf_rows, round((del_lf_rows/lf_rows)*100,2) pct_del_lf_rows from index_stats;
NAME                               HEIGHT DEL_LF_ROWS    LF_ROWS PCT_DEL_LF_ROWS
------------------------------ ---------- ----------- ---------- ---------------
SETTOP_POLL_PK                          3      163378    1008614            16.2

3 responses to “Breaking two myths about rebuilding indexes in the Oracle Database

  1. Pingback: Breaking two myths about rebuilding indexes in the Oracle Database | Jed's

  2. Encountered another bad index situation today. System just wasn’t processing. I determined it was only in the portion that does heavy inserts/deletes to a couple key tables. Nothing else made sense. Rebuilt the index on both and boom, processing like a champ again.

  3. Pingback: Find tables with high inserts/updates and rebuilding indexes | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s