Using Real-Time SQL Monitoring

Real-Time SQL Monitoring provides automatic monitoring of SQL statements, PL/SQL blocks, or composite database operations that are considered expensive. You can view a list of monitored SQL, view and save SQL Monitor Active Reports, and generate an Active Report for ad hoc SQL. The information provided by Real-Time SQL Monitoring is especially useful for long-running SQL statements.

Note:

The use of Real-time SQL Monitoring feature requires an Oracle Tuning Pack license (except for Oracle Database Free which requires no license). For more details, please refer to "Database Licensing Information User Manual" for the database version being used.

• Connect to a database in Oracle Explorer. Any user can view complete Active Reports for SQL they execute using schema objects they own. If your user has been granted the SELECT_CATALOG_ROLE, then you will be able to view Active Reports for SQL executed by other users. When this role has not been granted, and the schema objects involved are not owned by the user, the Active Report may be missing information, such as the Explain Plan.

• To view a list of monitored SQL, right click on a connection in Oracle Explorer and select Real Time SQL Monitoring from the menu. In the list of monitored SQL that appears, click the Activity Last: drop down and select the time frame you are interested in. Click the Top 100 by: drop down and select the metric you are interested in. Click the arrows in the column headers to sort by that column.

• To view an Active Report for a SQL statement in the list, click on the SQL ID you are interested in.

• To save a report, click the Save icon to choose to save as XML or as an Active Report.

• To generate an Active Report for ad-hoc SQL, type some SQL in the SQL editor, right click and select Execute and Monitor SQL.

  Note:

  The ad-hoc SQL will be executed in order for it to be monitored, however the query results for the SQL will not be displayed as it usually is when you execute SQL.