How to find indexes which are candidate for rebuild?
I know it’s a very arguable question, but let me put my words and then you can comment.
I follow different approaches to find out indexes for rebuild
- Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
- Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First "Analyze the index with validate structure option" and then,
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
But (a big but), the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.
See this example:
SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index
Index analyzed.
SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------ ---------- ---------- ----------- -------------
TEST_INDX 8 938752 29575 73342
You can see height of the index is 8 and also high number of DEL_LF_ROWS
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';
COUNT(*)
----------
39700
Elapsed: 00:00:27.25
Execution Plan
----------------------------------------------------------
Plan hash value: 870163320
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 1 117 10 (0) 00:00:01
1 SORT AGGREGATE 1 117
*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
764 consistent gets
757 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Now you rebuild the indexes
SQL> alter index TEST_INDX rebuild;
Index altered.
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';
COUNT(*)
----------
39700
Elapsed: 00:00:06.18
Execution Plan
----------------------------------------------------------
Plan hash value: 870163320 - See here although it is using the same plan but still it is faster
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------
0 SELECT STATEMENT 1 117 6 (0) 00:00:01
1 SORT AGGREGATE 1 117
* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
592 consistent gets
588 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
TEST_INDX 4 865410 15434 0
This clearly indicates rebuild helped my query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0
Now coming to second of part of Harvinder's comment.
Possible ways of Rebuilding.
- Online/Offline.
ONLINE Rebuild (8i onwards)
SQL> Alter index rebuild online;
This allows parallel DML to go on while Index is getting rebuild. Remember, online index requires more space, as it creates a new index before dropping the old one.
Index Rebuild is primarily a 3 step process
Prepare phase: Oracle locks the table for a fraction of second (actually not felt) to build index structure and populate data-dictionary.
Build phase: Most of the work is done in this phase. Oracle engine populates the index using the table and allows parallel DML's, parallel DML's uses a temporary journal table (b tree index like structure) to host the entries while the new index is getting populated.
Merge phase: Now the final stage, Oracle merges the new index with the journal table and drops the old index. Even during the merge phase, any changes to the table are recorded in the journal table and they get merged towards end of this phase.
9i onwards, online index rebuild feature includes Reverse Key, Function Based and Key Compressed indexes.
Offline Index rebuild.
SQL> Alter index rebuild;
This is conventional rebuild which was used(still available) before 8i. In this rebuild process, oracle drops the old index and creates a new one. In this process, no extra space is required, but parallel dml's are not supported.
************************************************************
Add on
Important update: 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 coalesce or rebuilding and note this demonstration of an index that benefits from scheduled oracle index rebuilding.
Also, please see my updates and other notes on index rebuilding strategies, and my complete notes are found in my book "Oracle Tuning: The Definitive Reference".
Some people suggest that indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema, bear in mind that your schema may contain many thousands of indexes, and a complete rebuild can be very time consuming.
Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Let’s look at one method for accomplishing this task.
How rare are "bad" indexes?
You cannot generalize to say that index rebuilding for performance is rare, or even medium rare, it depends on many factors, most importantly the characteristics of the application.
• In scientific applications (clinical, laboratory) where large datasets are added and removed, the need to rebuild indexes is "common".
• Conversely, in system that never update or delete rows, index rebuilding rarely improves performance.
• In systems that do batch DML jobs, index rebuilding "often" improves SQL performance.
Oracle MOSC note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Oracle's index rebuilding guidelines appear in MOSC note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.
However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.
These deleted leaf nodes can be easily identified by running the IDL.SQL script.
The number of deleted leaf rows
The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.
Index height
The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.
Oracle indexes can support many millions of entries in three levels. Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to it's pristine state.
Gets per index access
The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache.
Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands to populate the statistics in dba_indexes and related dictionary tables:
ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE
We might want to rebuild an index if the “block gets” per access is excessive. This happens when an index becomes "sparse" after high delete activity, making full-index scans requires unnecessary I/O. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes.
As you may know, you can easily rebuild an Oracle index with the command:
ALTER INDEX index_name REBUILD tablespace FLOP;
Done properly during scheduled downtime, rebuilding an index is 100% safe. Note the use of the tablespace option. When rebuilding multi-gigabyte indexes, many DBA's will rebuild partitioned indexes into a fresh, empty tablespace for greater manageability. ( I use the convention ts_ndexname_flip, and ts_indexname_flop)
The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:
alter index index_name
rebuild
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:
1. Walk the existing index to get the index keys.
2. Populate temporary segments with the new tree structure.
3. Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.
As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.
Most Oracle administrators run this script, and then select the index that they would like to rebuild. Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is not rebuilt within the default tablespace (usually SYS).
Be aware that it's always a good idea to move an index into another tablespace and you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.
Update:
When can we "prove" a benefit from an index rebuild? Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure:
"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache."
In an OracleWorld 2003 presentation titled “Oracle Database 10g: The Self-Managing Database” by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes.“
This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index:
“The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.”
I know it’s a very arguable question, but let me put my words and then you can comment.
I follow different approaches to find out indexes for rebuild
- Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
- Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First "Analyze the index with validate structure option" and then,
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
But (a big but), the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.
See this example:
SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index
Index analyzed.
SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------ ---------- ---------- ----------- -------------
TEST_INDX 8 938752 29575 73342
You can see height of the index is 8 and also high number of DEL_LF_ROWS
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';
COUNT(*)
----------
39700
Elapsed: 00:00:27.25
Execution Plan
----------------------------------------------------------
Plan hash value: 870163320
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 1 117 10 (0) 00:00:01
1 SORT AGGREGATE 1 117
*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
764 consistent gets
757 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Now you rebuild the indexes
SQL> alter index TEST_INDX rebuild;
Index altered.
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';
COUNT(*)
----------
39700
Elapsed: 00:00:06.18
Execution Plan
----------------------------------------------------------
Plan hash value: 870163320 - See here although it is using the same plan but still it is faster
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------
0 SELECT STATEMENT 1 117 6 (0) 00:00:01
1 SORT AGGREGATE 1 117
* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
592 consistent gets
588 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------
TEST_INDX 4 865410 15434 0
This clearly indicates rebuild helped my query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0
Now coming to second of part of Harvinder's comment.
Possible ways of Rebuilding.
- Online/Offline.
ONLINE Rebuild (8i onwards)
SQL> Alter index rebuild online;
This allows parallel DML to go on while Index is getting rebuild. Remember, online index requires more space, as it creates a new index before dropping the old one.
Index Rebuild is primarily a 3 step process
Prepare phase: Oracle locks the table for a fraction of second (actually not felt) to build index structure and populate data-dictionary.
Build phase: Most of the work is done in this phase. Oracle engine populates the index using the table and allows parallel DML's, parallel DML's uses a temporary journal table (b tree index like structure) to host the entries while the new index is getting populated.
Merge phase: Now the final stage, Oracle merges the new index with the journal table and drops the old index. Even during the merge phase, any changes to the table are recorded in the journal table and they get merged towards end of this phase.
9i onwards, online index rebuild feature includes Reverse Key, Function Based and Key Compressed indexes.
Offline Index rebuild.
SQL> Alter index rebuild;
This is conventional rebuild which was used(still available) before 8i. In this rebuild process, oracle drops the old index and creates a new one. In this process, no extra space is required, but parallel dml's are not supported.
************************************************************
Add on
Important update: 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 coalesce or rebuilding and note this demonstration of an index that benefits from scheduled oracle index rebuilding.
Also, please see my updates and other notes on index rebuilding strategies, and my complete notes are found in my book "Oracle Tuning: The Definitive Reference".
Some people suggest that indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema, bear in mind that your schema may contain many thousands of indexes, and a complete rebuild can be very time consuming.
Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Let’s look at one method for accomplishing this task.
How rare are "bad" indexes?
You cannot generalize to say that index rebuilding for performance is rare, or even medium rare, it depends on many factors, most importantly the characteristics of the application.
• In scientific applications (clinical, laboratory) where large datasets are added and removed, the need to rebuild indexes is "common".
• Conversely, in system that never update or delete rows, index rebuilding rarely improves performance.
• In systems that do batch DML jobs, index rebuilding "often" improves SQL performance.
Oracle MOSC note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Oracle's index rebuilding guidelines appear in MOSC note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.
However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.
These deleted leaf nodes can be easily identified by running the IDL.SQL script.
The number of deleted leaf rows
The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.
Index height
The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.
Oracle indexes can support many millions of entries in three levels. Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to it's pristine state.
Gets per index access
The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache.
Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands to populate the statistics in dba_indexes and related dictionary tables:
ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE
We might want to rebuild an index if the “block gets” per access is excessive. This happens when an index becomes "sparse" after high delete activity, making full-index scans requires unnecessary I/O. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes.
As you may know, you can easily rebuild an Oracle index with the command:
ALTER INDEX index_name REBUILD tablespace FLOP;
Done properly during scheduled downtime, rebuilding an index is 100% safe. Note the use of the tablespace option. When rebuilding multi-gigabyte indexes, many DBA's will rebuild partitioned indexes into a fresh, empty tablespace for greater manageability. ( I use the convention ts_ndexname_flip, and ts_indexname_flop)
The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:
alter index index_name
rebuild
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:
1. Walk the existing index to get the index keys.
2. Populate temporary segments with the new tree structure.
3. Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.
As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.
Most Oracle administrators run this script, and then select the index that they would like to rebuild. Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is not rebuilt within the default tablespace (usually SYS).
Be aware that it's always a good idea to move an index into another tablespace and you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.
Update:
When can we "prove" a benefit from an index rebuild? Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure:
"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache."
In an OracleWorld 2003 presentation titled “Oracle Database 10g: The Self-Managing Database” by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes.“
This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index:
“The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.”
Comments
Post a Comment