Real time SQL monitoring (requires DIAGNOSTIC+TUNING pack license)

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

REPORT_SQL_MONITOR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ s.* from sh.sales s,SH.channels c where s.channel_id=c.channel_id and c.channel_desc='Partners'

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  DONGHUA (150:2043)
 SQL ID              :  68r55a0ccwgzu
 SQL Execution ID    :  16777216
 Execution Started   :  03/02/2011 15:23:11
 First Refresh Time  :  03/02/2011 15:23:11
 Last Refresh Time   :  03/02/2011 15:23:35
 Duration            :  24s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@vmxavsvr.lab.dbaglobe.com (TNS V1-V3)
 Fetch Calls         :  17203

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    1.42 |    0.54 |     0.29 |     0.60 | 17203 |  18807 |   51 |  13MB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1652006204)
=======================================================================================================================================================
| Id |       Operation        |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity | Activity Detail |
|    |                        |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |   (# samples)   |
=======================================================================================================================================================
|  0 | SELECT STATEMENT       |          |         |      |        25 |     +0 |     1 |     258K |      |       |       |          |                 |
|  1 |   HASH JOIN            |          |    230K |  536 |        25 |     +0 |     1 |     258K |      |       |  383K |   100.00 | Cpu (1)         |
|  2 |    TABLE ACCESS FULL   | CHANNELS |       1 |    3 |         1 |     +0 |     1 |        1 |      |       |       |          |                 |
|  3 |    PARTITION RANGE ALL |          |    919K |  529 |        25 |     +0 |     1 |     919K |      |       |       |          |                 |
|  4 |     TABLE ACCESS FULL  | SALES    |    919K |  529 |        25 |     +0 |    28 |     919K |   51 |  13MB |       |          |                 |
=======================================================================================================================================================

Related Views: V$SQL_MONITOR, V$SQL_PLAN_MONITOR
Applies to Oracle DB 11gR1 onwards