面试宝典:介绍下Oracle数据库动态性能视图 V$SESSION_LONGOPS_oracle视图数据是实时更新的吗-优快云博客

在这里插入图片描述

📊 Oracle 19C V$SESSION_LONGOPS 动态性能视图详解

1. ✨ 视图概述与作用

V$SESSION_LONGOPS 是 Oracle 19C 中用于监控长时间运行操作进度的关键动态性能视图。它提供了对执行时间超过6秒的数据库操作的实时监控和进度跟踪能力，是DBA进行性能监控和运维管理的重要工具。

核心作用：实时显示和跟踪数据库中长时间运行的操作进度，帮助DBA了解操作的执行状态、预估完成时间和资源消耗情况。
重要性：对于需要长时间运行的操作（如全表扫描、索引创建、数据加载等），此视图提供了宝贵的监控信息，使DBA能够及时了解操作进展，做出相应的管理决策。

2. 🧐 主要应用场景

长时间操作监控：实时监控执行时间超过6秒的操作进度。
性能优化：识别和优化长时间运行的操作。
容量规划：基于历史长时间操作数据规划系统资源。
运维管理：在维护窗口期间监控大型操作的执行情况。
用户沟通：向用户报告长时间操作的执行进度和预计完成时间。

3. 📋 V$SESSION_LONGOPS 字段详解

V$SESSION_LONGOPS 包含多个字段，提供了长时间运行操作的详细进度信息。

字段名	数据类型	描述
SID	NUMBER	会话标识符。执行长时间操作的会话ID。
SERIAL#	NUMBER	会话序列号。与SID一起唯一标识会话。
OPNAME	VARCHAR2(64)	操作名称。长时间操作的名称描述。
TARGET	VARCHAR2(64)	操作目标。操作的对象或目标描述。
TARGET_DESC	VARCHAR2(32)	目标描述。目标的详细描述。
SOFAR	NUMBER	已完成工作量。到目前为止完成的工作单位数。
TOTALWORK	NUMBER	总工作量。操作的总工作单位数。
UNITS	VARCHAR2(32)	工作量单位。工作量的度量单位。
START_TIME	DATE	开始时间。操作开始执行的时间。
LAST_UPDATE_TIME	DATE	最后更新时间。进度最后更新的时间。
TIME_REMAINING	NUMBER	预计剩余时间。估计的剩余时间（秒）。
ELAPSED_SECONDS	NUMBER	已用时间。从操作开始到现在经过的时间（秒）。
CONTEXT	NUMBER	上下文信息。操作的上下文标识符。
MESSAGE	VARCHAR2(512)	进度消息。格式化的进度消息。
USERNAME	VARCHAR2(30)	用户名。执行操作的用户名。
SQL_ADDRESS	RAW(4 \| 8)	SQL地址。正在执行的SQL语句的地址。
SQL_HASH_VALUE	NUMBER	SQL哈希值。正在执行的SQL语句的哈希值。
SQL_ID	VARCHAR2(13)	SQL标识符。正在执行的SQL语句的ID。
QCSID	NUMBER	查询协调者SID。并行查询中协调者会话的SID。
INST_ID	NUMBER	实例ID。在RAC环境中标识实例。
CON_ID	NUMBER	容器ID。在多租户环境中标识容器。

4. 🔗 相关视图与基表

4.1 相关性能视图

V$SESSION：提供会话的基本信息。
V$SQL：提供SQL语句的详细信息。
V$SQL_AREA：共享SQL区域的统计信息。
V$PROCESS：进程相关信息。
DBA_HIST_ACTIVE_SESS_HISTORY：AWR活动会话历史。

4.2 底层基表 (X$ Tables) 与原理

V$SESSION_LONGOPS 的数据来源于 Oracle 的长时间操作跟踪机制。

底层原理：
1. 操作检测：Oracle自动检测执行时间超过6秒的操作。
2. 进度跟踪：通过DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS过程跟踪操作进度。
3. 内存存储：进度信息存储在SGA中的内部数据结构中。
4. 自动清理：完成后自动从视图中移除。
数据特性：
- 实时性：数据实时更新。
- 临时性：操作完成后自动移除。
- 准确性：提供准确的进度信息。

5. ⚙️ 常用查询SQL

5.1 监控当前长时间运行的操作

SELECT sid, serial#, opname, target, 
       sofar, totalwork, 
       ROUND((sofar/totalwork)*100, 2) AS progress_percent,
       time_remaining, elapsed_seconds,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
       message
FROM v$session_longops
WHERE sofar != totalwork
ORDER BY start_time;

5.2 查看特定类型操作的进度

SELECT sid, serial#, username, opname,
       sofar, totalwork, units,
       ROUND((sofar/totalwork)*100, 2) AS progress_pct,
       time_remaining, elapsed_seconds
FROM v$session_longops
WHERE opname LIKE '%Table Scan%'  -- 例如：表扫描操作
  AND sofar != totalwork
ORDER BY elapsed_seconds DESC;

5.3 获取详细的进度信息（包含SQL信息）

SELECT sl.sid, sl.serial#, sl.username,
       sl.opname, sl.target,
       sl.sofar, sl.totalwork,
       ROUND((sl.sofar/sl.totalwork)*100, 2) AS progress_pct,
       sl.time_remaining, sl.elapsed_seconds,
       s.sql_id, s.sql_text
FROM v$session_longops sl
LEFT JOIN v$sql s ON sl.sql_id = s.sql_id
WHERE sl.sofar != sl.totalwork
ORDER BY sl.elapsed_seconds DESC;

5.4 监控并行查询的进度

SELECT sl.sid, sl.serial#, sl.opname,
       sl.sofar, sl.totalwork, sl.units,
       sl.time_remaining, sl.elapsed_seconds,
       sl.qcsid, s2.username AS coordinator_user
FROM v$session_longops sl
LEFT JOIN v$session s2 ON sl.qcsid = s2.sid
WHERE sl.qcsid IS NOT NULL
ORDER BY sl.start_time;

5.5 生成预计完成时间报告

SELECT sid, serial#, opname,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
       TO_CHAR(start_time + (elapsed_seconds + time_remaining)/86400, 
               'YYYY-MM-DD HH24:MI:SS') AS estimated_end_time,
       ROUND(time_remaining/60, 2) AS remaining_minutes,
       ROUND(elapsed_seconds/60, 2) AS elapsed_minutes
FROM v$session_longops
WHERE time_remaining > 0
ORDER BY time_remaining DESC;

5.6 监控RMAN备份恢复进度

SELECT sid, serial#, opname, target,
       sofar, totalwork, units,
       ROUND((sofar/totalwork)*100, 2) AS progress_pct,
       time_remaining, elapsed_seconds,
       message
FROM v$session_longops
WHERE opname LIKE 'RMAN%'  -- RMAN相关操作
ORDER BY start_time;

6. 💎 核心知识点与原理

6.1 长时间操作检测机制

6秒阈值：Oracle自动检测执行时间超过6秒的操作。
进度报告：通过DBMS_APPLICATION_INFO包报告操作进度。
自动跟踪：某些操作（如全表扫描）自动被跟踪。

6.2 支持的操作类型

全表扫描：大型表的全表扫描操作。
索引创建/重建：索引维护操作。
数据加载：SQL*Loader或数据泵操作。
备份恢复：RMAN备份和恢复操作。
统计信息收集：数据库统计信息收集。
并行查询：并行执行的操作。

6.3 进度计算原理

-- 进度计算公式
进度百分比 = (SOFAR / TOTALWORK) * 100
预计剩余时间 = TIME_REMAINING
已用时间 = ELAPSED_SECONDS

6.4 自定义进度报告

-- 使用DBMS_APPLICATION报告自定义操作进度
DECLARE
  l_rindex NUMBER := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
BEGIN
  DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
    rindex      => l_rindex,
    slno        => 1,
    op_name     => 'Custom Operation',
    target      => 1000,
    context     => 0,
    sofar       => 0,
    totalwork   => 1000,
    units       => 'Rows',
    target_desc => 'CUSTOM_TABLE'
  );
  
  -- 更新进度
  FOR i IN 1..1000 LOOP
    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
      rindex    => l_rindex,
      slno      => 1,
      op_name   => 'Custom Operation',
      sofar     => i,
      totalwork => 1000
    );
    -- 执行实际工作
    NULL;
  END LOOP;
END;
/