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

原创于 2025-09-05 12:13:03 发布 · 923 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来详细探讨 Oracle 19C 数据库中一个非常重要且常用的动态性能视图：V$SQL_SHARED_CURSOR。这个视图是诊断游标无法共享（即导致子游标数量激增，引发“ORA-04031”错误或共享池争用）问题的核心工具。

我将严格按照您的要求，从作用、使用场景、字段含义、相关视图、基表、底层原理到常用SQL进行全方位详解。

1. 视图概述与核心作用

V$SQL_SHARED_CURSOR 视图用于解释一个 SQL 语句为何拥有多个子游标（Child Cursors）。

在 Oracle 中，一个父游标（Parent Cursor，由 SQL_ID 标识）下可以有一个或多个子游标（Child Cursor，由 SQL_ID 和 CHILD_NUMBER 共同标识）。理想情况下，相同的 SQL 语句应该共享同一个子游标。但现实中，很多因素会导致无法共享，从而为同一份 SQL 文本生成不同的子游标。

该视图的核心作用就是通过一系列 'Y'/'N' 的标志字段，精准地定位导致无法共享的具体原因。

2. 使用场景

此视图主要用于以下性能诊断和问题排查场景：

诊断共享池问题：当共享池（Shared Pool）出现争用、空间不足或遭遇“ORA-04031: unable to allocate shared memory”错误时，用于检查是否因大量非共享的子游标导致。
优化应用设计：确认应用是否使用了绑定变量（Bind Variables），或者是否存在不当的客户端设置（如每次执行都改变 MODULE、ACTION），导致本应共享的游标无法共享。
兼容性排查：在升级或迁移后，检查是否有 SQL 因优化器特性、参数更改等原因而无法共享原有的执行计划。
解析调用过高：发现系统硬解析（Hard Parse）或软解析（Soft Parse）次数异常时，用于定位根源。

3. 字段含义详解

V$SQL_SHARED_CURSOR 的字段绝大多数是 VARCHAR2(1) 类型（‘Y’ 或 ‘N’），‘Y’ 表示是该原因导致了子游标无法与另一个子游标共享。关键字段如下表所示：

字段名称	数据类型	含义详解
`SQL_ID`	VARCHAR2(13)	父游标的 SQL 标识符。
`CHILD_NUMBER`	NUMBER	该子游标的编号。与 `V$SQL` 中的 `CHILD_NUMBER` 对应。
`ADDRESS`	RAW(4 \| 8)	当前子游标的库缓存对象句柄地址。用于关联 `V$SQLAREA` 等视图。
`REASON`	VARCHAR2(1000)	（Oracle 12cR2+）一个人类可读的、解释为何不能共享的原因描述。这是最重要的诊断字段。
`UNBOUND_CURSOR`	VARCHAR2(1)	`Y` 表示当前子游标是一个未完全绑定的游标（通常用于中间状态）。
`TRANSLATION_MISMATCH`	VARCHAR2(1)	`Y` 表示涉及跨不同语境的游标转换（如异构数据库连接），导致不匹配。
`OPTIMIZER_MISMATCH`	VARCHAR2(1)	`Y` 表示两个子游标的优化器模式（如 ALL_ROWS vs. FIRST_ROWS）不同。
`OUTLINE_MISMATCH`	VARCHAR2(1)	`Y` 表示存储大纲（Stored Outline）或 SQL 计划基线（SPM Baseline）不匹配。
`STATS_ROW_MISMATCH`	VARCHAR2(1)	`Y` 表示对象的统计信息已发生变更（如表重新分析），导致需要新计划。
`LITERAL_MISMATCH`	VARCHAR2(1)	`Y` 表示 SQL 中使用了字面量（而非绑定变量），且字面值不同。
`SEC_DEPTH_MISMATCH`	VARCHAR2(1)	`Y` 表示安全深度不匹配（与细粒度审计等功能相关）。
`EXPLAIN_PLAN_CURSOR`	VARCHAR2(1)	`Y` 表示该游标是为 `EXPLAIN PLAN` 语句而生成，不应被共享。
`BUFFERED_DML_MISMATCH`	VARCHAR2(1)	`Y` 表示缓冲 DML 状态不匹配。
`PDML_ENV_MISMATCH`	VARCHAR2(1)	`Y` 表示并行 DML (PDML) 环境不匹配。
`INST_DRTLD_MISMATCH`	VARCHAR2(1)	`Y` 表示实例可引导（Instance Draining）状态不匹配。
`SLAVE_QC_MISMATCH`	VARCHAR2(1)	`Y` 表示在并行查询中，一个子游标是查询协调器（QC）的，另一个是从属进程的。
`TYPECHECK_MISMATCH`	VARCHAR2(1)	`Y` 表示在类型检查阶段发现不匹配（通常与绑定变量类型或长度有关）。
`AUTH_CHECK_MISMATCH`	VARCHAR2(1)	`Y` 表示两个会话的权限不同，导致执行计划需要区分。
`BIND_MISMATCH`	VARCHAR2(1)	`Y` 表示绑定变量的元数据（如数据类型、长度）不匹配。
`DESCRIBE_MISMATCH`	VARCHAR2(1)	`Y` 表示描述操作（Describe）的结果不匹配。
`LANGUAGE_MISMATCH`	VARCHAR2(1)	`Y` 表示会话的 `NLS_LANGUAGE` 设置不同。
`TERRITORY_MISMATCH`	VARCHAR2(1)	`Y` 表示会话的 `NLS_TERRITORY` 设置不同。
`EDITION_MISMATCH`	VARCHAR2(1)	`Y` 表示会话使用的版本（Edition）不同（与 Edition-Based Redefinition 相关）。

注意：实际字段远多于上表，但以上列出了最常见和最重要的原因字段。从 Oracle 12cR2 开始，REASON 字段极大地简化了诊断过程。

4. 相关视图与基表

相关视图：
- V$SQL / V$SQLAREA：获取 SQL 的统计信息（执行次数、逻辑读等）。通过 (SQL_ID, CHILD_NUMBER) 或 ADDRESS 与 V$SQL_SHARED_CURSOR 关联。
- V$SQLSTATS：提供高性能的 SQL 聚合统计信息。
- V$SESSION：查看当前正在执行 SQL 的会话信息。
基表：
V$SQL_SHARED_CURSOR 是一个动态性能视图，其数据来源于 X$KGLCURSOR_CHILD 或类似的 X$ 表。X$ 表是 Oracle 内部的内存结构接口，不建议用户直接查询。

5. 底层原理与工作机制

游标共享的基本原理：
Oracle 为了共享游标，会进行严格的检查。当一条新 SQL 被提交时，Oracle 会计算其 SQL_ID，然后在库缓存（Library Cache）中查找匹配的父游标。
如果找到父游标，它会遍历其下的所有子游标，检查执行环境（Execution Context）是否完全相同。
V$SQL_SHARED_CURSOR 的作用时机：
当 Oracle 在遍历子游标后发现无法共享（即执行环境有差异）时，它就会创建一个新的子游标。
与此同时，Oracle 会在内部设置一系列标志位，精确记录是哪个或哪些差异导致了这次“不共享”。V$SQL_SHARED_CURSOR 视图就是这些标志位的外部映射。
数据流向：
Library Cache Manager -> 内部内存结构 (X$KGLCURSOR_CHILD 等) -> GV_$SQL_SHARED_CURSOR -> V$SQL_SHARED_CURSOR。
这些数据是实时的，存在于内存中，实例关闭后即消失。

6. 常用查询 SQL

查询 1：查找不能共享的 SQL 及其原因（使用 REASON 字段）

此查询适用于 Oracle 12cR2 及以上版本，是最直接有效的方法。

SELECT s.sql_id, s.child_number, s.reason
FROM v$sql_shared_cursor s
WHERE s.sql_id IN (
    SELECT sql_id
    FROM v$sql
    GROUP BY sql_id
    HAVING COUNT(*) > 5 -- 查找拥有超过5个子游标的SQL
)
AND s.reason IS NOT NULL
ORDER BY s.sql_id, s.child_number;

查询 2：详细分析特定 SQL 的所有不共享原因

选择一个你怀疑的 SQL_ID 进行深入分析。

SELECT sql_id, child_number,
       unbound_cursor, optimizer_mismatch, outline_mismatch,
       stats_row_mismatch, bind_mismatch, literal_mismatch,
       auth_check_mismatch, translation_mismatch
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id' -- 替换为具体的SQL_ID， 例如 '8p8annhqjy64s'
ORDER BY child_number;

查询 3：结合 V$SQL 获取SQL文本和统计信息

这是一个更全面的诊断查询。

SELECT curs.sql_id,
       curs.child_number,
       sq.sql_text,
       sq.executions,
       curs.reason, -- 主要原因
       curs.bind_mismatch, -- 绑定变量不匹配
       curs.optimizer_mismatch -- 优化器模式不匹配
FROM v$sql_shared_cursor curs
JOIN v$sql sq ON (curs.sql_id = sq.sql_id AND curs.child_number = sq.child_number)
WHERE sq.sql_id = '&sql_id'
AND sq.child_address = curs.address;

查询 4：按原因分类统计，找到系统中最主要的游标不共享问题

SELECT reason, COUNT(*) AS cursor_count
FROM v$sql_shared_cursor
WHERE reason IS NOT NULL
GROUP BY reason
ORDER BY cursor_count DESC;

7. 关键知识点总结

游标：游标是库缓存中存储已解析SQL语句及其执行计划的内存结构。
父游标与子游标：
- 父游标：由 SQL_ID 标识，主要存储 SQL 文本。
- 子游标：由 (SQL_ID, CHILD_NUMBER) 标识，存储执行计划、绑定变量信息、优化器环境等。真正执行的是子游标。
硬解析 vs. 软解析：
- 硬解析： SQL 是全新的，需要创建父游标和子游标，进行语法语义检查、生成执行计划。代价极高。
- 软解析：找到可共享的子游标，直接使用现有的执行计划。代价很低。
为什么不能共享：任何导致执行环境变化的因素都会阻止共享，例如：
- 绑定变量类型/长度不同 (BIND_MISMATCH)
- NLS 设置不同 (LANGUAGE_MISMATCH, TERRITORY_MISMATCH)
- 优化器模式不同 (OPTIMIZER_MISMATCH)
- 对象统计信息已更新 (STATS_ROW_MISMATCH)
- 没有使用绑定变量 (LITERAL_MISMATCH)
- 权限差异 (AUTH_CHECK_MISMATCH)