Monitoring the Top SQL Statements

Previous

Next

---

The "top" SQL statements represent the SQL statements that are executed most often, that use more system resources than other SQL statements, or that use system resources more frequently than other SQL statements. Viewing the top SQL statements report that is available in the Oracle Database XE graphical user interface enables you to focus your SQL tuning efforts on the statements that can have the most impact on database performance.

See Oracle Database Performance Tuning Guide for a discussion of tuning SQL statements.

To monitor the top SQL statements:

1. Access the Database Home Page and log in as user SYSTEM.

   See "Accessing the Database Home Page" for instructions.

2. On the Database Home Page, click Administration, and then click Monitor.

3. On the Database Monitor page, click Top SQL.

   The Top SQL page appears.

4. (Optional) Select from the Top By list and click Go to change the statistic used to determine the statements that are the top SQL statements.

5. (Optional) Select from the Display Top list and click Go to change the number of SQL statements displayed in the report.

6. (Optional) Do one or more of the following to narrow down the contents of the report:

   • Enter search text into the SQL Text field, and then click Go.

     Only statements that contain the search text anywhere within them are displayed. The search is case-insensitive.

   • Enter a number into the Minimum Executions field, and then click Go.

     Only statements with executions greater than or equal to the Minimum Executions value are displayed.

   • Enter a module name into the Module field by clicking the up-arrow icon to the right of the field, clicking a module name in the pop-up Search window, and then clicking Go.

     Only statements run by the designated module are displayed.

7. (Optional) Click the magnifying glass icon to the left of the SQL statement to view the following information:

   • The complete statement text

   • The statement query plan

   • Descriptions of the tables involved in the statement