AMDP Trace with SQL Script Compiler Optimizer Plan
In one of the last post AMDP Utility Reports shows the list of reports that can be used for AMDP procedure tracing and in this post will make the AMDP trace with the report- RS_AMDP_STMT_TRACE.
Below we have a simple AMDP procedure with three block and each block performs the same operations and finally gives back 5 records from the SPFLI table.
The first and second section are the same though there are 1 line in section one and 2 lines in the section 2.
During the execution of the section 2, the SQL Script compiler, combines these two statements into 1 ( they have common table expression) to optimize it and then executes it. Now this shows that we can have a complex query with nested sub-queries which is not good at the reader’s perspective but the same query can be divided into simple queries with common table expression which is good at reader’s perspective but yet the result and the performance are same.
In the third section just used – WITH HINT( NO_INLINE ) which is a directive to the compiler that don’t combine statements into one though they have common table expression.
Below use case is just to know how to make use of it WITH HINT( NO_INLINE ) but the use case taken here is not the ideal condition.
WITH HINT( NO_INLINE ) can be used where multiple statements have the common table expression and the sql script compiler combines it to optimize it but that leads to a non-optimal plan performance.
Let’s use the below report to trace the execution of AMPD Proceure.
Let’s consider the first section of the method to be traced.
We have the report that calls the AMDP procedure.
Execute the report-RS_AMDP_STMT_TRACE , Activate the trace and execute the report and deactivate the trace.
First trace file.
Similarly trace other two sections of the method and finally we have 3 trace files to be compared.
Below here we have 3 trace files and let’s analyze one by one.
Trace File for Section-1 . Double click on the line highlighted under “STATEMENT” column.
The single Query with mandt and carrid = “LH’
Trace File for Section-2. Double click on the line highlighted under “STATEMENT” column
Here the two select queries are combined into one query by the compiler as a query optimizer plan.
Trace File for Section-3. Double click on the line highlighted under “STATEMENT” column.
Here we have two queries are performed as we have provided the directive WITH HINT( NO_INLINE )
The first query execution.
Second Query execution
The execution time of the first section and the second section are nearly same but the execution time of third section is larger. I think this post at least helps to understand the use of SQL Script Compiler directive WITH HINT( NO_INLINE ) and in future will try to post something with a real time example of the proper use case.
SAPCODES 