Enhanced DBA Cockpit: New Features for Monitoring and Analyzing SQL Statements

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