1)
set pagesize 0 echo off timing off linesize 1500 trimspool on trim on long 2000000 longchunksize 2000000
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(session_id=>sys_context('userenv','sid'),report_level=>'ALL') as report
from dual;

2)
set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off
spool dbarefsqlmonitoring1.html
select dbms_sqltune.report_sql_monitor(type=>'EM', sql_id=>'4abtrd97hoik8') monitor_report from dual;
spool off

Ever since 11gR2 has been released, it has created a lot of buzz in Oracle Blogosphere with various posts on 11g Release 2 (11gR2 ) New Features primarily ASM New features. I would like to introduce you all to a feature which has been incorporated in 11gR2 Oracle Enterprise Manager to display Real-Time SQL Monitoring information . I would say that this was much awaited feature which had to be implemented in OEM and comes as a handy tool for DBA’s to tune/monitor sql execution.

Oracle 11gR1 introduced two new views V$SQL_MONITOR and V$SQL_MONITOR_PLAN to provide runtime execution statistics. This was part of feature called Real-Time SQL Monitoring which allows you to monitor sql in near real-time as the statement executes.

To recap, please find below description for both views

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. Timing information could be viewed by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR).