Index storage capacity analysis

&

Re-build

Introduction:

Indexes are the vital component in database to search data in most optimal way using filter. By continuous changes in the data due to Insert, Update and Delete operations, the index stored on the database gets fragmented thus increasing Application response time and consuming hardware resources like disk space and CPU. Index rebuild is considered as one of the important activity in performance optimization.

1. Index storage capacity analysis

Execute report RSORAISQ/RSORAISQN through T-code SE38.

Provide any ID to distinguish it from others.

Provide the Index, Table and Tablespace name as shown. (Currently we are analyzing the PSAPSR3 Tablespace so it is provided).

Choose “Start workingset” option with Analyze (Fast) option and execute the report in background.

Check the job status from SM37 –> job name RSORAISQN.

Upon successful completion of job execute the report again. And choose the option “Show History” to get the results.

The report will provide us the list of indexes with their size, storage capacity and other details.

Sort the details in the report by index size and storage capacity and extract the details in sheet.

2. Index Re-build Methods

Method 1: Execute the report RSANAORA (Recommended)

Execute the report RSANAORA.

Select the Index option –> Enter Index names that are to be rebuilt from the extracted sheet.

(To avoid the system load it’s recommended to choose indexes batch wise for rebuild)

After copying the list of Indices through clipboard, select ALTER INDEX REBUILD ONLINE option

Execute the program in Background

This method is recommended as it does the index rebuild by online method.

Method 2: Transaction code – DBACOCKPIT/ DB02OLD

Execute the Transaction Code DB02OLD

Choose the option current sizes

Choose Tables/Indexes option.

Select the index and choose for detailed analysis option.

In the detailed analysis it will show the size of the index which is 192 KB in the current case.

From the menus choose Alter index –> Rebuild

Transaction Code – DBACOCKPIT

Execute the T-code DBACOCKPIT and navigate as space –> segments –> detailed analysis.

From the main screen select the Index.

Main Data tab you can validate / Rebuild index

Storage tab provides the current size and storage quality of selected index

Method 3: Brtools

You can use brtools for rebuild indexes

Launch the brtools by ora<SID> user and choose the following options to traverse.

Method 4: By executing Oracle query

Connect to oracle by

Sqlplus / as sysdba

And execute the query as

Syntax: alter index <tablesapace_name>.<index_name> REBUILD ONLINE

E.g. alter index SAPSR3.CRM_ACE2_OBJ_WL~0 REBUILD ONLINE

3. Result/Observation:

The usable memory capacity of the tablespace is increased. The increased system response time can also be observed.

This can be checked by T-code ST04 –> space –> Tablespace –> Overview.

Before index rebuild:

After index rebuild:

4. Reference Notes:

332677 – Alternatives to rebuild indexes

682926 – Problems with Create/rebuild index with the solutions.

712098 – RSORAISQ: Index Storage Quality Management, in order to fix authorization problem.