This blog is a co-operation with my colleague Jan Kritter from the DB6 performance team. We will explain the "explain (plan)" function for DB6 in the SAP environment. Furthermore the basic options in the execution plan view will be explained.
You may want to read the database independent introduction in explain plans first:
Where to find and how to use the explain plan in DB6
Besides the Explain in the ST05 SQL trace (discussed in the above mentioned blog) there are other places where we can find the Explain in DB6.
ST05:
The "Enter SQL Statement" (shown in the above mentioned blog) option in ST05 is database dependent. We have to use native SQL here. For DB6 the parameter markers are question marks. A typical statement that you can explain here looks like this:
SELECT * FROM USR02 WHERE MANDT = ? AND BNAME = ?
Don't forget to include the CLIENT/MANDT field if you "translate" an OPEN SQL Statement to a native SQL statement. The client is added automatically by the database interface (DBI), so you should add CLIENT / MANDT here.
ST04:
In ST04 you can find the Explain in the following places:
- - Performance - Applications: double-click an executing agent (Appl. status = UOW executing) - on the "Statement Text" tab click explain (bullet 1 in figure 1)
- - Performance - SQL cache : click explain or double-click a statement (bullet 2 in figure 1)
- - Diagnostics : Explain - Enter native SQL - click explain (bullet 3 in figure 1)
- - Diagnostics: New Explain - Enter native SQL - click explain (bullet 4 in figure 1) - The New Explain offers a graphic execution plan.
Figure 1 - Explain plan in ST04
Note: In each of these places the execution plan is an estimated execution plan. This means the current environment (e.g. parameter settings, statistics, hardware characteristics) is used to build the plan. By the way, the execution plan and access plan are often used as synonyms. Strictly speaking, "explain" is the DB6 function, which is applied to an explainable SQL statement during compilation time
We will continue with the classic Explain here, leaving out the new explain for the time being, in order to describe the basic option in the execution plan view of DB6.
Options in the execution plan view
After clicking "explain" you will see a screen like in figure 2.
Figure 2: Display Execution Plan for SQL Statement in DB6
In the following section we describe the options for the execution plan. The list also contains a very brief definition of the different functionalities, further explanations will be supplied in later blogs.
- Details (bullet.1 in figure 2): Shows the access details for each step in the execution plan, such as filter factors or sargable predicates.
- Snapshot (bullet.2 in figure 2): Shows the detailed table, index and column statistics.
- Optimizer (bullet 3 in figure 2): Allows changing the optimization level and the specification of optimizer guidelines. With this option different parameters can be passed to the optimizer.
- DB Catalog (bullet 4 in figure 2): Shows the basic table, column and index statistics.
- Dictionary (bullet 5 in figure 2): Navigates to the SAP data dictionary (SE12) for the table.
- Test Execute (bullet 6 in figure 2): Exists only for SQL (SELECT) with actual values not parameter markers, not for DML (UPDATE, ...). Executes the SELECT statement and collects run time data like index- and data pages read.
- Edit (bullet 7 in figure 2): Allows changing the statement text and getting a new execution plan with the "Explain Again" button.
- Source (bullet 8 in figure 2): Navigates to the ABAP source code (SE80, ...) that has called the statement.
- Collect (bullet 9 in figure 2): Collects all relevant information (such as statement text, access plan, statistics, ...) in case of problems where an execution plan needs to be sent to SAP support.
- Values (bullet A in figure 2): Substitutes the parameter markers with the values (if available - works for SQL traces only) and does a new explain plan. After that the button changes to "Markers" which reverts back to parameter markers.
- "Full Screen" (bullet B in figure 2): Hides or Displays the left part of the screen
- "DB6 in SDN" (bullet 0 in figure 2): Opens the "SAP on DB2 for Linux, UNIX, and Windows" page in SDN where you can find this blog ;-).
Note: In older SAP releases there might be an additional option called "Tree Info" which displays or hides the columns num_rows, tot_cost, io_cost for each step of the execution plan. In the current releases these columns are always shown.
Some of the options will be explained in more detail in future blogs where we will describe more on how to read and tune DB6 execution plans in detail.
We finish with a description of what is visible in right part of the Execution Plan view in figure 2.
Execution Plan view
The SQL Statement Section (bullet C in figure 2) shows the native SQL statement. This is what the DBI made out of the OPEN SQL statement. In case the OPEN SQL used a FOR ALL ENTRIES (FAE) or a pool or clustered table the native SQL statement might look very different from the OPEN SQL statement.
In the Access Plan line (bullet D in figure 2) we see the current level of optimization and parallelism that was used for this execution plan.
The execution plan itself is in the lower part of this screen (bullet E in figure 2). We plan to write more detailed blogs about the execution plan itself but here is already a quick overview of what we can see in our example.
The Execution Plan
Her we see a collection of operators that are organized in a tree-like structure. This structure describes the execution flow of the statement. Starting from top to bottom the first operation that has no child usually marks the first step in the execution plan. In our case in figure 2 this is the line:
3 IXSCAN USR02~0 #key columns: 2 1.0000E+00 2.3010E-02 0.0000E+00
This step indicates that we start with an index scan on index USR02~0 with 2 key columns and the associated estimated rows and costs (time and I/O) for this line.
The next step is the table access and the estimated rows and costs (time and I/O) for this line:
2 FETCH USR02 1.0000E+00 7.8638E+00 1.0367E+00
After fetching, the rows are returned to the application (ABAP program) in this step:
1 RETURN 1.0000E+00 7.8638E+00 1.0367E+00
The root node indicates the type of the statement (SELECT, INSERT, ...) and the total cost (time) for this execution plan
0 SELECT STATEMENT ( Estimated Costs = 7.864E+00 [timerons] ) num_rows tot_cost i/o_cost
This blog explained the basic option of explain plan in DB6. We will see more specific and detailed examples in future blogs.
Jan Kritter is Technical Support Consultant for SAP AGS (SAP Active Global Support)
- SAP Managed Tags:
1 Comment
| User | Count |
|---|---|
| 5 | |
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 |
