Skip to Content

Before we start we should talk about some terms first.

Explain plan:

A tool or function provided by the DBMS (Database Management System) that makes an execution plan visible.

Execution plan:

A parsed, optimized, compiled and executable SQL statement stored in the so-called SQL cache of the database. Using the explain plan the SQL execution flow of this plan can be made visible textually or graphically. The execution plan shows the sequence of the detailed steps that are necessary to execute an SQL statement.

Database names:

For the various databases that are supported by SAP there are often different names used. The following matrix should help you to get an overview:

Vendor Name

SAP name

SY-DBSYS in ABAP

IBM DB2 for z Series

DB2

DB2

IBM DB2 for i Series

DB4 / DB400

DB400

IBM DB2 for LUW (Linux, Unix, Windows)

DB6

DB6

Microsoft SQL Server

MSS / MSSQL

MSSQL

SAP MaxDB

MaxDB

ADABAS D

ORACLE

ORACLE

ORACLE

We will use the SAP names here.

Content of the execution plan

Within SAP you can find the explain plan in two important transactions: ST05 and ST04.

It is important to understand what you can see in these execution plans. For the same SQL statement the content of the execution plan might be different in these 2 transactions depending on the database platform.

We have to distinguish between an “estimated plan” and the “actual plan”. The main difference between these two is that they tell you how a statement will (probably) be executed, or how a statement actually was executed.

“Estimated execution plan”:

The estimated execution plan will show you how a statement will (probably) be executed. This means a given SQL or DML statement will we reparsed and re-optimized considering the current environment (parameters, statistics, indexes, settings, etc.) and this newly built execution plan will be shown.

“Actual execution plan”:

The actual execution plan will show you how a statement actually was executed. This means for a given SQL or DML statement the execution plan that was used for the last execution will be shown.

The following matrix shows what type of execution plan you can see in which transaction for the different databases:

Database (SAP name)

ST05

ST04

DB2

estimated execution plan OR

actual execution plan

estimated execution plan OR

actual execution plan

DB4 / DB400

estimated execution plan

actual execution plan

DB6

estimated execution plan

estimated execution plan

MSS / MSSQL

estimated execution plan

estimated execution plan OR

actual execution plan

MaxDB

estimated execution plan

estimated execution plan

ORACLE

estimated execution plan

estimated execution plan OR

actual execution plan

This matrix shows the current state in SAP release 7.0 with the latest database version available. However it is subject to change with new SAP releases and/or database versions.

In many cases the estimated and the actual execution plans are the sam but there are some database dependent reasons why the estimated and the actual plan might be different. Therefore this should be discussed with the database specific execution plans.

Getting the execution plan with ST05

Since ST04 is database dependent you will find the explain plan in various places within the tool. This is not discussed further here. We will finish with the description of how you can get an execution plan in ST05:

Once you have created a trace with ST05 you can obtain the execution plan with the explain plan function in the trace list or the statement summary like it is shown in figure 1 with numbers 1 and 2.

Note: In the trace list the explain function (number 1 in figure 1) is only possible on lines where the operation (Op. column) shows OPEN or REOPEN. On some databases e.g. DB2 there are more valid positions (e.g. PREPARE, DECLARE)

Another option in ST05 is to enter a SQL Statement (Button “Enter SQL Statement” in the main screen in ST05) manually. If you want to use this function you have to use native SQL, which is database dependent. This option is not discussed here but might be included in future database specific blogs.

Explain plan in ST05

Figure 1: Explain plan in ST05

After clicking on explain you can seethe execution plan, which again is database dependent. Therefore we will not go into the details here but maybe in future database dependent blogs.

Generally you can get the following information in the execution plan:

  • – detailed steps that are necessary to execute an SQL statement such as an index scan or a full table scan or the join method used
  • – the order of these steps which is decided by the optimizer
  • – i/o and/or cpu cost and cardinality estimations for each step
  • – additional information on how the predicates are used

 

In this blog we described what the explain plan function does and introduced its result, the execution plan. We distinguished two types of execution plans, the estimated execution plan and the actual execution plan and pointed out what you can see in ST04 and ST05 for the different database platforms. Furthermore we have seen where we can find the explain plan in ST05. Future blogs will continue with basic database dependent information (e.g. explain plan in ST04) and a description of examples for typical execution plans. This information will be crucial for SQL statement analysis and tuning.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Senthil Nattamai Sekar
    Hermann , Thanks for sharing this information . But i have question you – How do I say the cost of the data selection is high ? , I have seen some cases where the INDEX search’s cost is more than the actual data selection cost due to the wrong selection condition fields. Still in many cases I could not able to determine whether the cost is actually high or low or it okay.

    Any standard way to determine this ?
    Will DBA’s only have more knowledge on this ?

    (0) 
    1. Hermann Gahm Post author
      Hi,

      we often hear people saying that a SQL is “expensive” sometimes because the estimated costs are high. If or how this costs are derived is database dependent. On ORACLE e.g. the cost expresses the estimated amount of work to be done to execute the SQL. So the estimated nr. of physical I/Os and the type of the I/Os as well as the estimated CPU cycles needed are taken into account. However, all these estimations are based on the statistics. So estimated costs are just an assumption. What matters is the response time, istn’t it? E.g. how long does it take to read one row?

      To answer you question: No, i’m not aware of any standard KPIs reg. cost. I suggest to focus on response time.

      (0) 
  2. Praneet sai
    Herman,

    Thanks for your initiation at this topic.

    If the CPU cost is not useful, why SAP is displaying when you click on EXplain SQL.

    I think some meaning would be there for this CPU cost to evaluate query performance.

    (0) 
    1. Hermann Gahm Post author
      Hi,

      i didn’t mean it is not useful. One can use the cost estimation to understand why the optimizer prefers something over something else however than the details of the cost estimation process has to be known as well. The cost should reflect the time as well but this doesn’t work always since it depends on several things (satistics, assumptions, …). I use the cost sometimes when i try to understand optimizer decissions. But talking about query performance i start with time, not with cost. The estimated costs are not necessarily the real costs. You can see the real costs after execution as time or with database specific monitors (e.g. buffer gets,…)

      Kind regards,

      Hermann

      (0) 
  3. Manish Garg

    Hi Hermann,

    What is the difference between the Execution plan cost and Open statement duration. I have observed that when I modify something in the Select statment in abap program, my execution plan remains same with values in terms of estimate cost, number or rows and I/O cost but duration of the OPEN statement keep on changing. It’s very difficult to say  now that performance of program is improved or not based on the explain plain analysis.

    Please help me in understanding the facts about the OPEN statement duration. On which parameters this duration varies if execution plan remain same and where we do we can do analysis of thos parameters.

    Thanks,

    Manish

    (0) 
    1. Hermann Gahm Post author

      Hi,

      it depends  on the DB platform. On some platforms the OPEN includes the first fetch. Then it would most likely be influenced by caching. You need to look in the shared cursor cache and the KPIs there to find the reason for the time diff.

      Kind regards,

      Hermann

      (0) 
      1. Manish Garg

        Thanks for your reply. I have checked the details as you said but still not able to make clear picture on the OPEN statement duration.

        Only, the point which i found from the system that Database request time is varying a lot , which in result is increasing the response time. This I have observed from the STAD records. But I am not sure, if Database request time is used in calculation of the open statement duration also?

        Also,I guess STAD database request time is shown for complete program . What I mean here it’s let say if i have Z program which have ten select statments then database request time shown in STAD is for ten select queries but open statement duration is based on each table. So, I don’t know , how to see or calculate , database request time is used for each table?

        Secondly I have tried to check the expensive SQL statments and under SQL cache, I found the SQL query but it’s showing Buffer gets/Row processed as zero and Buffer quality as 98.65% but in opposite to this my Open statement duration in ST05 is still huge for that query.

        Could you please help me in understanding above points. Also I want to know, how system shows the sample type as “Slowest Execution” or “Fastest Execution”. Currently for this query, I can see , system is showing as “Slowest Execution”.

        We are having the database DB2 ver9.7 Fix Patch 5 .

        Thanks,

        Manish

        (0) 
        1. Hermann Gahm Post author

          Hi Manisch,

          lot’s of questions and i think this is not the correct place to diskus them.

          If you send me an email with your analysis i can comment on that.

          Kind regards,

          Hermann

          (0) 

Leave a Reply