一 视图说明
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(描述) |
---|---|---|
SID | NUMBER | Session identifier(Session标识) |
SERIAL# | NUMBER | Session serial number(Session串号) |
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(执行操作的用户ID) |
SQL_ADDRESS | RAW(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_VALUE | NUMBER | Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation(关联v$sql) |
SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement associated with the operation(关联v$sql) |
QCSID | NUMBER | Session 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;