ANBOB™

his 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秒的，但是要注意并不是所有的超过6秒的记录都会在这里，

这个视图跟踪需要几个条件，TIMED_STATISTICS或者SQL_TRACE 系统参数要为TRUE;所涉及的对象已有用analyze或dbms_stats收集统计信息，优化器为CBO

看这个视图是如何查询的

sys@ORCL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='V$SESSION_LONGOPS';


VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
V$SESSION_LONGOPS
select SID, SERIAL#, OPNAME, 
TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS,
 START_TIME, 
 LAST_UPDATE_TIME,  
TIMESTAMP,
 TIME_REMAINING, 
 ELAPSED_SECONDS, CONTEXT, MESSAGE, USERNAME, 
 SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, QCSID  
from GV$SESSION_LONGOPS     
 where inst_id = USERENV('Instance')


sys@ORCL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='GV$SESSION_LONGOPS';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------
select inst_id, ksulosno, ksulosrn, ksulopna,      
   ksulotna, ksulotde, ksulosfr, ksulotot, ksulouni,       
   to_date(ksulostm,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),   
   to_date(ksulolut,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),     
   to_date(ksuloinft, 'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),     
   decode(ksulopna, 'Advisor', ksuloif2,       
      decode(sign(ksulotot-ksulosfr),-1,to_number(NULL),
          decode(ksulosfr, 0, to_number(NULL),    
              round(ksuloetm*((ksulotot-ksulosfr)/ksulosfr))))),  
   ksuloetm, ksuloctx, ksulomsg, ksulounm,     
   ksulosql, ksulosqh, ksulosqi, ksuloqid  
   from x$ksulop

看到是从X$ksulop, kernel service, user long operation 这个系统视图查到的

视图字段
SID：Session标识
SERIAL#：Session串号
OPNAME：操作简要说明
TARGET：操作运行所在的对象
TARGET_DESC：目标对象说明
SOFAR：至今为止完成的工作量
TOTALWORK：总工作量
UNITS：工作量单位
START_TIME：操作开始时间
LAST_UPDATE_TIME：统计项最后更新时间
TIME_REMAINING：预计完成操作的剩余时间(秒)
ELAPSED_SECONDS：从操作开始总花费时间(秒)
CONTEXT：前后关系
MESSAGE：统计项的完整描述
USERNAME：执行操作的用户ID
SQL_ADDRESS：用于连接查询的列
SQL_HASH_VALUE：用于连接查询的列

所以看可看出计算剩余完成时间=（总工作量-已完成工作量）/已完成工作量*已花费时间

查询视图里的信息在每个版本也有可能不同，比如有一些会记录
Table scan;
Index Fast Full Scan;
Hash join;
Sort/Merge;
Sort Output;
Rollback;
Gather Table’s Index Statistics;
backup;
recovery

对于
Table scan;
其实一般都是知道操作超过6秒钟是显示在这个视图里的标准，但是在看了一遍日志以后发现这个背后还有另外一个标准：table has to occupy at least 10000 database blocks
这是一个BOTH的关系，如果table scan 超过了6秒，但是没有满足这个条件也是一样不会显示在该视图里的
对于
Index Fast Full Scan;
其实除了6秒外也有一个同时判断的标准：Index has to occupy at least 1000 blocks
等等还有很多标准

另这个视图的时间字段比如start_time是增不减的，如果改了os时间为去年那这个时间值也是会增无减的，要小心这点

还存在的session的长时间执行的操作记录
col target for a30 heading 'Target/Operation'
col rate_per_min for 999,990 heading 'Units/Min'
col mins_left for 999,990 heading 'Mins left'
col units for a10 heading 'Units'
col sofar heading 'Work Done'
col totalwork heading 'Work Total'

select l.sid
      ,to_char (sysdate,'hh24:mi:ss')        as time_now
      ,to_char (l.start_time,'hh24:mi:ss')   as started
      ,l.sofar                               as sofar
      ,l.totalwork                           as totalwork
      ,units                                 as units
      ,sofar/((sysdate-start_time)*24*60)    as rate_per_min
      ,time_remaining/60                     as mins_left
      ,nvl(l.target, opname)                 as target
from  v$session_longops l, v$session s
where l.sofar <> l.totalwork
and   l.sid = s.sid
order by l.sid, l.start_time, l.totalwork, l.sofar

打赏

微信扫一扫，打赏作者吧～