Monitoring the Shared SQL Area (Oracle)

Purpose

Poorly written SQL statements have perhaps the greatest impact on application performance. An SQL statement with which the Oracle database system reads and/or sorts thousands or even millions of rows of data can bring the database to a standstill. Proper use of indexes is vital to prevent such situations from occurring.

You should use the SQL trace to analyze any problems, provided that you know which transaction caused them. Alternatively, for Oracle databases you can analyze the shared SQL area. In order to identify resource-intensive operations, database administrators should be familiar with monitoring SQL statements in the shared SQL area.

To analyze the problem, first you should sort the shared SQL area according to the column Disk reads or Buffer gets and then analyze the SQL statements from top to bottom.

Process Flow

First check whether any indexes are missing in the tables that are accessed in the statement.

Check that current statistics exist for the tables that are accessed in the statement. For more information, see Tables/Index Analysis (Oracle)

Use SAPNet to search for notes using the keyword "Performance" and the table name of the resource-intensive SQL statement. SAP may recommend that you create or modify indexes or make program corrections.

You can use the explain plan (choose Explain) to decide whether creating or modifying an index would increase the performance of the SQL statement. Remember that a new index can also be counterproductive to system performance. Therefore you should only create new indexes after careful consideration.

To check the effect of creating an index on the performance of your system, SAP recommends the following procedure:

Before you create an index on the Database performance: Shared SQL screen, choose Select Table and enter the name of the table that should have the new index. The system displays all SQL statements in which the table is accessed. Save the selection results to a local file.

Create the index and repeat the procedure described above after the database has been productive for a short time. By comparing the Reads/Execution values before and after the indexes were created, you can now determine which statements were affected positively and which were affected negatively by the new index. (You should also examine the Gets/Execution column.)

An object accessed by a program may not be in an optimal state as far as performance is concerned. For more information, see Monitoring Table and Index Fragmentation (Oracle)