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.
To check the shared cursor cache, do the following:
Alternatively, use transaction code ST04.
The most interesting entries for the database administrator are:
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.
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)