Procedure documentationResource Monitor Locate this document in the navigation structure

 

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.

Prerequisites

Prerequisites

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.

Procedure

Open the Database Assistant (transaction DB50) or the liveCache Assistant (transaction LC10). Choose   Problem Analysis   SQL Performance   Resource Monitor  .

or

Open the DBA Cockpit (transaction DBACOCKPIT) and choose   Performance   SQL Performance   Resource Monitor  .

Result

Starting/Stopping the SQL Resource Monitor

To start or stop the SQL resource monitor, choose   Resource Monitor   Start Recording / Stop Recording  .

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   Resource Monitor   Refresh Monitor Status  .

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   Resource Monitor   Refresh Monitor Output  .

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   Resource Monitor   Initialize Monitor Tables  .

More Information