动态性能视图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对对象收集过统计信息

 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;

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

set lines 200 pages 50000
col USERNAME for a10
SELECT USERNAME,
       SID,
       OPNAME,
       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
       TIME_REMAINING,
       SQL_TEXT
  FROM GV$SESSION_LONGOPS, GV$SQL
 WHERE TIME_REMAINING <> 0
   AND SQL_ADDRESS = ADDRESS
   AND SQL_HASH_VALUE = HASH_VALUE;

set lines 200 pages 50000
col USERNAME for a10
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;

set lines 200 pages 50000
col USERNAME for a10
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
   ORDER BY TIME_REMAINING DESC;
  
set lines 200 pages 50000
col USERNAME for a10
col USERNAME for a10
SELECT USERNAME,
       SID,
       OPNAME,
       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
       TIME_REMAINING
  FROM V$SESSION_LONGOPS, V$SQL
 WHERE TIME_REMAINING <> 0
   AND SQL_ADDRESS = ADDRESS
   AND SQL_HASH_VALUE = HASH_VALUE;
   
set lines 200 pages 50000
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

select sql_text from v$sqlarea a,v$session b where a.HASH_VALUE = b.SQL_HASH_VALUE and b.username='SAPSR3' and b.status='ACTIVE'; 

select sql_text from gv$sqlarea a,gv$session b where a.HASH_VALUE = b.SQL_HASH_VALUE and b.status='ACTIVE'; 
 
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 这个视图