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