正德厚生,臻于至善

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

ColumnDatatypeDescription
SIDNUMBERSession identifier
SERIAL#NUMBERSession serial number
OPNAMEVARCHAR2(64)Brief description of the operation
TARGETVARCHAR2(64)The object on which the operation is carried out
TARGET_DESCVARCHAR2(32)Description of the target
SOFARNUMBERThe units of work done so far
TOTALWORKNUMBERThe total units of work
UNITSVARCHAR2(32)The units of measurement
START_TIMEDATEThe starting time of operation
LAST_UPDATE_TIMEDATETime when statistics last updated
TIMESTAMPDATETimestamp
TIME_REMAININGNUMBEREstimate (in seconds) of time remaining for the operation to complete
ELAPSED_SECONDSNUMBERThe number of elapsed seconds from the start of operations
CONTEXTNUMBERContext
MESSAGEVARCHAR2(512)Statistics summary message
USERNAMEVARCHAR2(30)User ID of the user performing the operation
SQL_ADDRESSRAW(4 | 8)Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation
SQL_HASH_VALUENUMBERUsed with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_IDVARCHAR2(13)SQL identifier of the SQL statement associated with the operation
QCSIDNUMBERSession 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;

如果是在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 这个视图
赞(0) 打赏
未经允许不得转载:徐万新之路 » 动态性能视图v$session_longops
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏