Real Time Statistics of a SQL statement in Oracle

To get real time statistics ( well , close to ) of a SQL statement , we can use the view v$sql_monitor.
This view is available only in Oracle 11g .

Here is an example .


SQL> SELECT *

2 FROM
3 (SELECT sid ,
4 sql_id ,
5 status ,
6 cpu_time ,
7 buffer_gets ,
8 disk_reads ,
9 SQL_PLAN_HASH_VALUE
10 FROM v$sql_monitor
11 order by cpu_time desc
12 )
13 WHERE rownum <= 5 ;


SID    SQL_ID STATUS CPU_TIME BUFFER_GETS DISK_READS SQL_PLAN_HASH_VALUE
---------- ------------- ------------------- ---------- ----------- ---------- -------------------
139    5zruc4v6y32f9    DONE   184656250   2877026   77594 0
154    401xxgh4h55g3    DONE    94843750   3258120    7137 0
152    401xxgh4h55g3    DONE    92531250   3254389    7550 0
36     401xxgh4h55g3    DONE    92109375   3239036    8009 0
141    401xxgh4h55g3    DONE    21687500    988400    3190 0

5 rows selected.

You can see the documentation of this view at
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3048.htm