SQL Trace Analysis in SAP

From the developer’s perspective, SQL (Structured Query Language) trace helps us to improve the overall performance of the program by understanding the amount of time a particular SELECT statement is taking to fetch the data from the database.

In SQL trace we would see the syntax of the SELECT statement used in ABAP program differs. This is because every time when a SELECT statement executed from SAP end, it gets converted into standard SQL query structure of ORACLE database.

We can take the SQL traces through the transactions: ST12/STAD/ST05/ST04/ST30. Now let’s see how to take the traces through the T-code ST12.

1. Run the T-Code ST12 and click on Current mode.

2. Provide the Program name for which you want to take the trace and execute the program. If you want to take the traces of a function module then select Transaction radio button and specify the T-code SE37, it will take to the Function Module Builder and then give the FM name and Execute. After the execution click on Full screen.

3. Select the trace against your user Id and Comment and click on SQL Trace Summary.

4. Now you will see all the SELECT statements used in the program and their duration. If you want to get into the depth of the SQL statement click on EXPLAIN TOOL.

5. The EXPLAIN TOOL displays the execution plan for a SQL statement. It gives the technical info about how the table was accessed and the Key/Secondary index it used to retrieve the table contents.

Sometimes in execution plan for a SQL statement, you might encounter a scenario wherein the system is performing full table scan to retrieve the contents by ignoring secondary index. This is because the CBO (Oracle's cost-based SQL optimizer) calculates that the cost (time) required to perform the full table scan is less than the cost of retrieving the table contents using the secondary index.

In such scenarios we have to use ABAP hints along with the SELECT query. ABAP hints forces the CBO to consider the secondary index while retrieving the table contents. The syntax of ABAP Hint is as follows:

%_HINTS ORACLE 'INDEX("<Table Name>" "<Table Name>~<Index Id>")'

For example, suppose we have to retrieve the entries from MARA table based on Material Type (MTART). Since MTART is not a primary key, we will create a secondary index for this key. After creating the index we need to update the STATISTICS with the help of basis team so that it gets reflected in ORACLE database. Then in the SELECT statement we must use ABAP Hints.

SELECT * FROM MARA

                INTO IT_MARA

    WHERE MTART  EQ ‘COUP’

    %_HINTS ORACLE 'INDEX("MARA" "MARA~T")'.

Please note even if the syntax of Hints used with SELECT is incorrect, system will not throw any errors.

We can use the EXPLAIN tool even through the transaction ST04.

We can take the SQL traces even using T-codes ST05/STAD.

As highlighted in the screen shot above, the different operations performed while executing the SELECT statements are:

PREPARE: Indicates the conversion of ABAP SELECT statement into ORACLE SQL statement.

OPEN: A cursor (logical entity) is assigned to a SQL statement which acts as a tunnel between SAP and ORACLE to access the table contents by passing the parameters such as the table fields and the key used to access the table.

FETCH: Retrieves table entries from the database.

REOPEN: Specifies that the declared cursor is used again by passing different parameters.

EXEC: Denotes INSERT/DELETE/UPDATE statement is executed.

REEXEC: Indicates same INSERT/DELETE/UPDATE statement is executed again.

EXECSTA: Specifies COMMIT WORK is performed.