DBA
Rebuilding Indexes – why, when, how? (Sept 2004)
Jonathan Lewis, JL Computer Consultancy
Optimal Thinking.
There are many areas of Oracle that are difficult to understand and require careful and subtle testing. Index maintenance is not one of those areas. (Deciding an optimal set of indexes is another matter – but that’s not the purpose of this presentation).
It is easy to create experimental data sets, easy to build indexes, easy to see what the structure of an index looks like. You don’t have to catch fleeting activity, or worry about timing critical concurrency components to make some special event occur. Indexes just sit there, waiting to be investigated at leisure.
The published purpose of this presentation is to address the issue: "How much effort should you spend trying to 'optimize' the quality of your indexes?", but the underlying approach is really just trying to understand indexes (b-tree indexes in particular) a little better. This white paper supports, and is supported by, the graphic images in the presentation.
Optimal Thinking.
The first (and only) rule of optimization is: "Avoid unnecessary effort". But you have to operate this rule at many levels. In the case of rebuilding indexes, for example, you have four considerations:
· If an index needs constant care and attention, is this a clue that you really need to be fixing a design error.
· If an index is required, it should not be allowed to degenerate so far that the optimizer should stop using it.
· You should not waste resources rebuilding indexes when the performance gain is not worth the effort or risk
· You should not spend excessive amounts of time trying to work out exactly when each index needs to be rebuilt.
Classifying Indexes
Before discussing the possible reasons or methods for doing house-keeping operations on indexes, it is worth noting that there are many variants of indexes that may need to be assessed and handled in different ways.
Consider the fact that you have simple B-tree indexes, simple bitmap indexes, index clusters, index-organized tables, B-tree indexes on IOTs, bitmap indexes on IOTs, and bitmap join indexes. Then, to add to the options, you can have partitioned tables with global, globally partitioned, or local indexes and in Oracle 10g you can even have partitioned indexes on non-partitioned tables. (Ed 7th June: Paul Hancock of HBOS emailed me to point out that you could have partitioned indexes on non-partitioned tables in earlier versions of Oracle – the specific new feature for 10g was that the indexes could be hash-partitioned when the earlier versions of Oracle only allowed for range-partitioning).
The options for "rebuilding" indexes include deciding whether you want to do online or offline rebuilds, whether you want to mark indexes as unusable first or whether there is scope for doing a drop and re-create; and perhaps a coalesce would be better than a rebuild anyway. And, alas, the type of index does restrict the options available – try doing an online rebuild of one sub-partition of a local bitmap index in 9.2 and you'll find it's not possible. Finally, there is the issue of what to do about index statistics when you perform some form of major surgery on an index.
This article is going to focus on simple B-tree indexes – although some of the points it raises will be equally applicable to the individual segments of a partitioned B-tree indexes, and I may throw in a couple of comments about bitmap indexes.
The benefits of indexes
We create indexes because they can reduce the amount of work we have to do to find data. Instead of examining millions of rows from a table to identify the few we need, we might start by examining a "much smaller" data set. This "much smaller" data set may point to exactly the few rows that we are really interested in, or it might perhaps identify a batch of rows that are good candidates for our requirements but still need further (filtering) tests to allow us to identify the exact rows we want. Either way, using an indexing structure should make the task of locating data much less costly.
The costs of indexes
Every time we change the data in a table, we may have to modify some related index information. The more indexes we create on a table, the more work we may have to do as we modify the table. Clearly we need to be careful when we define the set of indexes we want on a table. If we are too extravagant with creating indexes, we may pay a huge penalty as we load and modify the data – and it doesn't matter how quickly our queries can run if we can’t get the data into the system in time.
(Of course, the bitmap index is an extreme case of resource consumption – single row DML on tables with bitmap indexes can result in massive amounts of undo and redo being generated, combined with a huge space wastage inside the index that results in lots of unnecessary I/O as the index is both written or read. This situation has been improved somewhat in 10g).
But it's not simply the increased cost of the DML that worries us when we have indexes on our tables. The very act of changing the data could make our indexes less effective, and this is an issue that needs a little consideration. Fortunately, many indexes have a 'natural' steady state, so we often need only look out for special cases.
What's the problem?
If the way the data is inserted, updated and deleted over a period of time is consistent, then the indexes on that table will usually stabilize at an efficiency of around 70% to 75%. By this, I mean that each leaf block in the index will end up with about 70% – 75% of its space used. Some indexes will do better, running at close to 100% packed; some will do significantly worse, and a steady state of 50% space usage is not something I would consider particularly bizarre.
Some indexes, of course, degenerate catastrophically and you may occasionally find indexes running with most leaf blocks showing 90% or more empty space. In the worst cases, you may find indexes that appear to contain many completely empty blocks, lots of blocks with just one or two entries, and a few blocks packed full.
The important thing about space wastage, though, is that it isn't necessarily a problem. Until version 9i it was quite easy to get a good estimate of how much waste space there was in an index and in version 9i it became possible to get a very accurate idea of how that waste space was distributed through the index. Obviously, an index with a large amount of waste space could have a significant performance impact – but if you didn't have the resources to re-pack every suspect index, how do you identify the indexes where the space wastage really makes a difference.
A slightly more surprising feature of space wastage is that it's sometimes a good thing –packing an index too well could actually cause a performance problem. So not only could you waste resources re-packing indexes that didn't need to be repacked, you could even cause problems by re-packing indexes that positively should not be re-packed – and there is no 'numbers-only' way of deciding which indexes are which. (By "numbers-only", I mean a purely arithmetical way of analyzing the state of the index in isolation from the way it is being used).
Check space usage (1)
Of course, the easy way of checking the (average) efficiency of an index is to validate the index, then check the index_stats view. For example, from an Oracle 9i schema:
validate index t1_pk;
-- analyze index t1_pk validate structure;
execute print_table('select * from index_stats')
HEIGHT : 2
BLOCKS : 128
NAME : T1_PK
PARTITION_NAME :
LF_ROWS : 21666
LF_BLKS : 41
LF_ROWS_LEN : 280935
LF_BLK_LEN : 7996
BR_ROWS : 40
BR_BLKS : 1
BR_ROWS_LEN : 789
BR_BLK_LEN : 8032
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 21666
MOST_REPEATED_KEY : 1
BTREE_SPACE : 335868
USED_SPACE : 294652
PCT_USED : 88
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 3
PRE_ROWS : 762
PRE_ROWS_LEN : 12928
OPT_CMPR_COUNT : 2
OPT_CMPR_PCTSAVE : 0
The problem with validate structure is that it tries to lock the table, and fails with Oracle error 54 (resource busy and acquire with NOWAIT specified) if anyone is currently updating the table. Worse, if the validation takes a long time, anyone who then tries to update the table is locked out. The analyze version of the validate command can run online from 9i onwards but unfortunately the online version doesn’t populate the index_stats view – even in 10g. The validate command really only comes into its own if you regularly clone the production system into a backup or UAT system where interruption of service and poor performance are deemed to be acceptable.
I have used Tom Kyte's invaluable print_table procedure to list the contents of the single row that appears in the view index_stats after a validate. The most significant numbers to check are the used_space and btree_space. You can check the meanings of these column by reviewing the underlying x$ object, but the btree_space is the total space available in blocks that are currently in the index structure, the used_space is the actual space currently in use, and is the sum of the lf_rows_len, br_rows_len and pre_rows_len (leaf rows, branch rows, and prefix rows – for compressed indexes – respectively).
There are two other figures to note when using this view to assess the wastage in this index. The first is the 'percentage use' figure, pct_used: this is just used_space/btree_space and, as with all ratios, if you don't keep an eye on the scale of the underlying figures you may treat it with more significance than it merits. The other is the del_lf_rows_len which is the amount of space that would be available in existing leaf blocks for new insertions if the leaf blocks were cleaned. If this value is a large fraction of the leaf_rows_len, it is a clue that you need to investigate what is unusual about the way the table (or this specific index) is used; it is possible that this index should be regularly coalesced, it could be that you need to play clever games with function-based indexes.
Another number that gets reported from 9i onwards is the 'optimum compression count' opt_cmpr_count with its companion opt_cmpr_pctsave. These tell you the number of columns to compress in the index to get maximum space saving in the leaf blocks, and the percentage reduction in leaf block space used if you applied that compression count. Although it is nice to know these figures, bear in mind that index compression can increase CPU overhead, and the more index entries you have per block the more likely you are to get contention as more processes compete for those blocks.
In passing, don't forget that when you validate an index you also populate a view called index_histogram that gives you some idea of the unevenness in the numbers of repetitions of different keys in the index. (There is a hint about this in the index_stats view from the most_reported_key column). From an Oracle 10g schema:
select * from index_histogram;
REPEAT_COUNT KEYS_WITH_REPEAT_COUNT
------------ ----------------------
0 14097
8 5619
16 4004
24 3398
32 3497
40 3871
48 4140
56 2604
64 838
72 121
80 8
88 1
96 0
104 0
112 0
120 1
In this case, we see that there are 14,097 key values that appear less than 8 times each; 5,619 key values that appear between 8 and 15 time each, and so on until we get to one key value that appears somewhere between 120 and 127 times. Information like this can help us to decide whether there is some odd business-related feature of the index that requires us to make it a candidate for special treatment.
Check space usage (2)
Given the problem of locking that you have with the validate index option, is there an alternative way to get some warning figures about space usage? Yes, but only if you are running version 6 (yes, six) or later. Of course the results you get are going to be approximate, and the options for complicating (or refining) the estimate are numerous, but the method is simple. If you have gathered statistics on the table with the cascade option, then you know:
· The number of entries in the index (user_indexes.num_rows)
· The number of rows in the table (user_tables.num_rows)
· The average column length of each of the columns in the index (user_tab_col_statistics.avg_col_len)
· The number of null occurences for each column (user_tab_col_statistics.num_nulls)
For each index entry, you need space for:
· The entry overhead – two bytes for the offset, plus one byte for the lock byte and one byte for the flags.
· The rowid content – 6 bytes for a unique index, 7 for non-unique, or 10/11 bytes for a globally partitioned index.
For the column content, you don't need to consider index entries individually, you can just add up the total number of column-level entries that must be in the index. This will introduce a little inaccuracy where (for example) an index entry starts with a null column – but generally, the precision will be reasonable. For each column, you have the number of nulls, and the average column length; combine this with the number of rows in the table, and the total space in the index for each column is:
· (user_tables.num_rows – user_tab_col_statistics.num_nulls) * user_tab_col_statistics.avg_col_length.
Once you've done the tricky bits, you need only allow for the typical index block overhead, and the percentage wastage that you consider to be reasonable, plus a couple of percent for branch blocks, and you've got a reasonable estimate of how big the index should be. For further details check http://www.jlcomp.demon.co.uk/index_efficiency_2.html.