1. How are indexes built?
An index contains the values of the indexed table columns in a sorted order.
A normal B* tree index is a tree made up of blocks with up to five levels. The highest level consists of only one block which is called root block and which is the basis for every index access. The lowest level consists of a potentially very large number of blocks that are called leaf blocks and that contain the actual data from left to right in a sorted order (values of the indexed columns and ROWID for the access to the relevant table entry). The entries are called leaf rows. The leaf rows also include earlier index entries that were not physically removed from the index, but were only marked as deleted. This special type of leaf row is called a deleted leaf row.
Depending on the size of the index, additional index levels with branch blocks may exist between the root blocks and leaf blocks, and these contain control data for a fast search of the relevant data in the index.
If an index only consists of one level, the root block is also a leaf block and contains the actual data. If an index consists of several levels, the root block can be compared with the branch blocks and contains information about navigating in the index.
In general, an index mainly consists of leaf blocks. If there is a very large number of index entries or if a COALESCE has been executed for a fragmented index, the number of branch blocks may be comparatively high.
2. What does index fragmentation mean
The fragmentation of an index specifies how well the space is used in the B* tree of an index. The smaller the space that is actually used, the more fragmented the index. In this case, the index may also be called unbalanced or degenerated.If the space outside the structure of the index B* tree in an index segment is not used, then this is not strictly speaking index fragmentation. In this case, the use of space is simply not optimal. For more information, also see Note 821687.
3. What problems are caused by index fragmentation?
Index fragmentation causes the following problems:
Database growth
The greater the index fragmentation, the more space is unused in the index B* tree. This means that the index segment and the entire database grow more quickly than is necessary.
Poor performance by the index range scan
If an index is fragmented, then index accesses using the index range scan may have to read more blocks than is actually necessary. The 'buffer gets' and 'disk reads' associated with this process increase the access time considerably.
Poor global performance as a result of block displacements in the buffer pool
If, due to index fragmentation, more blocks must be read into the Oracle buffer pool than is strictly necessary, blocks are unnecessarily displaced from other segments. This in turn means that the buffer must be repeatedly reloaded, which can impede the performance of the entire database.
4. What triggers an index fragmentation?
Index fragmentation usually occurs when data is repeatedly deleted from the index and reinserted at a different location. In particular, this happens when a field is indexed whose values increase with time. If older entries are deleted, it is always from the left side of the index while new entries are always inserted on the right side of the index. As a result, the index tree is more or less empty on the left side, but keeps growing on the right side.
5. Which SAP tables typically undergo index fragmentation?
In particular, index fragmentation affects tables that are subject to considerable data changes and that also index any column values that increase with time. RFC tables, in particular, belong to this category. For more information, see Notes 706478 and 435125.
6. How can I measure index fragmentation?
There are two approaches for measuring index fragmentation:
Storage quality
The storage quality compares the available space with the space used, whereby the two values are roughly determined as follows:
- The available space is a result of the number of index blocks used multiplied by the block size (8192 bytes) minus the administration overhead for the block header and similar.
- The space that is filled is a result of the number of index entries multiplied by the average length of an entry plus 6 bytes for the ROWID (-> space used in the leaf blocks), bytes for marking the start of a record or column, and an overhead for the root blocks and branch blocks.
The smaller the space actually used in comparison with the total available space, the lower the index storage quality and the more fragmented the index.
Leaf row quality
The number of deleted leaf rows compared with all leaf rows is another measure for index fragmentation. The larger the portion of the deleted leaf rows compared with all leaf rows, the more fragmented the index.
7. As of which percentage value do I need to rebuild indexes?
It is not possible to give a general recommendation for the percentage value index at which fragmentation becomes critical. However, empirical values have the following tendencies:
Storage quality of 50% or higher: No action required
Storage quality between 25% and 50%: Action required in individual cases
Storage quality of 25% or lower: The index should be rebuilt.
It is logical to rebuild an index if it is clear that it occupies an unnecessarily large number of blocks in the buffer pool or that it is responsible for a large number of 'buffer gets' as a result of the fragmentation.
8. Which restrictions exist for these methods?
You must bear the following restrictions in mind when determining the storage quality:
Especially with small indexes, the space used is often very small compared with the available space (for example, a 20-byte entry in an 8K leaf block if the index only contains one row). Therefore, for small indexes, the storage quality is only useful to a certain extent. This is not a severe restriction because small indexes usually do not trigger performance problems due to fragmentation.
Generally speaking, it is difficult to correctly include ALL factors that contribute to the space calculations. For this reason, the mechanism of the index block splits, block-internal fragmentation due to record lengths, or storage parameters such as PCTFREE also play significant roles in terms of how much space can potentially be used.
The method of the deleted leaf rows has the following restrictions:
If an entry is added to a leaf block, all deleted leaf rows are removed from this block. The block may then be almost empty. However, this is not recognized because deleted leaf rows no longer exist.
The deleted leaf rows only ever refer to the leaf blocks. The number of existing branch blocks is ignored. For example, a COALESCE always results in 0 deleted leaf rows even though the index still has the same number of branch blocks and therefore can still be fragmented.
For these reasons, both methods can only be an INDICATOR of the level of index fragmentation, but they can never PROVE it. Therefore, fixed thresholds as of which we could say that an index is fragmented in a way that affects performance cannot be specified for the deleted leaf rows and the storage quality.
9. How can I detect fragmented indexes?
Report RSORATAD
To determine the index storage quality, SAP provides the RSORATAD report, which you can call directly in transaction SE38, or by using:
DB02
-> Detailed Analysis
-> Enter Index
-> Detailed Analysis
-> Analyze Index
-> Storage Quality
For more information, see Note 444287.
Advantages:
Disadvantages:
- It can only be executed for one index at a time.
- It has a long runtime.
- It causes a high system load.
- There is no support for bitmap indexes and partitioned indexes.
- You can only trust the results up to a point because B*TREE blocks on the FREELIST are not included in the calculation.
Report RSORAISQN
Unlike RSORATAD, you can use RSORAISQN to start an analysis of several or all indexes. For more information, see Note 970538.
Advantages:
- A lock is not necessary.
- You can access historical data.
- You can analyze several or all indexes with one call.
Disadvantages:
- It has a long runtime.
- It causes a high system load.
- There is no support for bitmap indexes and partitioned indexes.
- You can only trust the result up to a point because B*TREE blocks on the FREELIST are not included in the calculation. Therefore, a significantly higher storage quality may be calculated.
ANALYZE INDEX VALIDATE STRUCTURE
You can use the Oracle command ANALYZE INDEX VALIDATE STRUCTURE to determine both the storage quality and the leaf row quality of an index. Note 444287 describes how you can execute the command in transaction DB02 or at Oracle level, and how you can interpret the results.
Advantages:
- Since it is script-based, you can analyze ALL indexes.
- It retrieves exact information about the index fragmentation.
Disadvantages:
- The table is locked for DML statements (INSERT, UPDATE, DELETE) during the analysis.
- It has a long runtime.
- It causes a high system load.
BRCONNECT (option INDEX_STORE)
You can use the BRCONNECT option INDEX_STORE to determine the storage quality of indexes on the basis of ANALYZE TABLE VALIDATE STRUCTURE:
brconnect -u / -c -f stats -t
-v index_store
The results are stored in the DBSTATIORA table. The storage quality is determined by the relationship of the column values INDRU and INDBS. For more detailed information, see Note 554031.
Advantages:
- Any number of indexes can be analyzed in a run.
- It retrieves exact information about the index fragmentation.
Disadvantages:
- The table is locked for DML statements (INSERT, UPDATE, DELETE) during the analysis.
- It has a long runtime.
- It causes a high system load.
Index tree dump
You can use the object ID of an index to create a tree dump of the index from which you can retrieve information about leaf rows and deleted leaf rows:
SELECT OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = '';
ALTER SESSION SET EVENTS
'immediate trace name treedump level ';
The tree dump is stored in the saptrace/usertrace directory and contains a row for each index block. For example:
----- begin tree dump
branch: 0x4437342 71529282 (0: nrow: 7, level: 2)
branch: 0x8d5bab 9264043 (-1: nrow: 671, level: 1)
leaf: 0x4437343 71529283 (-1: nrow: 540 rrow: 540)
leaf: 0x4437344 71529284 (0: nrow: 533 rrow: 522)
leaf: 0x4437345 71529285 (1: nrow: 533 rrow: 517)
...
In the next step, you can display the leaf block rows (that are preceded by "leaf:" in the example above). The number behind 'nrow' is the number of leaf rows in the block while 'rrow' specifies the number of leaf rows that actually exist. Therefore, the difference between the two values is the number of deleted leaf rows in the block. If you add 'nrow' and 'nrow - rrow' for all leaf block rows, you get the number of leaf rows and deleted leaf rows in the entire index. You can use a script to automate this calculation. Example (: name of the tree dump file):
grep leaf | perl -p -i -e 's/.*nrow: (.*) rrow:
(.*)\)/$1\t$2/' | perl -e 'while (<>) { $leaf_rows += (split)[0];
$del_leaf_rows += (split)[0] - (split)[1];} print "Leaf Rows:
$leaf_rows\nDeleted Leaf Rows: $del_leaf_rows\nDeleted Leaf Rows
(%): " . int(($del_leaf_rows / $leaf_rows) * 100 + 0.5) . "\n"'
Advantages:
- A lock is not necessary.
- It retrieves exact information about the index fragmentation.
- It has a slightly shorter runtime than VALIDATE STRUCTURE.
Disadvantages:
- It has a long runtime.
- It causes a high system load.
- Space is required for the dump file.
- You require an analysis script for the tree dump file.
- The format of the dump file may vary between different Oracle releases.
CBO statistics
When creating CBO statistics, information that can be used to determine the storage quality of indexes is collected (for example, average column length, number of index leaf blocks, and number of index entries). Provided that the statistics are up-to-date and not too inaccurate, you can use them to determine the approximate storage quality of indexes. The following statement allows you to obtain the 20 B*TREE indexes, which contain more than 1,000 leaf blocks, and have the lowest storage quality:
COLUMN ROWLEN FORMAT 999990
COLUMN QLTY FORMAT 999990
SELECT * FROM
(SELECT
SUBSTR(I.INDEX_NAME, 1, 20) INDEX_NAME,
I.NUM_ROWS NUM_ROWS,
SUM(TC.AVG_COL_LEN + 1) + 7 ROWLEN,
I.LEAF_BLOCKS LEAVES,
ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /
1000000, 0) NET_MB,
ROUND(I.LEAF_BLOCKS * (8079 - 23 * I.INI_TRANS) *
(1 - I.PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /
(I.LEAF_BLOCKS * (8079 - 23 * I.INI_TRANS) *
(1 - I.PCT_FREE / 100)) * 100, 0) QLTY
FROM DBA_TABLES T, DBA_INDEXES I, DBA_TAB_COLUMNS TC,
DBA_IND_COLUMNS IC
WHERE T.TABLE_NAME = TC.TABLE_NAME AND
T.OWNER = TC.OWNER AND
I.INDEX_NAME = IC.INDEX_NAME AND
I.OWNER = IC.INDEX_OWNER AND
TC.TABLE_NAME = IC.TABLE_NAME AND
TC.OWNER = IC.INDEX_OWNER AND
TC.COLUMN_NAME = IC.COLUMN_NAME AND
I.INDEX_TYPE = 'NORMAL' AND
I.OWNER LIKE 'SAP%' AND
I.LEAF_BLOCKS > 200
GROUP BY T.TABLE_NAME, T.OWNER, I.NUM_ROWS, I.LEAF_BLOCKS,
I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
ORDER BY 7)
WHERE ROWNUM <=20;
Firstly, this statement determines how much net space is allocated by the entries in the index (NET_MB). Secondly, it determines the space that is actually available (excluding block headers, ITLs and INI_TRANS) in the index leaf blocks (GROSS_MB). The relationship between both values provides the storage quality of the index, whereby root blocks and branch blocks are not included in the calculation.
Advantages
- A lock is not necessary.
- The system load is insignificant.
- It has a short runtime.
- It provides relatively precise information regarding index fragmentation.
- (provided that the statistics are adequate)
Disadvantages
- It does not provide any information about indexes without statistics.
- The quality of the result relies heavily on the available CBO statistics being up-to-date and accurate.
- Fragmentation effects in root blocks and branch blocks are not examined.
- It cannot be used for bitmap indexes.
RSORAISQN
The new RSORAISQN function described in Notes 970538 and 979054 combines the analysis options of RSORAISQ ("Exact" analysis) with the approach to CBO statistics ("Fast" analysis). The advantages and disadvantages are the same as those associated with RSORAISQ or the approach to CBO statistics.
BR986W warnings during BRCONNECT statistical runs
Check the log files of the BRCONNECT statistical runs for entries of the following type:
BR986W Index is unbalanced - please rebuild the index
BR0986W index is unbalanced - please rebuild the index
These entries indicate that the index is fragmented. The system always reports the primary index to determine the necessity of statistics, since this is the only one analyzed by BRCONNECT. However, ALL of the indexes of the relevant tables should be rebuilt because it is likely that all indexes will be fragmented to a certain extent.
Advantages:
- A lock is not necessary.
- An additional system load is not caused because it is a by-product of the statistics that are generated anyway.
Disadvantages:
- There is no guarantee that all fragmented indexes are recognized or that the index mentioned in the warning is actually fragmented (see Note 439783).
Index size
Highly fragmented large indexes, in particular, can often be detected by comparing the table sizes (for example, in transaction DB02). If an index is larger than the table, this is an indicator that the index is fragmented. It is rare that all of the most important table columns are contained in the same index.
Even if the index is smaller than the table, you may detect a possible fragmentation when you use a plausibility check based on the indexed columns and typical column entries. For example, if an index only indexes two columns with a maximum of five characters each, while the entries in the table contain a large number of additional longer columns, the index is probably already fragmented even if it is only half the size of the table.
Advantages
- There is no significant system load.
- The runtime is short.
- A lock is not necessary.
Disadvantages
- It is only a very rough indicator.
- Blocks that were already allocated during an extent, but are not yet contained in the index tree, are included in the calculation even though they are irrelevant for the index fragmentation.
Index blocks in the Oracle buffer pool
If an index is fragmented, more blocks must be kept in the Oracle buffer pool. Therefore, it may be useful to determine the indexes with the highest number of blocks in the buffer pool so that you can subsequently check for fragmentation. You can use the SQL statement contained in Note 789011 to determine the objects with the most blocks in the pool buffer.
Advantages:
- The system load is low.
- The runtime is short.
- A lock is not necessary.
- It analyzes all indexes at the same time, and not only one index.
- It returns the indexes that currently have a lot of memory allocated to them, and that are therefore also relevant for performance.
Disadvantages:
- It is only a rough guideline that also often returns indexes that are not fragmented.
- Indexes that currently cannot be accessed are ignored because they do not have any blocks or only a few blocks in the buffer pool.
Block accesses during each execution
You can also use the block accesses during each execution, which can be determined with an SQL analysis (see Note 766349) in the shared cursor cache, as an indicator for a fragmented index. This means, for example, that an INDEX RANGE SCAN with a subsequent TABLE ACCESS BY INDEX ROWID in which 100 entries are read, does not require much more than 200 block accesses if all selective columns in the selection condition are specified with "=". If the actual values are higher, this is often due to the effects of an index fragmentation.
Advantages:
- It provides very precise information about the influence of the fragmentation on the number of the block accesses (and therefore on performance).
- The system load is low.
- The runtime is short.
- A lock is not necessary.
Disadvantages:
- You require good knowledge of SQL optimization.
- You cannot always come to a conclusion.
- An increased number of block accesses may also have other causes (such as parallel changes or roundtrips).
Space utilization of the underlying table
If a table has a lot of free space, there may be fragmentation effects even in the indexes created on this table. Typically, this situation arises after a large amount of data is deleted from the table. For more information, see Note 821687. Point 5 describes how to identify tables with a low rate of utilization.
Advantages:
- The system load is low.
- The runtime is short.
- A lock is not necessary.
Disadvantages:
- It ignores the indexes of tables that have no statistics.
- It ignores index fragmentation that is not triggered by mass deletion.
SAMPLE_SIZE much smaller than the selected statistic sample size
If the SAMPLE_SIZE to be found in DBA_TABLES is significantly smaller than the sample size selected when creating the statistics, this is a indicator of table fragmentation and therefore also index fragmentation. To determine these tables, proceed as described in Note 588668 (19).
Advantages:
- The system load is low.
- The runtime is short.
- A lock is not necessary.
Disadvantages:
- It ignores the indexes of tables that have no statistics.
- It ignores index fragmentation that does not correlate with table fragmentation.
- The sample size may be significantly below the standard BRCONNECT value for other reasons too, such as manually generated statistics.
10. Why can I not use ANALYZE INDEX VALIDATE STRUCTURE ONLINE?
You cannot use the online variant of ANALYZE INDEX VALIDATE STRUCTURE to determine index fragmentation because it does not return the necessary data for the leaf rows and the deleted leaf rows. Oracle regards this as a feature rather than a bug.
11. Which Oracle commands can I use to defragment an index?
You have the following options to defragment an index:
DROP and CREATE
REBUILD
COALESCE
For a description of these options, including their advantages and disadvantages, see Note 332677. In the case of REBUILD ONLINE operations, see also the problems described in Note 682926.
12. Which SAP tools can I use to defragment an index?
SAP delivers the following tools that are based on the Oracle commands described above:
brspace -f idrebuild -i
With this BRSPACE function, you can use the REBUILD command to rebuild a list of indexes. For detailed information about BRSPACE, refer to the SAP online documentation for SAP Web Application Server 6.40 and higher, and also to SAP Service Marketplace under the alias DBAORA.
Report RSANAORA
You can use the RSANAORA report to rebuild an index (by using the REBUILD command).
Transaction DB02
You can use transaction DB02 to defragment an index as follows:
DB02
-> Detailed Analysis (in the "Tables and Indexes" section)
-> Object Name:
-> Detailed Analysis
-> Alter Index
-> Coalesce / Rebuild
Report RSORAISQN
The RSORAISQN report (Note 970538) allows you to rebuild indexes based on various criteria, such as index storage quality, for example.