「优化体系」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对对象收集过统计信息
相关字段
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语句。
如果是在RAC,改成GV$SESSION_LONGOPS这个视图
篇幅有限,就介绍到这了,这个视图建议重点掌握。后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~