Identifying High-Load SQL Statements Using Top SQL

ADDM automatically identifies high-load SQL statements that may be causing systemwide performance degradation. Under normal circumstances, manual identification of high-load SQL statements is not necessary. In some cases, however, you may want to monitor SQL statements at a more granular level. The Top SQL section of the Top Activity page in Enterprise Manager enables you to identify high-load SQL statements for any 5-minute interval.

To access the Top Activity page: 

1. From the Database Home page, click Performance.

   The Performance page appears.

2. Under Additional Monitoring Links, click Top Activity.

   The Top Activity page appears.

   This page shows a 1-hour time line of the top activity running on the database. SQL statements that are using the highest percentage of database activity are listed under the Top SQL section, and are displayed in 5-minute intervals.

3. To move the 5-minute interval, drag the shaded box to the desired time.

   The information contained in the Top SQL section will be automatically updated to reflect the selected time period. Use this page to identify high-load SQL statements that may be causing performance problems.

4. To monitor SQL statements for a longer duration than one hour, select Historical from the View Data list.

   In Historical view, you can view the top SQL statements for the duration defined by the AWR retention period.

This section contains the following topics:

• Viewing SQL Statements by Wait Class

• Viewing Details of SQL Statements