「优化体系」Oracle优化必备之动态性能视图v$session_longops

概述

今天主要分享一个视图,对于性能优化还是有很大帮助的,通过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对对象收集过统计信息

「优化体系」Oracle优化必备之动态性能视图v$session_longops

相关字段

「优化体系」Oracle优化必备之动态性能视图v$session_longops

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这个视图中就无法查询到该信息。


实用脚本

#查找耗时超过6秒的操作
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;
#根据sid,serial#关联查询
select * from v$session a,v$session_longops b where a.SID = b.SID and a.SERIAL# = b.SERIAL# ; 
#查找具体sql
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 ;

根据v$session_longops中的sql_address和sql_hash_value两个字段,和v$sql动态视图结合起来查看这些长时间操作的SQL语句。

「优化体系」Oracle优化必备之动态性能视图v$session_longops

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


篇幅有限,就介绍到这了,这个视图建议重点掌握。后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

「优化体系」Oracle优化必备之动态性能视图v$session_longops

h1

相关推荐