转http://www.dbdream.com.cn/2013/10/14/vsession_longops%E8%A7%86%E5%9B%BE/
1.有的时候不准确 ，我看到 session wait event 一直在变化 ，也是 active 的 ，运行 时间也是48小时 以上 。但是 在 v$session_longops 显示为totalwork =0 , 无法观察 。是一条3000w 记录的update. 所以只能看v$transaction.
2.
对大部分DBA来说，V$SESSION_LONGOPS视图都不会陌生，以前在面试的时候，也有一些企业会问到如何查询数据库中运行时间比较长的SQL，就可以通过这个视图来查看。
V$SESSION_LONGOPS视图不但可以监控运行式时间比较长的SQL，也会记录RMAN备份、EXP/EXPDP、收集统计信息、排序等操作，基本数据库所有运行时间超过6秒的SQL都会记录在这个视图中，也有的DBA会定期检查这个视图来寻找可优化的SQL。
下面是这个视图的结构：
01 | SQL> desc v$session_longops |
02 | 名称 是否为空? 类型 注释 |
03 | ----------------------------------------- -------- ------------------ ------------------ |
04 | SID NUMBER 和V$SESSION中的SID一样 |
05 | SERIAL# NUMBER 和V$SESSION中的SERIAL#一样 |
06 | OPNAME VARCHAR2(64) 操作的名称，如全表扫描 |
07 | TARGET VARCHAR2(64) 被操作的对象名，如表名 |
08 | TARGET_DESC VARCHAR2(32) TARGET的描述 |
09 | SOFAR NUMBER 以完成的数量，如扫描多少数据块 |
10 | TOTALWORK NUMBER 一共需要完成的数量 |
11 | UNITS VARCHAR2(32) 计量单位 |
12 | START_TIME DATE 开始时间 |
13 | LAST_UPDATE_TIME DATE 最后一次调用set_session_longops的时间 |
14 | TIMESTAMP DATE 特定操作的时间戳 |
15 | TIME_REMAINING NUMBER 预计剩余时间，单位秒 |
16 | ELAPSED_SECONDS NUMBER 开始操作到最后更新的时间 |
17 | CONTEXT NUMBER |
18 | MESSAGE VARCHAR2(512) 对操作的描述 |
19 | USERNAME VARCHAR2(30) 操作用户的名字 |
20 | SQL_ADDRESS RAW(4) 用于关联V$SQL等视图 |
21 | SQL_HASH_VALUE NUMBER 用于关联V$SQL等视图 |
22 | SQL_ID VARCHAR2(13) 用于关联V$SQL等视图 |
23 | SQL_PLAN_HASH_VALUE NUMBER 用于关联V$SQL等视图 |
24 | SQL_EXEC_START DATE SQL开始运行的时间 |
25 | SQL_EXEC_ID NUMBER SQL执行的标识符 |
26 | SQL_PLAN_LINE_ID NUMBER SQL执行计划相关 |
27 | SQL_PLAN_OPERATION VARCHAR2(30) SQL执行计划相关 |
28 | SQL_PLAN_OPTIONS VARCHAR2(30) SQL执行计划相关 |
29 | QCSID NUMBER 并行查询 |
下面简单做几个超过6秒的操作，来查看下这个视图。
先测试下insert操作。
01 | SQL> create table longops_test as select * from dba_objects; |
02 | 表已创建。 |
03 | SQL> insert into longops_test select * from longops_test; |
04 | 已创建65525行。 |
05 | SQL> / |
06 | 已创建131050行。 |
07 | SQL> / |
08 | 已创建262100行。 |
09 | SQL> / |
10 | 已创建524200行。 |
11 | SQL> / |
12 | 已创建1048400行。 |
13 | SQL> commit; |
14 | 提交完成。 |
现在INSERT操作已经超过6秒，查询下V$SESSION_LONGOPS视图。
1 | SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS MESSAGE SQL_PLAN_OPERATION SQL_PLAN_OPTIONS |
2 | --- ------- --------- ---------------- ----- -------- ----- --------------- ------------------- --------------- |
3 | 194 12 Table Scan SYS.LONGOPS_TEST 14895 14895 Blocks Table Scan: TABLE ACCESS FULL |
4 | SYS.LONGOPS_TEST: |
5 | 14895 out of |
6 | 14895 Blocks done |
这是已经运行结束的INSERT操作，可见SOFAR和TOTALWORK的值是一样的，在看下没运行完的SQL。
1 | SQL> create table longops_ctas as select * from longops_test; |
在SQL执行6秒后(还没运行完)时，看下V$SESSION_LONGOPS视图的状态。
01 | SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS MESSAGE SQL_PLAN_OPERATION SQL_PLAN_OPTIONS |
02 | --- ------- --------- ---------------- ----- -------- ----- --------------- ------------------- --------------- |
03 | 194 12 Table Scan SYS.LONGOPS_TEST 14895 14895 Blocks Table Scan: TABLE ACCESS FULL |
04 | SYS.LONGOPS_TEST: |
05 | 14895 out of |
06 | 14895 Blocks done |
07 | 194 12 Table Scan SYS.LONGOPS_TEST 13275 29785 Blocks Table Scan: TABLE ACCESS FULL |
08 | SYS.LONGOPS_TEST: |
09 | 13275 out of |
10 | 29785 Blocks done |
可见，当前扫描了13275个数据块，总共需要扫描29785个数据块。RMAN备份和收集统计信息也同样会被这个视图记录。
01 | OPNAME SOFAR TOTALWORK MESSAGE |
02 | --------------------------------- ---------- ---------- ------------------------------------------- |
03 | Gather Table Partition Statistics 1 1 Gather Table Partition Statistics: Table |
04 | WR H$_OSSTAT : 1 out of 1 Partitions done |
05 | RMAN: aggregate input 1181953 1181953 RMAN: aggregate input: backup 33: 1181953 |
06 | out of 1181953 Blocks done |
07 | RMAN: aggregate input 1181953 1181953 RMAN: aggregate input: backup 33: 1181953 |
08 | out of 1181953 Blocks done |
09 | RMAN: aggregate output 359461 359461 RMAN: aggregate output: backup 33: 359461 |
10 | out of 359461 Blocks done |
11 | RMAN: full datafile backup 1181280 1181280 RMAN: full datafile backup: Set Count 18: |
12 | 1181280 out of 1181280 Blocks done |
下面是ORACLE官方文档对V$SESSION_LONGOPS视图的介绍：
01 | V$SESSION_LONGOPS 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. |
02 |
03 | To monitor query execution progress, you must be using the cost-based optimizer and you must: |
04 |
05 | •Set the TIMED_STATISTICS or SQL_TRACE parameters to true |
06 |
07 | •Gather statistics for your objects with the DBMS_STATS package |
08 |
09 | You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. |
10 | Column Datatype Description |
11 | ------------------- ---------- --------------------------------------------------------------------------------- |
12 | SID NUMBER Identifier of the session processing the long-running operation. If multiple sessi |
13 | ons are cooperating in the long-running operation, then SID corresponds to the mai |
14 | n or master session. |
15 | SERIAL# NUMBER Serial number of the session processing the long-running operation. If multiple se |
16 | ssions are cooperating in the long-running operation, then SERIAL# corresponds to |
17 | the main or master session. SERIAL# is used to uniquely identify a session's objec |
18 | ts. Guarantees that session-level commands are applied to the correct session obje |
19 | cts if the session ends and another session begins with the same session ID. |
20 | OPNAME VARCHAR2(64) Brief description of the operation |
21 | TARGET VARCHAR2(64) Object on which the operation is carried out |
22 | TARGET_DESC VARCHAR2(32) Description of the target |
23 | SOFAR NUMBER Units of work done so far |
24 | TOTALWORK NUMBER Total units of work |
25 | UNITS VARCHAR2(32) Units of measurement |
26 | START_TIME DATE Starting time of the operation |
27 | LAST_UPDATE_TIME DATE Time when statistics were last updated for the operation |
28 | TIMESTAMP DATE Timestamp specific to the operation |
29 | TIME_REMAINING NUMBER Estimate (in seconds) of time remaining for the operation to complete |
30 | ELAPSED_SECONDS NUMBER Number of elapsed seconds from the start of the operations |
31 | CONTEXT NUMBER Context |
32 | MESSAGE VARCHAR2(512) Statistics summary message |
33 | USERNAME VARCHAR2(30) User ID of the user performing the operation |
34 | SQL_ADDRESS RAW(4 | 8) Used with the value of the SQL_HASH_VALUE column to identify the SQL statement as |
35 | sociated with the operation |
36 | SQL_HASH_VALUE NUMBER Used with the value of the SQL_ADDRESS column to identify the SQL statement assoc |
37 | iated with the operation |
38 | SQL_ID VARCHAR2(13) SQL identifier of the SQL statement associated with the long operation, if any |
39 | SQL_PLAN_HASH_VALUE NUMBER SQL plan hash value; NULL if SQL_ID is NULL |
40 | SQL_EXEC_START DATE Time when the execution of the SQL started; NULL if SQL_ID is NULL |
41 | SQL_EXEC_ID NUMBER SQL execution identifier (see V$SQL_MONITOR) |
42 | SQL_PLAN_LINE_ID NUMBER SQL plan line ID corresponding to the long operation; NULL if the long operation |
43 | is not associated with a line of the execution plan |
44 | SQL_PLAN_OPERATION VARCHAR2(30) Plan operation name; NULL if SQL_PLAN_LINE_ID is NULL |
45 | SQL_PLAN_OPTIONS VARCHAR2(30) Plan operation options; NULL if SQL_PLAN_LINE_ID is NULL |
46 | QCSID NUMBER Session identifier of the parallel coordinator |