当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

v$session_longops视图

对大部分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

 

本文固定链接: http://www.dbdream.com.cn/2013/10/vsession_longops%e8%a7%86%e5%9b%be/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2013年10月14日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: v$session_longops视图 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

v$session_longops视图:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter