Viewing Details of SQL Statements

The Top SQL section of the Top Activity page displays the SQL statements executed within the selected 5-minute interval in descending order based on their resource consumption. The SQL statement at the top of this table represents the most resource-intensive SQL statement during that time period, followed by the second most resource-intensive SQL statement, and so on.

To view details of SQL statements: 

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.

3. In the Top SQL section, click the SQL ID link of the SQL statement.

   The SQL Details page for the selected SQL statement appears.

4. To view SQL details for a longer period, select Historical from the View Data list.

   You can now view SQL details in the past, up to the duration defined by the AWR retention period.

5. In the Text section, review the SQL text for the SQL statement.

   The Text section contains the SQL text for the selected SQL statement. Note that if only part of the SQL statement is displayed, then a plus sign (+) icon appears next to the Text heading. To view the SQL text for the entire SQL statement, click the plus sign (+) icon.

6. In the Plan Hash Values list in the Details section, do one of the following:

   • If the SQL statement has multiple plans, then select All to show SQL details for all plans.

   • Select a particular plan to display SQL details for this plan only.

7. View the subpages available on the SQL Details page to display additional information about the SQL statement, as described in the following sections:

   • Viewing SQL Statistics

   • Viewing Session Activity

   • Viewing the SQL Execution Plan

   • Viewing the Plan Control

   • Viewing the Tuning History

8. If the SQL statement is a high-load SQL statement, then tune it as described in Tuning SQL Statements.