动态性能视图v$session_longops

最新推荐文章于 2025-08-21 23:21:52 发布

weixin_30716725

最新推荐文章于 2025-08-21 23:21:52 发布

阅读量84

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/willspring/p/5670902.html

本文介绍Oracle数据库中的v$session_longops视图，该视图用于监控执行时间超过6秒的操作状态，如备份、恢复、统计信息收集等。文章详细解释了如何配置环境以启用此功能，并提供了查询示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

Column	Datatype	Description
`SID`	`NUMBER`	Session identifier
`SERIAL#`	`NUMBER`	Session serial number
`OPNAME`	`VARCHAR2(64)`	Brief description of the operation
`TARGET`	`VARCHAR2(64)`	The object on which the operation is carried out
`TARGET_DESC`	`VARCHAR2(32)`	Description of the target
`SOFAR`	`NUMBER`	The units of work done so far
`TOTALWORK`	`NUMBER`	The total units of work
`UNITS`	`VARCHAR2(32)`	The units of measurement
`START_TIME`	`DATE`	The starting time of operation
`LAST_UPDATE_TIME`	`DATE`	Time when statistics last updated
`TIMESTAMP`	`DATE`	Timestamp
`TIME_REMAINING`	`NUMBER`	Estimate (in seconds) of time remaining for the operation to complete
`ELAPSED_SECONDS`	`NUMBER`	The number of elapsed seconds from the start of operations
`CONTEXT`	`NUMBER`	Context
`MESSAGE`	`VARCHAR2(512)`	Statistics summary message
`USERNAME`	`VARCHAR2(30)`	User ID of the user performing the operation
`SQL_ADDRESS`	`RAW(4 \| 8)`	Used with the value of the `SQL_HASH_VALUE` column to identify the SQL statement associated with the operation
`SQL_HASH_VALUE`	`NUMBER`	Used with the value of the `SQL_ADDRESS` column to identify the SQL statement associated with the operation
`SQL_ID`	`VARCHAR2(13)`	SQL identifier of the SQL statement associated with the operation
`QCSID`	`NUMBER`	Session 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;

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这个视图

posted on 2016-07-14 16:39 田灬禾阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/willspring/p/5670902.html