Explain Everything in the DBA Cockpit

The DBA Cockpit is a monitoring tool that is available as part of every SAP ABAP-based system. Part of this tool is an easy-to-use EXPLAIN function, which was recently enhanced with a few new features:

• Explain from section (also called explain from cache)
• Explain from activity
• Download of the execution plan in db2exfmt format
• Collection of section actuals while calling db2support

Let me show you how they work – you might find them useful for your own issue analysis of poor performing SQL statements and when you need to send monitoring data to SAP support.

The EXPLAIN Function in the DBA Cockpit

In the DBA Cockpit, you can use the EXPLAIN function to determine the execution plan for all SELECT, INSERT, UPDATE, or DELETE statements. How do you get there? Well, in any SAP system based on SAP NetWeaver, simply call up the DBA Cockpit using transaction DBACOCKPIT. In the DBA Cockpit, you have different options to get to the EXPLAIN function: For example, from the navigation frame, you can choose Diagnostics >  EXPLAIN. On the following Diagnostics: EXPLAIN screen, enter an SQL statement, and choose Explain. You can also navigate to the EXPLAIN function from screens where SQL statements are displayed: Performance > Top SQL Statements, Performance > SQL Cache, Performance > Applications. In addition to the DBA Cockpit, you can also use other transactions to run an EXPLAIN, for example, Performance Trace (transaction ST05, ST05_OLD) or Single Transaction Analysis (transaction  ST12).

If an SQL statement was explained successfully, an execution plan is shown in the DBA Cockpit. The execution plan is displayed as a tree structure. This structure consists of all database objects and operations that are performed when the statement is executed.

This EXPLAIN function has been available for a quite a while, so now let’s turn to a bit of background and then to the new features.

Background

How does EXPLAIN work? To execute an SQL statement, the Db2 optimizer first needs to prepare the execution plan. The execution plan basically describes the database objects needed and operations performed to execute the SQL statement. The execution plan is stored in the SQL cache. In the Db2 database, the following Db2 functions are available for analyzing execution plans:

• EXPLAIN_FROM_SECTION analyzes the execution plan that was already prepared and stored in the SQL cache.
• EXPLAIN takes the SQL statement as input and determines the execution plan fresh.
• EXPLAIN_FROM_ACTIVITY analyzes the execution plan of SQL statements that violate a WLM threshold (for example, rows read) during the execution of an SQL statement.

All functions have in common that they store the description of the execution plan into the so-called explain tables. These explain tables are read by the DBA Cockpit or the Db2 utility db2exfmt, which can then display the execution plans.

Previously, the DBA Cockpit only used the EXPLAIN Db2 function, which takes the SQL statement as input and determines the execution plan fresh. However, there was no functionality in the DBA Cockpit that displayed the results of the Db2 functions EXPLAIN_FROM_SECTION or EXPLAIN_FROM_ACTIVITY as execution plans on the UI. This has changed now with new SAP Notes and support packages. What’s more, there are more possibilities to download execution plans from the DBA Cockpit.

Now let’s turn to these new features.

Overview of New Features

Here’s an overview of how the new features in the DBA Cockpit work together:

With SAP Note 3049243, you can now also use the DBA Cockpit to display the execution plan of  a prepared SQL statement out of the SQL cache (EXPLAIN_FROM_SECTION, no. 1 in the graphic). With SAP Note 3094083, you can now also display the execution plan of an SQL statement that violated a WLM threshold (EXPLAIN_FROM_ACTIVITY, no. 2 in the graphic). Explained SQL statements are shown on the DBA Cockpit explain screen (3).

The DBA Cockpit explain screen is a perfect way to interactively check and analyze the execution plan of an SQL statement. However, sometimes you might want to download an execution plan from the DBA Cockpit, for example, to send it to SAP support. A download is also convenient if you want to compare multiple execution plans using an external diff tool.

This leads us to two more new functions in the DBA Cockpit: First, with SAP Note 3038068, you can now export the SQL execution plan as it’s shown on the DBA Cockpit explain screen with all details in Db2 explain format into a file (no. 4 on the graphic).  Second, there’s the db2support function in the DBA Cockpit (5) that you can use to collect and afterwards download all required information for SAP support into a so-called db2support.zip file. This file is then used by Db2 support to recreate and to analyze a problematic SQL statement. The db2support download function is not new, but now, with SAP Note 3019155, the db2support download also contains section actuals, which I will explain later.

In the following, I will go into details on how to call up and use the new features in the DBA Cockpit.

Explain from Section/Cache (1)

To display the execution plan of an already prepared SQL statement from the SQL cache, proceed as follows: In the DBA Cockpit, choose Performance -> SQL Cache, double-click an SQL statement and choose the new button EXPLAIN from Cache.

Note that the button in the DBA Cockpit is called EXPLAIN from Cache to reflect the fact that the execution plan is read from the SQL cache. What’s technically behind it, though, is the Db2 function called EXPLAIN_FROM_SECTION, which I’ve described earlier.

Explain from Activity (2)

Remember, the Db2 function EXPLAIN_FROM_ACTIVITY analyzes the execution plan of SQL statements that violate a WLM threshold. Previously, you could display a list of SQL statements that violate WLM thresholds in the DBA Cockpit, but you could not display their execution plans using EXPLAIN_FROM_ACTIVITY. This has changed now, and, what’s more, these execution plans can now also contain section actuals.

Before I describe how to use explain from activity in the DBA Cockpit in detail, let me explain what section actuals are.

During the preparation of the execution plan, the Db2 optimizer estimates the cardinality (number of rows) for each database object and each operation, for example, a JOIN operation between two tables. The estimation is based on statistics, for example, data distribution statistics. If the optimizer wrongly estimates the cardinality, the access plan might be suboptimal. This is where section actuals come into play.

Section actuals are runtime statistics that are collected during the execution of an SQL statement. Selection actuals contain, for example, the actual number of rows for each database object and each operation in the execution plan. This means that you are then able to compare the estimated number of rows with the actual number of rows and find areas in the execution plan with misestimated cardinalities.

The Db2 function EXPLAIN_FROM_ACTIVITY also gets section actuals and stores them in the explain tables.

Now let’s have a look at the DBA Cockpit and how you can display execution plans of SQL statements that violate a WLM threshold, including their section actuals.

In the DBA Cockpit, choose Workload Management -> Threshold Configuration and create a new threshold. You can choose the threshold from a dropdown list, for example, rows returned for query.

For the selected threshold, enter the value for the threshold and choose the extent of data that is collected if the threshold is violated. In the field Data Collection upon Threshold Violation, choose Activity Data With Statements, Bind Values and Sections because this triggers the collection of section actuals such as the actual number of rows.

After you have saved the new threshold, execute your SAP workload.  Then, in the DBA Cockpit, choose Workload Management -> Threshold Violation and check for violations that are displayed. Here’s an example:

To display the execution plan of the SQL statement that violated the threshold, select an entry in the table and choose the new button EXPLAIN from Activity. The DBA Cockpit displays the execution plan of the SQL statement including its section actuals.

db2exfmt Download (3)

Let’s have a look at another new feature, the db2exfmt download. You can download an explained SQL statement in Db2 explain format. On the DBA Cockpit explain screen, from the SAP GUI menu, choose Goto > Download Plan from db2exfmt.

You’ll get a download of the execution plan created with IBM db2exfmt:

If you compare the DBA Cockpit explain screen and the db2exfmt output, you will notice that both show more or less the same data, but in a different format.

db2support with Section Actuals (4)

The db2support feature in the DBA Cockpit has been available for a while; it’s used to collect all kind of information regarding a particular SQL statement. The main purpose is to send the output to Db2 support in the case of performance problems. As of Db2 11.5.6, the db2support feature, as it’s integrated in the DBA Cockpit, can also collect section actuals. On the DBA Cockpit explain screen, you can find a Collect button that you can use to collect data for SAP support. This data collection has now been enhanced to capture a formatted execution plan output with section actuals for an SQL statement.

How do you get this data? Choose the Collect button on the EXPLAIN screen of the DBA Cockpit:

On the following popup Collect db2support Data, select the checkbox Collect Section Actuals:

As a result, in the background, the db2support command plus a subsequent db2caem command is run to execute the SQL statement and to capture section actuals for the SQL statement. The formatted output with actuals is contained in db2support.zip as file db2caem.exfmt.1 in the following directory structure: db2supp_opt.zip -> OPTIMIZER -> DB2CAEM -> DB2CAEM<timestamp>.

The formatted output looks like this:

Note: This output contains section actuals (the actual number of rows, highlighted in yellow).

Functional Overview

Here’s an overview of the various methods and options that I’ve shown to you:

Execution Plans on the DBA Cockpit UI:

Execution Plan Downloads from the DBA Cockpit:

Conclusion

I hope I have shown you how useful the EXPLAIN functions in the DBA Cockpit are. With the new features, you can now easily download access plans for your own analysis and enhanced Db2 statistical information for SAP support. Furthermore, you can now view execution plans for already prepared SQL statements from the SQL cache (EXPLAIN_FROM_SECTION) and executions plans of SQL statements that violate a WLM threshold (EXPLAIN_FROM_ACTIVITY). You can get these new features with the following SAP Notes or with the support packages mentioned there:

• 3049243 – DB6: DBA Cockpit: Support of EXPLAIN-from-Cache on Screens “SQL Cache”, “Top SQL Statements” and “Applications“
• 3094083 – DB6: DBA Cockpit: Support of EXPLAIN-from-Activity for Threshold Violations Recorded by Workload Management
• 3019155 – DB6: DBA Cockpit: SAP GUI EXPLAIN Extension for Collecting Additional Db2 Monitoring and Statistical Information from db2caem
• 3038068 – DB6: DBA Cockpit: Download Access Plan Output of db2exfmt in SAP GUI EXPLAIN

Finally, I’d like to thank everyone who contributed to this blogpost, especially my SAP colleagues Sabine Reich and Oliver Brein.