v$session_longops

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

To monitor query execution progress, you must be using the cost-based optimizer and you must:

    Set the TIMED_STATISTICS or SQL_TRACE parameter to true

    Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

--模拟一下
SQL> create table long_ as select level lv,rownum rn from dual connect by level<10000000;

表已创建。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> insert into long_ select level lv,rownum rn from dual connect by level<10000000;

已创建9999999行。

SQL> commit;

提交完成。

SQL> select count(*) from long_;

  COUNT(*)
----------
  39999996

SQL> desc v$session_longops
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 QCSID                                              NUMBER

SQL> col sid for 99999
SQL> col opname for a20
SQL> col sofar for 9999999999
SQL> col totalwork for 9999999999
SQL> col units for a20
SQL> col sql_hash_value for 999999999999
SQL> col sql_id for a15
SQL> set linesize 200
--v$session_longops中记录了这次全表扫描操作
SQL> select sid,opname,sofar,totalwork,units,sql_hash_value,sql_id from v$session_longops where sql_id is not null;

   SID OPNAME                     SOFAR   TOTALWORK UNITS                SQL_HASH_VALUE SQL_ID
------ -------------------- ----------- ----------- -------------------- -------------- ---------------
   145 Table Scan                 92908       92908 Blocks                   1268759093 gw2gzt55tzfjp

--找出sql
SQL> select a.sql_text from v$sqlarea a,v$session_longops b where b.sql_id=a.sql_id;

SQL_TEXT
-------------------------------------------------------------------------------------------------------

select count(*) from long_

v$session_longops.START_TIME字段代表操作开始时间。