Procedure documentationCommand Monitor Locate this document in the navigation structure

 

The SQL Performance user menu contains, among other things, 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.

This tool is intended for short analyses, since it records a limited number of SQL statements. You can specify criteria to restrict the volume and type of SQL statements that are recorded.

Prerequisites

Prerequisites

Only for the OLTP database that is the basis of the current SAP Web AS system: To ensure that the name of the ABAP program and other information can be sent to the database, instance profile parameter dbs/ada/register_appl_info=1 must be set.

Procedure

Open the Database Assistant (transaction DB50) or the liveCache Assistant (transaction LC10). Choose   Troubleshooting   SQL Performance   Command Monitor  .

or

Open the DBA Cockpit (transaction DBACOCKPIT) and select   Performance   SQL Performance   Command Monitor  .

Result

Activating or Deactivating the SQL Command Monitor

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

  1. Choose   Command Monitor   Change Monitor Settings  

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

    Recording Criteria

    Number of page accesses

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

    SQL statement runtime

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

    Selectivity

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

    Further Setting

    Max. number of monitor entries

    This value determines the maximum number of entries that are held in table SYSMONITOR before the table is overwritten cyclically.

    SAP provides you with defaults that you can confirm or change. If you want to confirm the SAP system defaults, choose Copy SAP Default Setting in the Change Monitor Settings display.

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

During normal operation of the database system, the SQL command monitor is deactivated. The status of the SQL command monitor is displayed in the Current Monitor Settings area and can be refreshed using   Command Monitor   Refresh Monitor Settings  .

Deactivate the SQL command monitor as soon as the transaction you want to analyze ends.

Choose   Command Monitor   Change Monitor Settings   and then Exit SQL Monitoring.

Output of Results

To display the SQL statements logged according to the recording criteria selected, choose   Command Monitor   Refresh Monitor Output   in the SQL command monitor.

Displaying SQL Statements

If a logged SQL statement was called from an ABAP program, you can trace the statement back to that program. To do this, select the SQL statement and choose Display Call in ABAP Program (only for the OLTP database instance that is the basis of the current SAP Web AS system).

To obtain additional information about an SQL statement, double-click the SQL statement or choose Display SQL Statement. You can see the complete SQL statement in the following view. Additional analyses are possible for SQL statements:

  • Display/Trace Execution Plan for an SQL Statement:

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

    If requested by Support, you may need to create an SQL Optimizer trace.

    Choose Explain with Hint if you want to test the effect of different indexes on the execution plan of an SQL statement.

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

  • Print Version: You can copy the output to the clipboard or print it directly.

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

  • Display Callpoint in ABAP Program: (only for the OLTP database instance that is the basis of the current SAP Web AS system)

  • Information on the ABAP Callpoint: (only for the OLTP database instance that is the basis of the current SAP Web AS system)

Initializing the Command Monitor Tables

The SQL statements and the values specified in their WHERE clauses are recorded in the SYSMONITOR and SYSMONDATA tables, provided these statements meet the recording criteria, and are executed in the database system after the SQL command monitor has been started. These tables are overwritten cyclically. Deactivating the command monitor does not initialize the SYSMONITOR and SYSMONDATA tables.

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

More Information

Resource Monitor

Concepts of the Database System, Search strategies, List of all Search Strategies, EXPLAIN Statement