The SQL Performance user menu of the DBA Cockpit contains the SQL resource monitor. By analyzing SQL resource consumption, you can identify the most costly SQL statements. The resources used by an SQL statement are measured (runtime and I/O accesses, for example).
If an SQL statement is used more than once, the total cost is calculated. This enables you to identify those SQL statements that have a relatively short runtime but cause a considerable database load due to the number of times they are executed. The SQL resource monitor is therefore a monitoring tool that can be used for load analysis of one workday, for example.
The SQL resource monitor is always started. The SQL resource monitor tables (among others, the system table COMMANDSTATISTICS) contain the SQL statements and the statistical details that the SQL resource monitor records when an SQL statement is executed.
Open the DBA Cockpit (transaction DBACOCKPIT) and choose .
For SAP NetWeaver 7.1 , this status information is displayed in the Resource Monitor menu, which is different from the menu described below.
Current Monitor Status
The status of the SQL resource 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.
Output Criteria
Since the number of recorded SQL statements can be very large, you can restrict the display by setting output criteria.
Data Since |
Restart: The SQL statements since the last restart of the database system are displayed. Last Reset: The SQL statements since the last reset of the SQL resource monitor tables are displayed. |
Number of Rows Read |
An SQL statement is logged if the specified number of read rows is exceeded. |
Number of Executions |
An SQL statement is logged if the specified number of executions is exceeded. |
Runtime in ms |
An SQL statement is displayed if the specified runtime is exceeded. |
Number of statements (display of the <n> statements with the longest runtime) |
Since the number of recorded SQL statements can be very large, you can restrict the number displayed by setting this output criterion. |
To output the results in the SQL resource monitor, choose Refresh Monitor Output.
Recorded SQL Statements
The SQL statements that match the desired output and display criteria are displayed.
To obtain more information about an SQL statement, double-click the SQL statement or choose Details . You can see the complete SQL statement and additional analyses for the SQL statement in the following view:
Tables/View Information: The Tables/Views/Synonyms display appears.
EXPLAIN for SQL Statement: The system switches to the SELECT Editor with the EXPLAIN statement specified for this SQL statement. The EXPLAIN statement is only displayed if no parameter placeholders are specified in the 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 SQL statement back to that program.
To initialize the SQL resource monitor tables, in the SQL resource monitor, choose Reset Counter.
Concepts of the Database System, Displaying the Search Strategies Used by SQL Statements (EXPLAIN)
SQL Reference Manual, COMMANDSTATISTICS