Reduce Processing Time by Reducing the Index

Rebuilding Oracle indexes is nothing new, but there are contrasting ideas about when this action should be taken.  Some argue that there is usually no real benefit, and others set automatic jobs to rebuild indexes regularly as a rule.

I think that each case should be evaluated on its merits.  Without going into it (the topic is well covered elsewhere) some indexes naturally tend towards 75% capacity and others >90%.  Index capacities well under 75% are usually due to deletions.  The only general rule I’d suggest is that when data is deleted, especially by a few large operations, then the related indexes might be worth checking to see if a reorganisation is warranted.

Compact (dense) indexes mean less disk space, less memory for caching the same information, less I/O and less processing (CPU time).

Rebuilding, coalescing (Enterprise Edition) or shrinking space (10g+) can be big and easy wins.  There are many cases I could describe, but here is the most extreme example.

Purging Performance Problem

An organisation approached me after they been trying to purge years worth of data from an Oracle 9i database, but found that the performance was atrocious, and they were after that elusive go fast switch.

The application had a purging function built in, (rare but so beneficial!), so the code couldn’t be adjusted.  There were no application design changes possible, eg partition by time and efficiently drop partitions instead of deleting.

The application purged data by asking for a period to retain and one to purge, then looping – deleting more recent data in the first iteration followed by incrementally older data in the next iterations.  Here is a simplified example of what I believe it was doing from the description given to me:

delete where data_date between :retention_date-1 and :retention_date;
commit;
delete where data_date between :retention_date-2 and :retention_date;
commit;
delete where data_date between :retention_date-3 and :retention_date;
commit;
....

This was probably designed to reduce undo space requirements.

The data_date key was indexed, which avoided tablescans.  Oracle would handle the first deletion by entering the index at retention_date – N and then (range) scanning the index until it found the retention date, deleting as it went.

The problem is obvious to those who understand how Oracle indexes work.  The deleted index entries result in more and more empty leaf blocks between the data yet to be purged and the retention date.  These empty leaf blocks are not taken out of the index structure until they are reused elsewhere (by an insert or update).  The index range scan had to access more and more of these empty leaf blocks as the purging process continued, requiring more processing time for each subsequent delete.

The solution I suggested was to punctuate the purge cycles with manual index coalesce commands to remove the empty leaf blocks.  The first coalesce of the relevant index took several minutes, but took less time after that. This is the feedback we received when the index coalesce was added to the purging process:

“Wow……..what previously took 1 hour now takes about 25s.  Yes, I can now purge a day off the table in about 25s.   This is great.  A whole week in 1 minute. and a month in 10 minutes.”

In this case, it wasn’t even necessary to access the database to find the solution.  When one does have access to the database, evidence that the index should be rebuilt / shrunk / coalesced can come from:

• a high logical read count for a SQL statement, and
• a high logical read count for an index segment, and
• a low density shown by the results of analyze index .. validate structure, or
  
• a low density shown by the results of the segment advisor (10g), or
• a low estimated density from the key length x number of keys versus actual index size, or
• a tree dump of the index showing many empty (or mostly empty) leaf blocks in the structure

Big Wins are the Easiest

Isn’t that always the way with Oracle tuning?  The really dramatic improvements are often the easiest to find and implement.  Tuning tasks that are highly restrictive, complex and only offer a small scope for performance gains may be the most challenging but the least appreciated.  Finding the “low hanging fruit” (beneficial, cheap and low risk) is justifiably what customers want first.

Here are two contrasting examples from my experience:

An Easy Win

We had just taken over support of a customer’s Oracle database from a competitor.  It was a terabyte database on Oracle 8i and Windows (32-bit).  When doing the initial audit, I was shocked to learn that their monthly billing process ran for two weeks solid!  This meant no outages, slow performance for OLTP users, and if something went wrong, they’d only just have time to run it again before the next billing process needed to start.

It was obvious to me that before we could start making other improvements highlighted in our audit, we’d need to get the billing process down to a reasonable length of time.  Sure, the platform was hardly appropriate for the amount of data and processing, but two weeks to process one batch job each month was ridiculous.

It took a few minutes to spot two inefficient SQL statements and to recommend a couple of indexes that ended up reducing the monthly billing run to less than a day.  The only problem was that the companies being billed were surprised to get their bills earlier. 

This kind of miracle tuning is welcomed by the customer and looks great on a CV, but it was embarrassingly easy.

A Hard Win

In complete contrast to the previous example, this one took a lot of work for small but necessary gains.  A different customer had a legacy monthly billing process that was very poorly designed, and was taking longer each month as the data grew.  The core engine could not be altered; only tweaks were possible.  The application was due to be replaced in the new year, but until then, we were tasked with keeping it running within a window of a weekend.  (Users could not be in the system during this monthly billing process, so it had to complete within a weekend).

This application had already been through iterations of tuning, so there were no easy and quick wins left.  It took a lot of analysis and work to save 10% here and 5% there, just to hold a steady run time each month.  We tuned multiple layers: the hardware, storage, OS, Oracle parameters, data density, SQL, indexes and finally the PL/SQL packages.

Although the results were appreciated by the customer, and provided me with job satisfaction, stopping an application from slowing down doesn’t grab attention in the same way as the easy win described above.

How All this Relates to the HP Database Services Blog

Big and easy wins are often not so technically interesting or challenging, and are usually well covered in books and forums and other blogs.  Therefore, it is tempting to just write about more difficult or rare performance problems.  However, “low hanging fruit” are commonly sought after, so I’ll try to keep this in mind for future posts.

Here’s a start in that theme.