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.

4. 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:

1. 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.

2. 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.)