Index storage capacity analysis & Re-build
Index storage capacity analysis
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
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.