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

Oracle数据库V$ACCESS视图介绍

原创于 2025-08-14 12:22:11 发布 · 603 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来详细介绍一下 Oracle 19c 中的 V$ACCESS 动态性能视图。

核心作用：
V$ACCESS 视图的核心作用是显示当前哪些数据库会话（SID）正在访问（或曾经尝试访问）哪些数据库对象（如表、视图、过程、序列等），以及这些对象当前在库缓存（Library Cache）中的锁定状态。它是诊断对象锁定（Library Cache Lock/Pin）、权限问题（ORA-00942, ORA-01031）、查找阻塞会话以及理解会话对象依赖关系的宝贵工具。

关键特性：

实时性： 反映库缓存中对象锁定的当前状态。
会话关联： 明确显示哪个会话（SID）持有或正在请求哪个对象的锁。
对象粒度： 定位到具体的模式对象（表、视图、过程、同义词指向的对象等）。
锁定类型： 区分对象是被锁定（LOCKED）还是被固定（PINNED）。
瞬态性： 视图内容在实例生命周期内不断变化，对象被老化（aged out）出库缓存或其上的锁/钉被释放后，相应记录就会消失。数据库重启后内容清空。
库缓存焦点： 主要关注库缓存中与SQL/PLSQL执行相关的对象锁定，不直接反映事务级别的行锁（TX）或表级锁（TM），那些通常在V$LOCK/DBA_LOCKS中查看。

字段详解 (V$ACCESS Columns)：

字段名	数据类型	描述	重要性与示例
`SID`	NUMBER	会话标识符 (Session Identifier)。持有该对象锁或在尝试访问该对象（可能被阻塞）的会话的唯一ID。	核心字段。 `123`, `456`。关联 `V$SESSION.SID`。
`OWNER`	VARCHAR2	对象所有者 (Object Owner)。拥有被访问对象的数据库用户的名称。	核心字段。 `SCOTT`, `HR`, `SYS`。
`OBJECT`	VARCHAR2	对象名称 (Object Name)。被访问的数据库对象的名称（如表名、视图名、过程名、序列名、同义词名等）。	核心字段。 `EMP`, `MY_PROC`, `MY_SEQ`, `PUBLIC_SYN`。
`TYPE`	VARCHAR2	对象类型 (Object Type)。被访问对象的类型。常见值：`TABLE`, `VIEW`, `PROCEDURE`, `FUNCTION`, `PACKAGE`, `PACKAGE BODY`, `SYNONYM`, `SEQUENCE`, `INDEX` 等。	帮助理解访问的是什么类型的对象。`TABLE`, `PROCEDURE`。
`LOCKED_MODE`	NUMBER	锁模式 (Lock Mode Held)。该会话（SID）当前在该对象上持有的库缓存锁的模式。常用值： `0`: None (通常对应 `PINNED` 状态) `1`: Null (NL) - 读兼容 `2`: Row-S (SS) - 子游标共享 `3`: Row-X (SX) - 子游标排他 `4`: Share (S) - 父游标共享 `5`: S/Row-X (SSX) - 父游标共享行排他 `6`: Exclusive (X) - 排他	核心状态字段。指示会话持有锁的强度。`3` (SX), `6` (X)。值越大，互斥性越强。
`REQUEST_MODE`	NUMBER	请求模式 (Requested Lock Mode)。 *该会话（SID）当前正在尝试获取该对象的库缓存锁的模式。* 取值含义同 `LOCKED_MODE`。如果为 `0`，则表示该会话没有正在进行的锁请求（它可能已经持有锁或尚未请求）。	核心状态字段。用于诊断阻塞。非零值表示会话在等待锁。`3`, `6`。
`LOCKED_TOTAL`	NUMBER	持有锁总数 (Total Locks Held)。该会话（SID）当前在该对象上持有的所有类型锁的总计数（可能包括不同模式的锁）。通常关注 `LOCKED_MODE` 和 `REQUEST_MODE` 更直接。	辅助信息。`1`, `2`。
`REQUEST_TOTAL`	NUMBER	请求锁总数 (Total Locks Requested)。该会话（SID）当前在该对象上正在请求的锁的总计数。通常关注 `REQUEST_MODE` 更直接。	辅助信息。`0`, `1`。
`OBJECT_ID`	NUMBER	对象ID (Object Identifier)。被访问对象的唯一数据字典对象ID。可关联 `DBA_OBJECTS.OBJECT_ID`。	用于精确关联数据字典视图。`12345`。
`SESSION_ID`	NUMBER	会话ID (Session Identifier - 同 SID)。与 `SID` 列重复。在 Oracle 19c 中，此列通常与 `SID` 相同。	冗余字段，一般使用 `SID`。
`PROCESS`	VARCHAR2	操作系统进程ID (Operating System Process ID)。与数据库会话关联的操作系统进程标识符（PID）。格式可能因操作系统而异（如 Unix/Linux 的数字 PID，Windows 的 Thread ID）。	关联操作系统进程。`12345` (Unix PID)。

重要说明：

LOCKED_MODE 和 REQUEST_MODE 是诊断阻塞的关键：
- 如果一个会话的 REQUEST_MODE > 0，表示它正在等待获取一个锁。
- 查找另一个会话（阻塞者），它持有 (LOCKED_MODE > 0) 一个与等待者请求模式冲突的锁（例如，等待者请求 X(6)，而持有者持有 S(4) 或 X(6)）。
OBJECT 列显示的是对象名： 如果是同义词，它显示的是同义词名本身，而不是其指向的基础对象名（需要再查 DBA_SYNONYMS）。对于其他对象类型，显示对象本身名称。
库缓存锁模式 (Locked_Mode/Request_Mode)： 这些模式与事务锁（V$LOCK 中的 LMODE/REQUEST）不同。库缓存锁保护的是共享SQL区域、游标结构、对象依赖关系等元数据，确保对象在解析或执行期间结构不被修改或删除。

基表 (Underlying Base X$ Table):

V$ACCESS 的数据主要来源于内存中的动态性能基表 X$KGLLK。
X$KGLLK 存储了关于库缓存锁（Library Cache Locks）的详细信息。V$ACCESS 对这个底层结构进行了更易读的封装和关联（如将地址关联到对象名、会话ID等）。
原理简述： 当会话解析或执行一条SQL/PLSQL语句时：
1. 它需要访问语句中引用的对象（表、视图、过程等）。
2. 为了确保对象结构在操作期间保持一致（例如，防止别人在查询过程中DROP TABLE或ALTER PROCEDURE），会话需要在库缓存中获取这些对象上的锁（Library Cache Lock）和/或钉（Library Cache Pin）。
3. X$KGLLK 跟踪这些锁的获取（LOCKED_MODE）、等待（REQUEST_MODE）以及持有会话（SID）和被锁对象（通过内部句柄关联到 OBJ$ 等数据字典表以获取 OWNER, OBJECT_NAME, TYPE）。
4. V$ACCESS 查询 X$KGLLK 并结合其他内部结构（如 V$SESSION, 对象字典信息）生成用户友好的输出。

主要使用场景：

诊断 ORA-00054: 资源正忙 / ORA-04021: 等待对象锁定超时：
- 当执行 DROP TABLE, ALTER PROCEDURE, ANALYZE TABLE 等DDL操作，或编译对象时遇到这些错误，表示对象被其他会话锁定。
- 使用 V$ACCESS 快速找出是哪个会话（SID）持有（LOCKED_MODE > 0）或正在访问该对象（即使没持有强锁，也可能阻塞DDL需要的X锁），特别是查找持有 EXCLUSIVE(X) 或 SHARE(S) 锁的会话。结合 V$SESSION 找出会话的详细信息（用户、程序、SQL等）。
诊断权限错误 (ORA-00942: 表或视图不存在, ORA-01031: 权限不足)：
- 有时权限问题（尤其是涉及间接权限或同义词）可能与对象锁定状态混淆。
- 检查 V$ACCESS 看会话是否尝试访问了该对象（即使访问失败，也可能在视图中有记录，显示 REQUEST_MODE > 0 且最终失败），或者查看它实际访问的对象是否与预期一致（如同义词指向了错误对象？）。
查找阻塞会话 (Blocking Sessions)：
- 当一个会话因为等待库缓存锁而挂起（在 V$SESSION 中 WAIT_CLASS='Concurrency', EVENT='library cache lock' 或 'library cache pin'），查询 V$ACCESS：
  - 找到等待会话（REQUEST_MODE > 0）。
  - 然后找到持有冲突锁（根据锁兼容性矩阵）的会话（LOCKED_MODE > 0 且 LOCKED_MODE 与等待者的 REQUEST_MODE 冲突）。
- 这是定位库缓存锁争用的核心视图。
理解会话依赖关系：
- 查看某个特定会话（SID）当前正在使用或依赖哪些数据库对象。这对于分析复杂应用的行为、进行影响评估（如修改对象前）或审计很有用。
验证对象是否被缓存/使用：
- 如果一个对象出现在 V$ACCESS 中，说明它最近被某个会话访问过，并且其元数据（游标、依赖信息等）很可能还驻留在库缓存中。但这不表示数据块在Buffer Cache中。

常用查询 SQL 示例：

查看所有当前会话的访问和锁定信息：

SELECT s.sid, s.serial#, s.username, s.osuser, s.program, s.status,
       a.owner, a.object, a.type,
       a.locked_mode, a.request_mode
FROM v$access a
JOIN v$session s ON a.sid = s.sid
ORDER BY a.sid, a.owner, a.object;

查找特定对象（如表 SCOTT.EMP）被哪些会话访问/锁定：

SELECT s.sid, s.serial#, s.username, s.osuser, s.program, s.status,
       a.locked_mode, a.request_mode
FROM v$access a
JOIN v$session s ON a.sid = s.sid
WHERE a.owner = 'SCOTT' AND a.object = 'EMP'
ORDER BY a.sid;

查找持有锁（LOCKED_MODE > 0）的会话及其对象：

SELECT s.sid, s.serial#, s.username, s.osuser, s.program,
       a.owner, a.object, a.type, a.locked_mode
FROM v$access a
JOIN v$session s ON a.sid = s.sid
WHERE a.locked_mode > 0
ORDER BY a.locked_mode DESC, a.sid; -- 按锁强度降序排

查找正在等待锁（REQUEST_MODE > 0）的会话及其被阻塞的对象：

SELECT s.sid, s.serial#, s.username, s.osuser, s.program, s.status,
       a.owner, a.object, a.type, a.request_mode
FROM v$access a
JOIN v$session s ON a.sid = s.sid
WHERE a.request_mode > 0
ORDER BY a.request_mode DESC, a.sid; -- 按请求强度降序排

诊断阻塞链 (结合 V$SESSION 等待事件)：

-- 先找到等待 'library cache lock' 或 'library cache pin' 的会话
SELECT sid, serial#, username, event, state, seconds_in_wait
FROM v$session
WHERE wait_class = 'Concurrency'
  AND (event LIKE 'library cache%lock' OR event LIKE 'library cache%pin'); -- 注意 Oracle 事件名可能有空格差异

-- 假设找到等待会话 SID = &blocked_sid
-- 查看这个等待会话在请求哪个对象的什么锁
SELECT owner, object, type, request_mode
FROM v$access
WHERE sid = &blocked_sid
  AND request_mode > 0;

-- 假设查到它在请求 SCOTT.EMP 上的 X 锁 (REQUEST_MODE=6)
-- 查找谁持有 SCOTT.EMP 上的锁（特别是 S/X 或更高模式）
SELECT s.sid, s.serial#, s.username, s.program, a.locked_mode
FROM v$access a
JOIN v$session s ON a.sid = s.sid
WHERE a.owner = 'SCOTT'
  AND a.object = 'EMP'
  AND a.locked_mode > 0
  AND a.locked_mode IN (4, 5, 6) -- S, SSX, X 可能与 X 请求冲突
ORDER BY a.locked_mode DESC;

查看特定会话 (SID = &your_sid) 访问了哪些对象：

SELECT owner, object, type, locked_mode, request_mode
FROM v$access
WHERE sid = &your_sid
ORDER BY owner, object;

重要提示：

V$ACCESS 显示的是库缓存级别的锁和访问信息，不是事务级别的行锁或表锁（TM锁）。
视图内容是瞬态的，只反映查询时刻的状态。
频繁查询 V$ACCESS 本身会对系统产生一些开销，在高并发或问题诊断时需注意。
结合 V$SESSION, V$SQL, V$LOCK(事务锁), DBA_DDL_LOCKS(提供类似 V$ACCESS 的信息但结构不同) 等视图能获得更全面的诊断信息。
在 RAC 环境中，V$ACCESS 通常只显示本实例上的会话访问信息。需要使用 GV$ACCESS (Global View) 来查看所有实例的信息。查询 GV$ACCESS 时，注意 INST_ID 列表示实例号。