动态性能视图v$session_longops

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

 

该视图记录了执行时间长于6秒的某个操作(这些操作可能是备份，恢复，收集统计信息，Hash Join,Sort ,Nested loop，Table Scan, Index Scan 等等)，这个视图通常用来分析SQL运行缓慢的原因，配合V$SESSION视图。

1.必须将初始化参数 timed_statistics设置为true或者开启sql_trace

 

2.必须用ANALYZE或者DBMS_STATS对对象收集过统计信息

 

 

Column	Datatype	Description
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Session serial number
OPNAME	VARCHAR2(64)	Brief description of the operation
TARGET	VARCHAR2(64)	The object on which the operation is carried out
TARGET_DESC	VARCHAR2(32)	Description of the target
SOFAR	NUMBER	The units of work done so far
TOTALWORK	NUMBER	The total units of work
UNITS	VARCHAR2(32)	The units of measurement
START_TIME	DATE	The starting time of operation
LAST_UPDATE_TIME	DATE	Time when statistics last updated
TIMESTAMP	DATE	Timestamp
TIME_REMAINING	NUMBER	Estimate (in seconds) of time remaining for the operation to complete
ELAPSED_SECONDS	NUMBER	The number of elapsed seconds from the start of 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 associated with the operation
SQL_HASH_VALUE	NUMBER	Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement associated with the operation
QCSID	NUMBER	Session identifier of the parallel coordinator

 

 SID                    Session标识              
 SERIAL#                Session串号              
 OPNAME                 操作简要说明           　  
 TARGET                 操作运行所在的对象          
 TARGET_DESC            目标对象说明      　　　　  
 SOFAR                  至今为止完成的工作量    　　 
 TOTALWORK              总工作量    　　　　　　　　 
 UNITS                  工作量单位            　　 
 START_TIME             操作开始时间          　　
 LAST_UPDATE_TIME       统计项最后更新时间     　　
 TIMESTAMP              操作的时间戳              
 TIME_REMAINING         预计完成操作的剩余时间(秒)   
 ELAPSED_SECONDS        从操作开始总花费时间(秒)　　 
 CONTEXT                前后关系    　　　　　　　　 
 MESSAGE                统计项的完整描述       　　
 USERNAME               执行操作的用户ID       　　 
 SQL_ADDRESS            关联v$sql                
 SQL_HASH_VALUE         关联v$sql       　　　　　　
 SQL_ID                 关联v$sql　　 　　　　
 QCSID 　　　　　　　　　　主要是并行查询一起使用　

要理解的就是：比如某个SQL语句执行时间比较长，但是每个操作都没有超过6秒钟，那么你在V$SESSION_LONGOPS这个视图中就无法查询到该信息。

• 相关操作语句：

　　　　

SELECT USERNAME,
       SID,
       OPNAME,
       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
       TIME_REMAINING,
       SQL_TEXT
  FROM V$SESSION_LONGOPS, V$SQL
 WHERE TIME_REMAINING <> 0
   AND SQL_ADDRESS = ADDRESS
   AND SQL_HASH_VALUE = HASH_VALUE;

 

select * from v$session a,v$session_longops b where a.SID = b.SID and a.SERIAL# = b.SERIAL# ;  

select * from v$sql a,v$session_longops b where a.SQL_ID = b.SQL_ID ;  

select * from v$sqlarea a,v$session_longops b where a.HASH_VALUE = b.SQL_HASH_VALUE ;  

如果是在RAC,改成GV$SESSION_LONGOPS这个视图