signed

QiShunwang

“诚信为本、客户至上”

动态性能视图v$session_longops

2021/6/3 14:13:29   来源:

一 视图说明

v$session_longops视图记录了执行时间长于6秒的某个操作(备份、恢复、收集统计信息、Hash Join、Sort 、Nested loop、Table Scan、 Index Scan 等),该视图通常用来分析SQL运行缓慢的原因,配合V$SESSION视图使用。

使用该视图,需要满足如下条件:

  • 将初始化参数 timed_statistics设置为true或者开启sql_trace;
  • .用ANALYZE或者DBMS_STATS对对象收集过统计信息。

列含义解释:

Column (列)Datatype (数据类型)Description(描述)
SIDNUMBERSession identifier(Session标识)
SERIAL#NUMBERSession serial number(Session串号)
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(执行操作的用户ID)
SQL_ADDRESSRAW(4 | 8)Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation (关联v$sql)
SQL_HASH_VALUENUMBERUsed with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation(关联v$sql)
SQL_IDVARCHAR2(13)SQL identifier of the SQL statement associated with the operation(关联v$sql)
QCSIDNUMBERSession identifier of the parallel coordinator(主要是并行查询一起使用)

注意:若某个SQL语句执行时间比较长,但是每个操作都没有超过6秒钟,那么你在V$SESSION_LONGOPS这个视图中就无法查询到该信息。

二 使用范例

查询SQL语句执行的进度和预计完成操作的剩余时间

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;

在这里插入图片描述