The EXPLAIN Function (SAP GUI-Based Version)

You can use this function to explain all SELECT, INSERT, UPDATE, or DELETE statements. The statements might contain optional comments such as --OPTLEVEL( <optlevel> ) -- QUERY_DEGREE(< query_degree> --LOCATION( <report> , <position> ). If no comments are specified, the statements are explained using the default <optlevel> and default <query_degree> for the work process.

Accessing the EXPLAIN

You can call the EXPLAIN function in the following ways:

Call the DBA Cockpit and choose Diagnostics EXPLAIN in the navigation frame of the DBA Cockpit. On the Diagnostics: EXPLAIN screen, enter an SQL statement manually and choose Explain.

Call the DBA Cockpit and choose Performance Applications in the navigation frame of the DBA Cockpit.

Call the DBA Cockpit and choose Performance SQL Cache in the navigation frame

of the DBA Cockpit.

Call the DBA Cockpit and choose Diagnostics Cumulative SQL Trace in the navigation frame of the DBA Cockpit.

Call transaction ST05 and choose Enter SQL statement. Enter an SQL statement manually and choose Explain.

If a statement cannot be explained, the ERROR: Check SQL Statement screen

appears providing a detailed error message and the possibility to modify the statement. To continue, choose Explain Again.

Call transaction ST05 and choose Trace list. Select one statement and choose Explain.

Displaying the Access Plan of a Statement

If a statement was explained successfully, the Display Execution Plan for SQL Statement screen appears, providing information on the SQL statement text, the OPTLEVEL and QUERY_DEGREE that was used to explain this statement, and the access plan.

The access plan generated by the DB2 optimizer is displayed as a tree structure. It consists of all database operations that will be performed when the statement is executed.

The estimated execution time is displayed in timerons (arbitrary IBM time unit). All operators are numbered, starting with zero. Operators can have the following extensions:

Extension Description

[O]/[I] Shows whether the operator acts as an outer/inner input branch for a subsequent join operation

(<Partition>) Shows on which partition this operation was performed

This is only displayed if you are using a multi-partition database. Non-volatile tables and indexes of non-volatile tables are displayed in blue. Volatile tables and indexes of volatile tables are displayed in orange.

For each index used in the access plan, the number of key columns, that is, index fields that were really used within the access plan, are displayed.

For further analysis of the displayed information, you can choose from various options in the application tool bar. For more information, see EXPLAIN Options [Page 47].

More Information

For additional information about the EXPLAIN function, see SAP Note 400938.

For more general information, see the IBM documentation: Administration Guide: Chapter 26,

SQL Explain Facility.

3.1.2.1 EXPLAIN Options

On the Display Execution Plan for SQL Statement screen, the following options are available:

Option Description

Details If no operator in the access plan is highlighted when choosing this option, a dialog box is displayed providing detailed

information on the statement and each operator. This output is similar to the one of the DB2 command line tool db2exfmt. For more information, see the IBM documentation

Administration Guide, Appendix I.

If operator no. 0 is highlighted, only the original statement and optimized statement are displayed in a separate dialog box. If any other operator is highlighted, the system displays detailed information on the selected operator only.

Optimizer The access plan may vary depending on the optimizer

parameters specified. When you choose this button, the Change

Query Optimization dialog box appears where you can change

the parameters OPTIMIZER LEVEL, QUERY DEGREE and the flag VOLATILE for the tables used in the access plan. To explain the statement with new parameters, choose the Explain

Again pushbutton.

DB Catalog With this option, you can display system catalog information on tables and indexes that are shown in the access plan. The following information is displayed:

For a table:

Selected information from table SYSCAT.TABLES is displayed. Additionally, all indexes of the table are displayed with their index columns.

For an index:

Selected information from table SYSCAT.INDEXES for this index is displayed. Additionally, selected information from table SYSCAT.COLUMNS is displayed for all index columns.

Depending on whether you have selected a table or an index, the following pushbuttons are available:

Table

Displays selected information from table SYSCAT.TABLES

Additionally, all indexes of the table are displayed with their index columns.

Columns

Displays selected information from table SYSCAT.COLUMNS for all table columns Indexes

Displays information from table SYSCAT.INDEXES for all indexes of the table as well as information from table SYSCAT.COLUMNS for all index columns

Update Statistics

Updates the catalog statistics for the table

If the catalog statistics were updated successfully, the field <stats-time> is displayed in green.

Dictionary With this option, you can display the ABAP Dictionary structure (definition) of a table by selecting the table in the access plan.

If you do not select a table in the access plan, the ABAP Dictionary structure (definition) of the first dictionary object of the SQL statement is displayed.

With this option, you can display the structure of views, even though views never appear in the access plan.

This function is not available for systems monitored using a remote database connection.

Test Execution This option is only available, if a:

SELECT statement is explained using transaction ST05 Trace list, the parameter values for all parameter markers of the statement are provided and the operation is other than PREPARE

SELECT statement without parameter markers is explained

When you use the EXPLAIN function, the entered SQL statement is only prepared and the access plan of the optimizer is chosen because of the system catalog statistics. On the basis of this information the optimizer estimates the costs for the execution of this statement.

However, the estimated costs may not correspond to the real execution time. Reasons for this might be bad statistics, a bad database layout or problems of the optimizer itself.

The Test Execution option measures the real execution time and provides other snapshot data, such as the number of buffer pool accesses and sorts for the selected statement. When the statement is executed, the parameter markers are replaced by the actual parameter values. A dialog box appears where you can change these values to investigate the

The result of several test executions of the same statement can vary because, for example, the buffer pool may already contain data that is necessary for the execution.

This function is not available for systems monitored using a remote database connection.

Tree Info The following additional information is displayed or hidden: num_rows

Estimated number of rows (result set) tot_cost

Estimated total cost for this statement i/o_cost

Estimated I/O cost of the statement

This information is also included in the output information when you choose Details.

Edit When choosing this option, the system switches to an editor window in which you can modify the selected SQL statement and explain it again.

Source This option is only available when the statement contains a LOCATION comment, for example, when you call EXPLAIN using transaction ST05 Trace list.

The location of the statement in the ABAP source code is displayed in a separate window.

This function is only available for the local system or ABAP systems for which an additional RFC destination has been assigned.

The Collect function is no longer available. To collect EXPLAIN data, use the db2support command line tool.