Show TOC

 SQL Request (Shared SQL Area)Locate this document in the navigation structure

Use

Execution of a single SQL statement can sometimes have a negative effect on system performance for all users. This is possible, for example, if the scanned dataset is very large or if the data returned must be processed (sorted) in large amounts. Statements of this type use CPU time ineffectively and database buffer and disk I/O operations reduce system performance for all users. It is the database administrator's task to monitor the shared cursor cache (also Shared SQL-Area), to identify uneconomical statements and to determine how to increase their performance.

Procedure

To check the shared cursor cache, do the following:

  1. Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Activity.

    Alternatively, use transaction code ST04.

  2. Choose Detail analysis menu and then SQL Request. You can also change the default selection criteria (Buffer gets >= 100.000, Disk Reads >= 10.000).

The most interesting entries for the database administrator are:

  • Total Executions - The frequency a statement is executed
  • Disk reads  - Number of Oracle blocks read for the statement by the hard disk
  • Buffer gets  - Number of Oracle buffer blocks read for the statement from the data buffer
  • Records processed - Number of table lines for the statement returned to the R/3 work process

You can see the SQL statements in the column SQL Text  and display them in full by double-clicking on the line.

For an overview of the statement types frequently executed in the cursor cache, you can use Sort to arrange the display according to different areas.

In any case, do not worry if the value of Total Executions is high, as some statements must be regularly executed. If, on the other hand, a repeatedly executed SQL statement has a high number of Reads or Gets  each time it is executed, you should analyze the system in detail. Check whether any indexes are missing or whether existing indexes are fragmented. Uneconomical SQL statements often access tables which would benefit from a new, secondary index. It is possible that indexes exist for the table, but that the SQL statement is written in such a way that it cannot use these indexes correctly.

Note

From this screen you cannot tell which user or which ABAP program is responsible for the uneconomical statement. From time to time it can be a laborious process from realizing that a table contains uneconomical statements, to actually finding the program containing these statements. You can use the dictionary info system to find a description of a specified table. You can also determine where this table is used. This information should help you to restrict your search.

See also:

Monitoring the Shared SQL Area (Oracle)

Missing Indexes