how to find which table and index is being used by a long running ABAP report

This documentation is copyright by SAP AG.
How to Find Database Index usage per ABAP Report and Creating an Index

Step 1- Identifying the long running report

Login to the R/3 server and check the background jobs to identify the long running report. Or by usual system monitoring, such long running reports can be isolated. In this case for example purposes, we have taken the audit report RPUAUD000 which is used to display all changes that were made using infotype change documents.

Step 2- ST04 Database performance analysis

Navigate to TCode /n ST04 and click on detailed analysis menu for more options. How to Find Database Index usage
Step 3 – SQL requests

Click on SQL request to get a list of DB statements/access running on the server presently.

Now sort as per buffer gets and click on execute. 

Step 4 – Run the resource consuming report

Now open another SAP session and navigate to TCode /n SE38 to manually run the audit report RPUAUD000 after entering the required data.

Step 5 – Identifying the table being used

There are several ways to identify the database table being accessed, but the easiest will be to navigate to TCode /n SM50 and check the tables being accessed. Narrow down with user name and client details to identify the table for the report being run. In this case it is PCL4 table.

Step 6 – SQL statements accessing PCL4 table

Now that the table being accessed is identified, navigate back to /n ST04 and search for PCL4 table. Click on GOTO-> Find table.

Enter the table name and click on execute.

Step 7 – Index and fields analysis

Select the table and click to access more details.

More details about the index being used, fields and estimated costs/estimated rows are depicted.

Step 8 – Adding new index to optimize more

If the amount of access is on the higher end, adding an index with only the fields that is being accessed by the report will be useful as it will ensure that the database access is overall less leading to more faster data retrieval. Navigate to /n SE12 to check more details of the database table PCL4.

Step 9 – Indexes currently created and adding new one if necessary.

The TCode shows the fields that are present for the table and to find the list of indexes currently configured on the server, click on the tab as below:

As of now, 3 indexes are created on the server. If "choose" is clicked, then more details about the fields being used are shown.

Step 10 – Creating an Index

If the server administrator feels that a new index can be created with less fields to be accessed so that the report runs faster then click on create and enter the below details.

Once the index has been created, do not forget to activate it.