Get the Execution Plan from/using V$SQL_PLAN_MONITOR

Starting with 11g, Oracle introduced a new tool to add to the performance optimizer’s toolkit called “Real Time SQL Monitoring”.  Real Time SQL Monitoring, or just “SQL Monitoring”, enables you to measure the performance of SQL statements while they are executing.  

The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to ALL or TYPICAL (the default value). Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value).

SQL monitoring is automatically started when a SQL statement runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution.

SQL monitoring is active by default. However, to force SQL monitoring, use the MONITOR hint. To stop monitored, use the NO_MONITOR hint.

We can monitor the statistics for SQL statement execution using the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views.

Monitoring information is not deleted immediately, it will be kept in the V$SQL_MONITOR view for at least one minute. The entry is eventually deleted so its space can be reclaimed as new statements are monitored.


The V$SQL_MONITOR and V$SQL_PLAN_MONITOR views can be used in conjunction with the following views to get additional information about the execution that is monitored:V$SQL, V$SQL_PLAN, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS, and V$SESSION.

Instead, you can use the SQL monitoring report to view SQL monitoring data. The SQL monitoring report is also available in a GUI version through Enterprise Manager and
SQL Developer.

The DBMS_SQLTUNE.REPORT_SQL_MONITOR function accepts several input parameters to specify the execution, the level of detail in the report, and the report type (TEXT, HTML, or XML). By default, a text report is generated for the last execution that was monitored if no parameters are specified.

Syntax

DBMS_SQLTUNE.REPORT_SQL_MONITORK(
   sql_id                   IN VARCHAR2  := NULL,
   session_id             IN NUMBER    := NULL,
   session_serial        IN NUMBER    := NULL,
   sql_exec_start       IN DATE      := NULL,
   sql_exec_id           IN NUMBER    := NULL,
   inst_id                  IN NUMBER    := -1,
   start_time_filter    IN DATE      := NULL,
   end_time_filter     IN DATE      := NULL,
   instance_id_filter  IN NUMBER    := NULL,
   parallel_filter        IN VARCHAR2  := NULL,
   event_detail         IN VARCHAR2  := 'YES',
   report_level          IN VARCHAR2  := 'TYPICAL',
   type                     IN VARCHAR2  := 'TEXT')

 RETURN CLOB;


Example:

Generate report in text format for recently captured sql....

SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;

Generate a report in html format.......

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /home/report_sql_monitor.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => '123mvcc67nfy4',

  type         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

REPORT_SQL_MONITOR_LIST:

This function will give you list of already monitered sql's and other details.

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /home/report_sql_monitor_list.htm

SELECT DBMS_SQLTUNE.report_sql_monitor_list(

  type         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

Reference Link:  This page has more stuff.

• http://www.oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1.php#report_sql_detail