Show TOC

Resource MonitorLocate this document in the navigation structure

Use

The SQL Performance user menu of the DBA Cockpit contains the SQL resource monitor. By analyzing SQL resource consumption, you can identify the most costly SQL statements. The resources used by an SQL statement are measured (runtime and I/O accesses, for example).

If an SQL statement is used more than once, the total cost is calculated. This enables you to identify those SQL statements that have a relatively short runtime but cause a considerable database load due to the number of times they are executed. The SQL resource monitor is therefore a monitoring tool that can be used for load analysis of one workday, for example.

Prerequisites

Prerequisites

The SQL resource monitor is always started. The SQL resource monitor tables (among others, the system table COMMANDSTATISTICS) contain the SQL statements and the statistical details that the SQL resource monitor records when an SQL statement is executed.

Procedure

Open the DBA Cockpit (transaction DBACOCKPIT) and choose Start of the navigation path Performance Next navigation step SQL Performance Next navigation step Resource Monitor End of the navigation path.

Note

For SAP NetWeaver 7.1 , this status information is displayed in the Resource Monitor menu, which is different from the menu described below.

Result

Current Monitor Status

The status of the SQL resource monitor is shown in the Current Monitor Status area.

Last Update: You can see when the display of the recorded SQL statements was last updated.

Output Criteria

Since the number of recorded SQL statements can be very large, you can restrict the display by setting output criteria.

Data Since

Restart: The SQL statements since the last restart of the database system are displayed.

Last Reset: The SQL statements since the last reset of the SQL resource monitor tables are displayed.

Number of Rows Read

An SQL statement is logged if the specified number of read rows is exceeded.

Number of Executions

An SQL statement is logged if the specified number of executions is exceeded.

Runtime in ms

An SQL statement is displayed if the specified runtime is exceeded.

Number of statements (display of the <n> statements with the longest runtime)

Since the number of recorded SQL statements can be very large, you can restrict the number displayed by setting this output criterion.

To output the results in the SQL resource monitor, choose Refresh Monitor Output.

Recorded SQL Statements

The SQL statements that match the desired output and display criteria are displayed.

To obtain more information about an SQL statement, double-click the SQL statement or choose Details . You can see the complete SQL statement and additional analyses for the SQL statement in the following view:

  • Tables/View Information: The Tables/Views/Synonyms display appears.

  • EXPLAIN for SQL Statement: The system switches to the SELECT Editor with the EXPLAIN statement specified for this SQL statement. The EXPLAIN statement is only displayed if no parameter placeholders are specified in the SQL statement.

    Among other things, this displays the search strategy that the SQL optimizer would select to process this SQL statement.

  • Save as Local File: You can copy the output to the clipboard or print it directly.

  • Display Callpoint in ABAP Program (only for the OLTP database that serves as the basis for the current SAP Web AS system): If a recorded SQL statement was called from an ABAP program, you can trace the SQL statement back to that program.

To initialize the SQL resource monitor tables, in the SQL resource monitor, choose Reset Counter.

More Information