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.
We can take the SQL traces even using T-codes ST05/STAD.
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.
