Rebuilding SAP Indexes

When an indexed field in a table is changed repeatedly, the data is deleted from the index and re-inserted at a new block in the index. The new entries are always added to the right side of the index tree and therefor deletion is relatively to the left.

When the index elements are deleted and added repeatedly, the left side of the index tree tends to be sparse and the right side keeps growing. Depending on the size of index, there might be thousands of sparse blocks, which are being checked during execution.

Fragmented index results in increased usage of database space and more blocks being read into the buffer. This can be avoided by rebuilding the index.

One can measure index fragmentation using the report RSORATAD or using DB02 --> Detailed Analysis --> Enter Index --> Detailed Analysis --> Analyze Index --> Storage Quality.

If storage quality is less than 50% you may need to reorg the index. If you wish to run an analysis on all the indexes, run the report RSORAISQN.

Check SAP note 970538 for more details on the restriction with using this report. Do not run the report without reading the note.

You can also get an idea on the amount of fragmentation by comparing the size of the table and the index. If the size of the index is larger than that of the table, the index is heavily fragmented.

To rebuild index, you can use one the following methods:
1. brspace -f idrebuild -i <index_list>
2. Using DB02
DB02 --> Detailed Analysis (in the "Tables and Indexes" section) --> Object Name: <index> --> Detailed Analysis --> Alter Index --> Coalesce / Rebuild
3. Using RSORAISQ report
   Call SAP transaction SE38 and execute the report RSORAISQ.
   Input the tablespace name and the index name in Object Selection section.
   Check rebuild option in Function Code section.
   A report will be generated with details on the index.
   Click on the Index Rebuild button.
   You will be prompted to feed the name of the index and tablespace again, enter the details and execute.
4. Rebuild indexes offline using the report RSANAORA
5. Mass rebuild of indexes using the report RSORAISQN

Deleting unwanted entries from RFC tables (ARFCSSTATE, ARFCSDATA etc.)

375566 Large number of entries in tRFC and qRFC tables
366869 HOLD/EXECUTED/WCONFIRM entries in ARFCRSTATE

For tRFC outbound the tables used are ARFCSSTATE and ARFCSSDATA.

For qRFC outbound the tables used are TRFCQOUT ARFCSSTATE and ARFCSSDATA.

The RFC tables should be kept as small as possible - in some cases these tables can contain millions of entries.

To check the number of entries goto SE16, enter the table name, for example ARFCSDATA, then choose the "Number of Entries" Option:

  

The 3 notes above detail reports for deleting entries in the RFC tables.

Note that report RSTRFCEF is not to be used anymore, we have a new report to check inconsistencies as described in the SAP note: 779664

The report RSTRFCEF will be replaced by the report RSTRFCEG.

Note that report RSTRFCEG contains a parameter CHECK_ONLY, if you mark it, then the inconsistencies will not be deleted.

The standard way to delete queue entries is to run report RSTRFCQD/DS as a batchjob as mentioned in note
763255

In SMQ1 you can goto "QRFC" in the Menu and then choose reorganise, this will delete ALL queues in SMQ1. However, if you want to delete
selected queues then you could choose "Edit" in the menu and then choose "delete Selected objects".

Note 779664 - Consistency check of qRFC queues with deletion

Symptom

You suspect that there are inconsistencies in the data of the qRFC outbound queue. You base this assumption on:

• LUWs that are still displayed in transaction SMQ1, but were already processed.

• An unchanged or a steadily increasing volume of data in the QREFTID or ARFCSDATA tables.

You suspect that there are inconsistencies in the data of the qRFC incoming queue. You base this assumption on:

• LUWs that are still displayed in transaction SMQ2, but were already processed.

Other terms

qRFC, SMQ1, SMQ2
RSTRFCEF, RSTRFCEL, RSTRFCEK, note 760113

Reason and Prerequisites

The inconsistencies could be caused by the fact that, after an LUW was processed, the data describing this LUW could not be deleted from all tables of the qRFC data model.
The tables in question in the outbound processing are: TRFCQOUT, QREFTID , ARFCSSTATE, ARFCSDATA.
The tables in question in the inbound processing are: TRFCQIN, TRFCQINS, TRFCQSTATE, TRFCQDATA.

Solution

You can identify and delete inconsistencies in the outbound queue using report RSTRFCEG.
You can identify and delete inconsistencies in the inbound queue using report RSTRFCEH.

• Attributes of the RSTRFCEG and RSTRFCEH reports:

• The reports should be scheduled as a background job.

• If the background job is executed parallel to other processing in the qRFC using the "Check only" option, it may also report temporary inconsistencies that can no longer be found as inconsistencies when the processing finishes. The report therefore already checks all inconsistencies a second time.

• If the background job is used to delete inconsistent entries and is executed in parallel to other processes in the qRFC, database locks may occur. We therefore recommend that you do not carry out any parallel processing for the deletion.

• All checks are carried out in all clients in the system.

• The QDEL value is required for executing reports in the S_ADMI_FCD authorization object.

• Common parameters of the RSTRFCEG and RSTRFCEH reports:

• LUWs in the memory (READ)  This parameter enables you to limit the number of LUWs to be checked. This parameter is essential for the system load (low value) and performance (high value).

• LUWs up to DB commit (BLK_SIZE) - This parameter controls the number of inconsistent LUWs before the database commit is executed for the deletion. This parameter is essential for the system load (low value) and performance (high value).

• "Check only" checkbox (CHECK) - This parameter enables you to display the number of inconsistent LUWs. Deletions are not possible in this mode.

• Additional parameters of the RSTRFCEG report:

• "TRFCQOUT table check" (QOUT) checkbox - This parameter activates the check from the TRFCQOUT table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "ARFCSSTATE table check" checkbox (STATE) - This parameter activates the check from the ARFCSSTATE table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "QREFTID table check" checkbox (REFTID) - This parameter activates the check from the QREFTID table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "ARFCSDATA table check" checkbox (DATA) - This parameter activates the check from the ARFCSDATA table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• Additional parameters of the RSTRFCEH report:

• "TRFCQIN table check" checkbox (QIN) - This parameter activates the check from the TRFCQIN table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "TRFCQINS table check" checkbox (QINS) - This parameter activates the check from the TRFCQINS table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "TRFCQSTATE table checkbox" check (STATE) - This parameter activates the check from the ARFCSSTATE table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

• "TRFCDATA table check" checkbox (DATA) - - This parameter activates the check from the ARFCSDATA table. If you can already eliminate this table as the source element for an inconsistency, for performance reasons, you should deactivate this check.

The reports mentioned above are provided in the Support Packages attached to this note. You can receive local versions of the reports from the attachment to this note and create them yourself in the customer namespace.

1. Weekly rebuild index table: ARFCSSTATE, ARFCSDATA, ARFCRSTATE, TRFCQOUT, TRFCQIN, TRFCQSTATE and TRFCQDATA
2. RSTRFCQDS (Outbound Q delete), RSTRFCER (ARFCRSTATE delete) as Monthly job
3. Delete inconsistencies in the outbound queue using report RSTRFCEG and delete inconsistencies in the inbound queue using report RSTRFCEH by Monthly basis.

Note 444287 - Checking the index storage quality

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.

Solution

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;

              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, pct_used FROM index_storage
WHERE pct_used < 60 and lf_blks >4 order by pct_used desc;