The SQL Performance user menu of the DBA Cockpit 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.
Only for the SAP MaxDB (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.
Open the DBA Cockpit (transaction DBACOCKPIT) and select .
To activate or deactivate the SQL command monitor shortly before a transaction that is to be analyzed is started, follow the procedure below:
Choose
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.
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.
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
.Deactivate the SQL command monitor as soon as the transaction you want to analyze ends. Choose
and then Exit SQL Monitoring.To display the SQL statements logged according to the recording criteria selected, choose
in the SQL command monitor.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 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)
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
.Concepts of the Database System, Search strategies, List of all Search Strategies, EXPLAIN Statement