Command Monitor 

The SQL Performance user menu (OLTP/ liveCache) contains, among other things, the command monitor. Use this tool if the analysis of the database bottlenecks (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

OLTP: Prerequisites

Only for the OLTP database instance that is the basis of the current SAP Web AS system: 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.

liveCache: The user has been assigned a role in accordance with the authorization concept. You are in the user menu of the user with the authorizations that correspond to this role.

Procedure

OLTP: In the Database Assistant, choose Problem Analysis → SQL Performance  → Command Monitor.

liveCache: In the liveCache Assistant, choose liveCache → Monitoring → Problem Analysis → SQL Performance→ Command Monitor.

..

Activating or Deactivating the Command Monitor

To activate or deactivate the 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 recording criteria.

       3.      Choose Activate Monitor Settings.
Deactivate the command monitor as soon as the transaction ends. Choose End SQL Monitoring to do this.

Recording Criteria

The recording criteria determine how SQL statements are to be 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 ratio 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. You can only use the options Display Execution Plan for SQL Statement or Trace Execution Plan for SQL Statement if this recording criterion is active.
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 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.

Displaying SQL Statements

●     To display the logged SQL statements, choose Refresh Display in the command monitor.

●     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 Call Points 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 statement. You can see the complete SQL statement in the following view.
Additional analyses for SQL statements:

- Display/Trace Execution Plan for an SQL Statement :
Among other things, the search strategy that the optimizer would select to process this SQL statement is displayed here
(See also Concepts of the Database System, List of All Search Strategies
If requested by Support, you may need to create an optimizer trace.
   Choose Explain with Hint, if you wan to test the affect of different indexes on the
   excecution plan of an SQL statement.

- Replace 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 directly.

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

- Display Call Point in ABAP Program (only for the OLTP database instance that is the basis of the current Web AS system)

- Information about the ABAP Call Point (only for the OLTP database instance that is the basis of the current Web AS system)

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 initialize the command monitor tables, choose Command Monitor  → Initialize Monitor Tables.