Summary
Symptom
Poor performance despite correct index
Reason and Prerequisites
If a data record is deleted from a table, it must also be deleted from the index. However, the space used by this index entry is not released again, but a placeholder is retained at that point in the index tree instead. If a new data record is inserted into the index tree at that point, the space is used again; until this happens, however, this space remains unused. If a very large number of these placeholders exist in the leaf blocks, MORE blocks must be read when accessing the index in order to find all applicable data records.
If a statement takes a particularly long time even though the correct index is used, it is advisable to check the storage quality of the index.
SolutionIf a statement takes a particularly long time even though the correct index is used, it is advisable to check the storage quality of the index.
There are three different ways of checking the storage quality of the index.
- 1. At R/3 level - rough analysis:Transaction db02 -> 'Detail analysis' button -> Enter table name -> 'Table <-> Indices ' button -> Click index to be checked -> 'Detail analysis' button -> menu analyze index -storage qualityA percentage is specified here for the storage quality. This percentage is a rough calculation but generally provides a good overview of the storage quality. If the specified index storage quality percentage is high, you can assume that you do not need to reorganize the index.If a percentage less than 70% is specified here, a reorganization of the index is usually recommended. Unfortunately, the procedure is inaccurate for very small indexes and may display a small percentage even though the index was reorganized. You should therefore also check the 'number of blocks' value, if this is lower than 10, we recommend that you use one of the remaining two methods to find out the storage quality - this should be executed very quickly with this size.
- 2. At R/3 level:Caution: this method 'locks' parts of the index against updates, that is, we recommend that you only execute this option if there is a low user load on the system. Update statements on this index sometimes wait until the result of the storage analysis is returned if this option is selected.Transaction db02 -> 'Detail analysis' button -> Enter table name -> 'Table <-> Indices ' button -> Click index to be checked -> 'Detail analysis' button -> Menu analyze index - validate structure in dialog
This is how the current statistics are determined and displayed. The following section is relevant in the 'Analysis of B*-tree' section:
B*-tree leaf blocks
entries
deleted
The relationship deleted/entries should be less than 25%. Caution
- 1. At Oracle level:Caution: this method 'locks' parts of the index against updates, that is, we recommend that you only execute this option if there is a low user load on the system. If this option is selected, update statements against this index may wait until the result of the storage analysis has been returned.sqlplus ' / as sysdba'SQL> connect internal;SQL> analyze index sapr3."<indexname>" validate structure;
The index_stats view is therefore filled with the relevant statistics. However, these are only ever available for a single index in this view. As soon as another index is analyzed, the statistics of the first index are no longer available. Furthermore, these statistics are only available from this one session. If index_stats is selected from another session, no data record is returned.
SQL> SELECT (del_lf_rows/(lf_rows+1)*100) wastage_deletion, 100-(lf_rows_len/(lf_blks*lf_blk_len)*100) wastage_split
FROM index_stats;
SQL> SELECT (del_lf_rows/(lf_rows+1)*100) wastage_deletion, 100-(lf_rows_len/(lf_blks*lf_blk_len)*100) wastage_split
FROM index_stats;
Both returned values should be LESS than 25. Being 25.
- a) Variant changed as of Oracle 9:As of Oracle 9, the 'online' addition can be attached to the analyze index command to minimize locking. Unfortunately, this online method does not create any statistics, which means that the addition is currently NOT useful for our purposes.
- 2. At Oracle level for all indexes using script:The same method as above applies here, but the storage quality for all indexes of the user sapr3 can be checked with this script. Execute this only at times of little or no system activity since some data records are locked during this check.First create a table in which the data records can be temporarily stored (only create this table once):sqlplus sapr3/<passwd>CREATE TABLE index_storage( name varchar2(30), height number, blocks number, lf_rows number, lf_blks number, lf_rows_len number, del_lf_rows number, btree_space number, pct_used number, wastage_deletion number);You can then carry out the analysis. The result is stored in the table index_storage.sqlplus sapr3/<passwd>set heading off feedback off pagesize 0 echo off;truncate table index_storage;spool index_storage.sqlSELECT 'ANALYZE INDEX "' || index_name || '" VALIDATE STRUCTURE;' , 'INSERT INTO index_storage SELECT name, height, blocks, lf_rows, lf_blks, lf_rows_len, del_lf_rows, btree_space, pct_used, (del_lf_rows/(lf_rows+1)*100) FROM index_stats;' FROM dba_indexes where owner='SAPR3';spool off;@index_storage.sql
You can use the following select statement to display all indexes with poor storage quality;
SELECT name, wastage_deletion FROM index_storage
WHERE wastage_deletion >= 25 order by wastage_deletion desc;
The percentage from the two methods directly from Oracle refers to the portion of 'empty' blocks in the indexes.
Aside from fragmentation caused by deleting records, the storage quality of an index can also deteriorate if large numbers of data records are inserted, which means that leaf blocks have to be split so that the data records can be saved. A split such as this must be carried out if a block is 100% full, but a particular data record has to be stored due to the sort sequence of the key in the block. In this case, a new block is added to the index, and the data records for the block that is 100% full are distributed between the two blocks. It is useful to recreate these indexes from time to time. This means that both blocks are only 50% full, which has a negative effect on storage quality.
It makes sense to also set up these indexes again from time to time.
SELECT name, wastage_deletion FROM index_storage
WHERE wastage_deletion >= 25 order by wastage_deletion desc;
The percentage from the two methods directly from Oracle refers to the portion of 'empty' blocks in the indexes.
Aside from fragmentation caused by deleting records, the storage quality of an index can also deteriorate if large numbers of data records are inserted, which means that leaf blocks have to be split so that the data records can be saved. A split such as this must be carried out if a block is 100% full, but a particular data record has to be stored due to the sort sequence of the key in the block. In this case, a new block is added to the index, and the data records for the block that is 100% full are distributed between the two blocks. It is useful to recreate these indexes from time to time. This means that both blocks are only 50% full, which has a negative effect on storage quality.
It makes sense to also set up these indexes again from time to time.
SELECT name, wastage_deletion, pct_used FROM index_storage
WHERE pct_used < 60 and lf_blks >4 order by pct_used desc;
No comments:
Post a Comment