The SQL Performance user menu contains, among other things, the SQL resource monitor.
By analyzing 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.
Only for the OLTP database that is the basis of the current SAP Web AS system: In order for the name of the ABAP program and other information to be sent to the database, the instance profile parameter dbs/ada/register_appl_info=1 must be set.
Open the Database Assistant (transaction DB50) or the liveCache Assistant (transaction LC10). Choose .
Open the DBA Cockpit (transaction DBACOCKPIT) and choose .
Starting/Stopping the SQL Resource Monitor
To start or stop the SQL resource monitor, choose.
During normal operation of the database system, the resource monitor is stopped. The status of the SQL resource monitor is displayed in the Current Monitor Status area and can be refreshed using .
Output of Results
Since the number of recorded SQL statements can be very large, you can restrict the display by setting output criteria.
To output the results in the SQL resource monitor, select.
Displaying SQL Statements
If a logged SQL statement was called from an ABAP program, you can trace the SQL statement back to that program. To do this, select the SQL statement in the display quantity of the SQL resource monitor 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:
Tables/View Information: The Tables/Views/Synonyms display appears.
Print Version: You can copy the output to the clipboard or print it directly.
Display Callpoint in ABAP Program (only for the OLTP database instance that serves as the basis for the current SAP Web AS system)
Information about ABAP Callpoint (only for the OLTP database that serves as the basis for the current SAP Web AS system)
Initializing the Resource Monitor Tables
Tables SYSDATA_ANALYZE and SYSCMD_ANALYZE save statistical information that is calculated when an SQL statement is executed, along with the SQL statements themselves, when the resource monitor is active. Stopping the resource monitor does not initialize tables SYSDATA_ANALYZE and SYSCMD_ANALYZE.
To initialize the resource monitor tables, in the SQL resource monitor, choose.
DBA Cockpit: SAP MaxDB, Resource Monitor
Database Administration, Special Database Parameters