Show TOC

(Command Monitor)Locate this document in the navigation structure

Use

The SQL Performance user menu of the DBA Cockpit contains the SQL command monitor. Use this tool if the analysis of the database bottlenecks ( Bottlenecks) detected inefficient database access. This enables you to target long-running SQL statements.

For performance reasons, you should only use the tool for a short-term analysis. You can specify criteria to restrict the volume and type of SQL statements that are recorded.

Prerequisites

Prerequisites

During normal operation of the database system, the SQL command monitor is deactivated.

Procedure

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

Note

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

Result

Current Monitor Status

The status of the SQL command 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.

  • Monitoring Active Since: You can see when the SQL command monitor was activated. This information appears only if the SQL command monitor is active.

  • Additional status information may also be displayed.

Recording Criteria

You can set recording criteria to determine which SQL statements are to be recorded.

To activate the SQL command monitor shortly before a transaction that is to be analyzed is started, follow the procedure below:

  1. Choose Change Values.

  2. Enter the desired recording criteria. The recording criteria determine how SQL statements are to be logged in the SQL command monitor tables.

    Number of Pages Read

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

    SQL Statement Runtime in ms

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

    Selectivity in %

    An SQL statement is logged if the ratio of qualified records to read records falls below the specified percentage.

    SAP provides you with defaults that you can confirm or change. If you want to use the SAP system defaults, choose Enter Default Values. You can Delete All Values or Cancel Entry.

  3. If you want to activate the SQL command monitor with the recording criteria you have defined, choose Activate Values.

Deactivate the SQL command monitor as soon as the transaction you want to analyze ends. To do this, choose Deactivate Monitoring.

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

Display Criterion

Since the number of recorded SQL statements can be very large, you can restrict the number displayed by setting an output criterion. To do this, under Displayed Statements (Display starts with most runtime-intensive statement) , choose the number of SQL statements to be displayed.

Recorded SQL Statements

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

To obtain additional information about an SQL statement, double-click the SQL statement. You can see the complete SQL statement and additional analyses in the following view.

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

  • Replacing Placeholders in the SQL Statement: You can replace placeholders in an SQL statement.

  • EXPLAIN for SQL Instruction: The system switches to the SELECT Editor with the EXPLAIN statement specified for this 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 statement back to that program.

To refresh the display the recorded SQL statements, choose Refresh (F5).

Initializing the SQL Command Monitor Tables

The SQL statements that meet the recording criteria and the values specified in their WHEREclauses are recorded in the SQL command monitor tables (among others, the system table COMMANDMONITOR), and are executed in the database system after the SQL command monitor has been started. Deactivating the SQL command monitor does not initialize the SQL command monitor tables.

To initialize the SQL command monitor tables in the SQL command monitor output area, choose Initialize Monitor Tables.

More Information