Command Monitor 

The SQL Performance menu contains, among other things, the command monitor.Use this tool if the analysis of the database 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.  Specify criteria to restrict the volume and type of SQL statements that are recorded.

Prerequisite

Prerequisites

So that the name of the ABAP program and other information can be sent to the database instance, the instance profile parameter dbs/ada/register_appl_info=1 must be set.

Procedure

...

       1.      In the user menu, choose Database: Monitoring ® Database Assistant (transaction DB50).

       2.      Choose Problem Analysis ® SQL Locks ® Command Monitor.

Activating or Deactivating the Command Monitor

To activate or deactivate the command monitor, choose Command Monitor ® Change Monitor Settings.

Activate the command monitor (entry recording criteria, choose Activate Monitor Settings) shortly before the relevant transaction is started. Deactivate the command monitor (Exit SQL Monitoring) as soon as the transaction ends.

Recording Criteria

Choose Command Monitor ® Monitor Settings to determine the recording criteria for which SQL statements are logged in the command monitor tables.

Option	Explanation
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 ration of qualified records to read records falls below the specified percentage.
Save Parameter Values	Select this field if you want to log the SQL statements with their parameters. If this criterion is active, choose Display Execution Plan for SQL Statement to check which access strategy the SQL optimizer would choose to process this SQL statement. If requested by Support, you may need to create an optimizer trace. To do this, choose Trace Execution Plan for SQL Statement.
Max. Number of Monitor Entries	This value determines the maximum number of entries that are held in the table SYSMONITOR before the table is overwritten.

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.

Displaying SQL Statements

In the command monitor, choose Refresh Display to display the logged SQL statements.

If a logged SQL statement was called from an ABAP program, you can trace the statement back to this program. To do this, select the statement and choose Calling Pos. In ABAP Program.

Double-click an SQL statement in the command monitor to shows its details. In this way, you can view the complete SQL statement.

Initializing the Command Monitor Tables

The SQL statements and the values specified in their WHERE clauses are entered in the tables SYSMONITOR and SYSMONDATA, if these statements meet the recording criteria, and are executed in the database system after the command monitor was started. These tables are overwritten cyclically. Stopping the command monitor does not initialize the tables SYSMONITOR and SYSMONDATA. To do this, choose Command Monitor ® Initialize Monitor Tables.