Explaining the explain plan

[former_member192616]

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.

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.