面试宝典:介绍下Oracle数据库动态性能视图 V$SESSION

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

1. ✨ 视图概述与作用

V$SESSION 是 Oracle 数据库中最重要、最基础也是使用最频繁的动态性能视图之一。它提供了当前所有会话（Session）的实时状态信息，每个连接到数据库实例的用户进程、后台进程或系统进程都会在此视图中有一条对应的记录。

• 核心作用：提供数据库会话级别的全方位监控和诊断能力。它是DBA实时了解数据库活动状态、诊断性能问题、排查锁冲突、分析资源消耗和进行会话管理的主要入口。
• 重要性：几乎所有实时性能诊断和会话管理操作都离不开 V$SESSION 视图。它就像数据库活动的"实时监控大屏"，展示了谁在连接、在做什么、遇到了什么问题以及消耗了多少资源。

2. 🧐 主要应用场景

• 实时性能诊断：识别长时间运行或消耗大量资源的会话和SQL语句。
• 锁冲突与阻塞分析：查找导致锁等待和阻塞的源头会话。
• 会话管理与控制：监控用户会话活动，必要时终止异常或恶意会话。
• 应用行为分析：通过模块（MODULE）、动作（ACTION）等信息了解应用程序的行为模式。
• 连接池监控：监控连接池的使用情况和连接状态。
• 审计与安全监控：跟踪用户登录信息、访问来源和操作行为。

3. 📋 V$SESSION 字段详解

V$SESSION 包含大量字段（Oracle 19c中超过100个），下面分类详解最重要和最常用的字段。

3.1 会话标识信息

字段名	数据类型	描述
SID	NUMBER	会话标识符（Session Identifier）。在实例内唯一标识一个会话，是会话的主要ID。
SERIAL#	NUMBER	会话序列号。与SID共同唯一标识一个会话。如果会话断开重连，SID可能重用，但SERIAL#会递增，用于确保操作（如KILL）的对象是正确的会话。
AUDSID	NUMBER	审计会话ID。用于会话审计，在数据库启动周期内唯一。
USER#	NUMBER	用户标识号。与DBA_USERS.USER_ID对应。
USERNAME	VARCHAR2(30)	Oracle用户名。对于后台进程，值为NULL。

3.2 会话状态信息

字段名	数据类型	描述
STATUS	VARCHAR2(8)	会话状态。主要值：ACTIVE(正在执行SQL), INACTIVE(空闲), KILLED(已被标记终止), SNIPED(被PMON进程清理), CACHED(在共享服务器中缓存)。
SERVER	VARCHAR2(9)	服务器类型。主要值：DEDICATED(专用服务器), SHARED(共享服务器), PSEUDO(伪会话), NONE(无)。
SCHEMANAME	VARCHAR2(30)	模式名称。通常与USERNAME相同，除非使用了ALTER SESSION SET CURRENT_SCHEMA。
OSUSER	VARCHAR2(30)	操作系统用户名。启动数据库会话的操作系统用户。
MACHINE	VARCHAR2(64)	客户端机器名。连接来自的计算机名称。
TERMINAL	VARCHAR2(30)	客户端终端标识符。
PROGRAM	VARCHAR2(48)	客户端程序名。如sqlplus.exe, JDBC Thin Client等。
TYPE	VARCHAR2(10)	会话类型。USER(用户会话), BACKGROUND(后台进程)。

3.3 等待事件信息

字段名	数据类型	描述
EVENT	VARCHAR2(64)	当前等待事件。如果会话处于等待状态，显示正在等待的事件名称；如果未等待，显示空或NULL。
STATE	VARCHAR2(19)	等待状态。WAITING(正在等待), WAITED UNKNOWN TIME(等待过但时间未知), WAITED SHORT TIME(短暂等待过)。
WAIT_TIME	NUMBER	等待时间。含义取决于STATE：如果STATE=‘WAITING’, 值为0；如果STATE=‘WAITED SHORT TIME’, 值为最后一次等待的时间(厘秒)。
SECONDS_IN_WAIT	NUMBER	已在当前事件中等待的时间（秒）。
P1, P2, P3	NUMBER	等待事件参数。具体含义取决于EVENT字段，提供等待事件的详细信息。
P1TEXT, P2TEXT, P3TEXT	VARCHAR2(64)	等待事件参数说明。解释P1, P2, P3参数的含义。

3.4 资源消耗信息

字段名	数据类型	描述
LOGON_TIME	DATE	登录时间。会话建立的时间。
LAST_CALL_ET	NUMBER	最后一次调用经历时间（秒）。对于ACTIVE会话，是当前操作已执行的时间；对于INACTIVE会话，是自最后一次操作结束以来的时间。
SQL_ID	VARCHAR2(13)	当前正在执行的SQL的SQL_ID。可与V$SQL关联获取SQL文本。
SQL_CHILD_NUMBER	NUMBER	当前SQL的子游标号。
SQL_EXEC_ID	NUMBER	SQL执行标识符。唯一标识一次SQL执行。
SQL_EXEC_START	DATE	当前SQL开始执行的时间。
PREV_SQL_ID	VARCHAR2(13)	前一条执行的SQL的SQL_ID。
ROW_WAIT_OBJ#	NUMBER	会话正在等待的行的对象ID。可与DBA_OBJECTS关联。
ROW_WAIT_FILE#	NUMBER	会话正在等待的行的文件号。
ROW_WAIT_BLOCK#	NUMBER	会话正在等待的行的块号。
ROW_WAIT_ROW#	NUMBER	会话正在等待的行号。
BLOCKING_SESSION_STATUS	VARCHAR2(11)	阻塞会话状态。VALID(有有效阻塞者), NO HOLDER(无阻塞者), UNKNOWN(未知), NOT IN WAIT(不在等待)。
BLOCKING_INSTANCE	NUMBER	阻塞会话所在的实例ID（RAC环境）。
BLOCKING_SESSION	NUMBER	阻塞当前会话的会话SID。

3.5 应用追踪信息

字段名	数据类型	描述
MODULE	VARCHAR2(48)	应用程序模块名。由应用通过DBMS_APPLICATION_INFO.SET_MODULE设置。
ACTION	VARCHAR2(32)	应用程序动作名。由应用通过DBMS_APPLICATION_INFO.SET_ACTION设置。
CLIENT_INFO	VARCHAR2(64)	客户端信息。由应用通过DBMS_APPLICATION_INFO.SET_CLIENT_INFO设置。
CLIENT_IDENTIFIER	VARCHAR2(64)	客户端标识符。用于精细审计和资源管理。
SERVICE_NAME	VARCHAR2(64)	服务名。会话连接使用的数据库服务名。

3.6 多租环境相关信息

字段名	数据类型	描述
CON_ID	NUMBER	容器ID。在多租户环境中，标识会话所属的容器。
ECID	VARCHAR2(64)	执行上下文ID。用于Oracle中间件层的请求追踪。

4. 🔗 相关视图与基表

4.1 相关性能视图

• V$PROCESS：提供与会话相关的操作系统进程信息。
• V$SQL\ast \ast 、\ast \ast V$SQLAREA：提供SQL语句的详细信息，通过SQL_ID关联。
• V$SESSIO{N}_{W}AIT\ast \ast 、\ast \ast V$SESSION_EVENT：提供会话等待事件的详细历史信息。
• V$SESSION_LONGOPS：显示长时间运行操作的进度。
• V$LOCK：提供详细的锁信息。
• V$TRANSACTION：提供活动事务的信息。

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

V$SESSION 的数据来源于 Oracle SGA 中的内存数据结构。

• 底层原理：

  1. 会话控制块：Oracle 为每个会话在内存中维护一个会话控制块（Session Control Block） 数据结构，存储了会话的所有状态信息。
  2. 实时更新：会话的各种状态（如执行的SQL、等待的事件、资源消耗等）会实时更新到其会话控制块中。
  3. 内存存储：所有会话控制块组织在内存中的 X$ 表（主要是 X$KSUSE）中，这是一个未公开的内部结构。
  4. 视图暴露：V$SESSION 视图通过 SQL 层查询 X$KSUSE 和其他相关X$表，将复杂的二进制内存数据结构转换为可读的字段信息。

• 数据特性：

  • 实时性：数据是实时更新的，反映了会话的当前状态。
  • 实例范围：只显示当前实例的会话信息（RAC环境中）。
  • 动态性：会话连接和断开时，记录会自动添加和删除。

5. ⚙️ 常用查询SQL

5.1 查看所有活动会话及执行的SQL

SELECT s.sid, s.serial#, s.username, s.status, s.osuser, s.machine,
       s.program, s.sql_id, s.event, q.sql_text
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE' AND s.type = 'USER';

5.2 查找阻塞和等待锁的会话

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )' AS blocker,
       s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' )' AS waiter,
       s2.sql_id, s2.event, lo.type, lo.id1, lo.id2
FROM v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON l2.sid = s2.sid
LEFT JOIN v$locked_object lo ON s2.sid = lo.session_id
WHERE l1.block = 1 AND l2.request > 0;

5.3 按资源消耗排序会话

SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
       s.last_call_et AS seconds, s.event, p.spid AS os_pid,
       (SELECT SUM(value) FROM v$sesstat ss 
        JOIN v$statname sn ON ss.statistic# = sn.statistic# 
        WHERE ss.sid = s.sid AND sn.name LIKE '%session %memory%') AS memory
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.type = 'USER'
ORDER BY s.last_call_et DESC;

5.4 查看指定用户的会话信息

SELECT sid, serial#, status, osuser, machine, program,
       TO_CHAR(logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
       last_call_et, sql_id, event, state
FROM v$session
WHERE username = 'YOUR_USERNAME'
ORDER BY logon_time DESC;

5.5 监控会话等待事件

SELECT s.sid, s.serial#, s.username, s.event, s.wait_time, s.seconds_in_wait,
       s.state, s.p1, s.p2, s.p3, s.p1text, s.p2text, s.p3text
FROM v$session s
WHERE s.wait_class <> 'Idle' AND s.status = 'ACTIVE'
ORDER BY s.seconds_in_wait DESC;

5.6 终止特定会话

-- 首先查询会话信息
SELECT sid, serial#, username, status, program 
FROM v$session 
WHERE username = 'PROBLEM_USER' OR machine = 'PROBLEM_MACHINE';

-- 然后使用ALTER SYSTEM KILL SESSION终止会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

6. 💎 核心知识点与原理

1. 会话生命周期管理：Oracle 为每个连接创建和管理会话结构，包括内存分配、状态跟踪和资源清理。
2. 等待事件接口：Oracle 使用等待事件模型来跟踪会话遇到的各种资源争用和等待情况。
3. SQL追踪机制：通过SQL_ID等字段，Oracle 能够将会话活动与共享池中的SQL语句关联起来。
4. 锁与并发控制：V$SESSION 提供锁等待和阻塞关系的可见性，是诊断并发问题的重要工具。
5. 资源管理集成：会话信息与Resource Manager集成，支持基于会话属性的资源分配策略。
6. 多租户支持：在CDB环境中，V$SESSION 支持容器级别的会话监控和管理。

7. 📝 总结

V$SESSION 视图是Oracle数据库性能监控和故障诊断的核心工具，它提供了：

• 全面的会话可见性：实时了解所有会话的状态、活动和资源消耗。
• 精细的诊断能力：深入到SQL级别、等待事件级别的性能分析。
• 有效的管理手段：支持会话监控、分析和控制操作。
• 丰富的集成信息：与SQL、等待事件、锁、事务等多个维度关联。

掌握 V$SESSION 视图的使用是Oracle DBA必备的核心技能，几乎所有的实时性能诊断和会话管理场景都离不开这个视图。通过熟练使用其中的字段和相关的SQL查询，DBA能够快速定位和解决数据库性能问题，确保数据库系统的稳定高效运行。

欢迎关注我的公众号《IT小Chen》