如 何 解 决 ORA-04031 错 误 翻 译 :Fenng 文 章 内 容 1. 和 共 享 池 (shared pool) 相 关 的 实 例 参 数 2. 诊 断 ORA-04031 错 误 3. 解 决 ORA-04031 错 误 已 知 的 Oracle BUG 共 享 池 碎 片 o V$SQLAREA 视 图 o X$KSMLRU 视 图 小 的 共 享 池 尺 寸 o 库 高 速 缓 冲 (library cache) 命 中 率 o 共 享 池 大 小 的 计 算 4. 对 ORA-04031 的 高 级 分 析 诊 断 并 解 决 ORA-04031 错 误 对 于 大 多 数 应 用 来 说, 共 享 池 的 大 小 对 于 Oracle 性 能 来 说 都 是 很 重 要 的 共 享 池 中 保 存 数 据 字 典 高 速 缓 冲 和 完 全 解 析 或 编 译 的 的 PL/SQL 块 和 SQL 语 句 当 我 们 在 共 享 池 中 试 图 分 配 大 片 的 连 续 内 存 失 败 的 时 候,Oracle 首 先 刷 新 池 中 当 前 没 使 用 的 所 有 对 象, 使 空 闲 内 存 块 合 并 如 果 仍 然 没 有 足 够 大 单 个 的 大 块 内 存 满 足 请 求, 就 会 产 生 ORA-04031 错 误 当 这 个 错 误 出 现 的 时 候 你 得 到 的 错 误 信 息 如 下 : Error: ORA 4031 Text: unable to allocate %s bytes of shared memory (%s,%s,%s) Cause: More shared memory is needed than was allocated in the shared pool. Action: Either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the init.ora parameter "shared_pool_size". 1. 共 享 池 相 关 的 实 例 参 数 在 继 续 之 前, 理 解 下 面 的 实 例 参 数 是 很 重 要 的 :
SHARED_POOL_SIZE 这 个 参 数 指 定 了 共 享 池 的 大 小, 单 位 是 字 节 可 以 接 受 数 字 值 或 者 数 字 后 面 跟 上 后 缀 "K" 或 "M" "K" 代 表 千 字 节, "M" 代 表 兆 字 节 SHARED_POOL_RESERVED_SIZE 指 定 了 为 共 享 池 内 存 保 留 的 用 于 大 的 连 续 请 求 的 共 享 池 空 间 当 共 享 池 碎 片 强 制 使 Oracle 查 找 并 释 放 大 块 未 使 用 的 池 来 满 足 当 前 的 请 求 的 时 候, 这 个 参 数 和 SHARED_POOL_RESERVED_MIN_ALLOC 参 数 一 起 可 以 用 来 避 免 性 能 下 降 这 个 参 数 理 想 的 值 应 该 大 到 足 以 满 足 任 何 对 保 留 列 表 中 内 存 的 请 求 扫 描 而 无 需 从 共 享 池 中 刷 新 对 象 既 然 操 作 系 统 内 存 可 以 限 制 共 享 池 的 大 小, 一 般 来 说, 你 应 该 设 定 这 个 参 数 为 SHARED_POOL_SIZE 参 数 的 10% 大 小 SHARED_POOL_RESERVED_MIN_ALLOC 这 个 参 数 的 值 控 制 保 留 内 存 的 分 配 如 果 一 个 足 够 尺 寸 的 大 块 内 存 在 共 享 池 空 闲 列 表 中 没 能 找 到, 内 存 就 从 保 留 列 表 中 分 配 一 块 比 这 个 值 大 的 空 间 默 认 的 值 对 于 大 多 数 系 统 来 说 都 足 够 了 如 果 你 加 大 这 个 值, 那 么 Oracle 服 务 器 将 允 许 从 这 个 保 留 列 表 中 更 少 的 分 配 并 且 将 从 共 享 池 列 表 中 请 求 更 多 的 内 存 这 个 参 数 在 Oracle 8i 是 隐 藏 的 2. 诊 断 ORA-04031 错 误 ORA-04031 错 误 通 常 是 因 为 库 高 速 缓 冲 中 或 共 享 池 保 留 空 间 中 的 碎 片 在 加 大 共 享 池 大 小 的 时 候 考 虑 调 整 应 用 使 用 共 享 的 SQL 并 且 调 整 如 下 的 参 数 : SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, SHARED_POOL_RESERVED_MIN_ALLOC. 首 先 判 定 是 否 ORA-04031 错 误 是 由 共 享 池 保 留 空 间 中 的 库 高 速 缓 冲 的 碎 片 产 生 的 提 交 下 的 查 询 : SELECT free_space, avg_free_size,used_space, avg_used_size,request_failures, last_failure_size FROM v$shared_pool_reserved; 如 果 : REQUEST_FAILURES > 0 并 且 LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC 那 么 ORA-04031 错 误 就 是 因 为 共 享 池 保 留 空 间 缺 少 连 续 空 间 所 致 要 解 决 这 个 问 题, 可 以 考 虑 加 大 SHARED_POOL_RESERVED_MIN_ALLOC 来 降 低 缓 冲 进 共 享 池 保 留 空 间 的 对 象 数 目, 并 增 大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 来 加 大 共 享 池 保 留 空 间 的 可 用 内 存 如 果 : REQUEST_FAILURES > 0 并 且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
或 者 REQUEST_FAILURES 等 于 0 并 且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 那 么 是 因 为 在 库 高 速 缓 冲 缺 少 连 续 空 间 导 致 ORA-04031 错 误 第 一 步 应 该 考 虑 降 低 SHARED_POOL_RESERVED_MIN_ALLOC 以 放 入 更 多 的 对 象 到 共 享 池 保 留 空 间 中 并 且 加 大 SHARED_POOL_SIZE 3. 解 决 ORA-04031 错 误 ORACLE BUG 要 解 决 这 个 错 误, 进 行 的 诊 断 的 第 一 步 是 在 你 的 平 台 上 使 用 最 新 的 补 丁 集 大 多 数 的 ORA-04031 错 误 都 和 BUG 相 关, 可 以 通 过 使 用 这 些 补 丁 来 避 免 下 面 表 中 总 结 和 和 这 个 错 误 相 关 的 最 常 见 的 BUG, 可 能 的 环 境 和 修 补 这 个 问 题 的 补 丁 BUG 描 述 Workaround Fixed <Bug:1397603> ORA-4031 / SGA memory leak of 8172, PERMANENT memory occurs for _db_handles_cached = 0 901 buffer handles. <Bug:1640583> ORA-4031 due to leak / cache buffer 8171, chain contention from AND-EQUAL Not available 901 access INSERT AS SELECT statements may <Bug:1318267> not be shared when they should be _SQLEXEC_PROGRESSION_ 8171, if TIMED_STATISTICS. It can lead COST=0 8200 to ORA-4031 <Bug:1193003> 8162, Cursors may not be shared in 8.1 Not available 8170, when they should be 901 共 享 池 结 构 中 的 一 些 BUG 会 引 起 这 个 错 误, 不 过 通 常 大 量 的 共 享 的 SQL/PLSQL 语 句 也 会 引 起 这 个 错 误 一 旦 打 过 了 最 新 的 补 丁, 在 遇 到 这 个 问 题 的 时 候 我 们 强 烈 推 荐 调 整 数 据 库 和 应 用 要 得 到 已 知 的 BUG 的 完 整 信 息, 可 以 参 考 :<Note:62143.1>: Main issues affecting the Shared Pool on Oracle 7, Oracle8 and Oracle8i 共 享 池 碎 片 每 一 次, 需 要 被 执 行 的 SQL 或 者 PL/SQL 语 句 的 解 析 形 式 载 入 共 享 池 中 都 需 要 一 块 特 定 的 连 续 的 空 间 数 据 库 要 扫 描 的 第 一 个 资 源 就 是 共 享 池 中 的 空 闲 可 用 内 存 一 旦 空 闲 内 存 耗 尽, 数 据 库 要 查 找 一 块 已 经 分 配 但 还 没 使 用 的 内 存 准 备 重 用 如 果 这 样 的 确 切 尺 寸 的 大 块 内 存 不 可 用, 就 继 续 按 照 如 下 标 准 寻 找 :
大 块 (chunk) 大 小 比 请 求 的 大 小 大 空 间 是 连 续 的 大 块 内 存 是 可 用 的 ( 而 不 是 正 在 使 用 的 ) 这 样 大 块 的 内 存 被 分 开, 剩 余 的 添 加 到 相 应 的 空 闲 空 间 列 表 中 当 数 据 库 以 这 种 方 式 操 作 一 段 时 间 之 后, 共 享 池 结 构 就 会 出 现 碎 片 当 共 享 池 存 在 碎 片 的 问 题, 分 配 一 片 空 闲 的 空 间 就 会 花 费 更 多 的 时 间, 数 据 库 性 能 也 会 下 降 ( 整 个 操 作 的 过 程 中,"chunk allocation" 被 一 个 叫 做 "shared pool latch" 的 闩 所 控 制 ) 或 者 是 出 现 ORA-04031 错 误 errors ( 在 数 据 库 不 能 找 到 一 个 连 续 的 空 闲 内 存 块 的 时 候 ) 参 考 <Note:61623.1>: 可 以 得 到 关 于 共 享 池 碎 片 的 详 细 讨 论 如 果 SHARED_POOL_SIZE 足 够 大, 大 多 数 的 ORA-04031 错 误 都 是 由 共 享 池 中 的 动 态 SQL 碎 片 导 致 的 可 能 的 原 因 如 下 : 非 共 享 的 SQL 生 成 不 必 要 的 解 析 调 用 ( 软 解 析 ) 没 有 使 用 绑 定 变 量 要 减 少 碎 片 的 产 生 你 需 要 确 定 是 前 面 描 叙 的 几 种 可 能 的 因 素 可 以 采 取 如 下 的 一 些 方 法, 当 然 不 只 局 限 于 这 几 种 : 应 用 调 整, 数 据 库 调 整 或 者 实 例 参 数 调 整 请 参 考 <Note:62143.1>, 描 述 了 所 有 的 这 些 细 节 内 容 这 个 注 释 还 包 括 了 共 享 池 如 何 工 作 的 细 节 下 面 的 视 图 有 助 于 你 标 明 共 享 池 中 非 共 享 的 SQL/PLSQL: V$SQLAREA 视 图 这 个 视 图 保 存 了 在 数 据 库 中 执 行 的 SQL 语 句 和 PL/SQL 块 的 信 息 下 面 的 SQL 语 句 可 以 显 示 给 你 带 有 literal 的 语 句 或 者 是 带 有 绑 定 变 量 的 语 句 : SELECT substr(sql_text,1,40) "SQL", count(*), sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30 ORDER BY 2; 注 意 : 语 句 Having 中 的 "30" 数 值 可 以 根 据 需 要 调 整 以 得 到 更 为 详 细 的 信 息
X$KSMLRU 视 图 有 一 个 固 定 表 x$ksmlru 跟 踪 共 享 池 中 导 致 其 它 对 象 换 出 (age out) 的 应 用 这 个 固 定 表 可 以 用 来 标 记 是 什 么 导 致 了 大 的 应 用 如 果 很 多 对 象 在 共 享 池 中 都 被 阶 段 性 的 刷 新 可 能 导 致 响 应 时 间 问 题 并 且 有 可 能 在 对 象 重 载 入 共 享 池 中 的 时 候 导 致 库 高 速 缓 冲 闩 竞 争 问 题 关 于 这 个 x$ksmlru 表 的 一 个 不 寻 常 的 地 方 就 是 如 果 有 人 从 表 中 选 取 内 容 这 个 表 的 内 容 就 会 被 擦 除 这 样 这 个 固 定 表 只 存 储 曾 经 发 生 的 最 大 的 分 配 这 个 值 在 选 择 后 被 重 新 设 定 这 样 接 下 来 的 大 的 分 配 可 以 被 标 记, 即 使 它 们 不 如 先 前 的 分 配 过 的 大 因 为 这 样 的 重 置, 在 查 询 提 交 后 的 结 果 不 可 以 再 次 得 到, 从 表 中 的 输 出 的 结 果 应 该 小 心 的 保 存 监 视 这 个 固 定 表 运 行 如 下 操 作 : SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0; 在 Oracle8i 中 这 个 表 不 能 被 SYS 用 户 之 外 的 用 户 所 选 取 小 的 共 享 池 尺 寸 最 后, 一 个 小 的 共 享 池 可 以 导 致 ORA-04031 错 误, 不 过 在 碎 片 真 正 的 是 个 问 题 的 时 候 增 大 共 享 池 的 大 小 的 时 候 要 小 心 在 错 误 发 现 的 时 候 通 常 有 延 迟 现 象, 不 过 当 在 大 的 共 享 池 的 碎 片 中 找 到 一 片 空 闲 的 内 存 会 加 大 对 性 能 的 影 响 下 面 的 信 息 将 有 助 于 你 调 整 共 享 池 的 大 小 : 库 高 速 缓 冲 命 中 率 命 中 率 有 助 于 你 衡 量 共 享 池 的 使 用, 基 于 多 少 次 SQL/PLSQL 需 要 被 解 析 而 不 是 重 用 下 面 的 SQL 语 句 有 助 于 你 计 算 库 高 速 缓 冲 的 命 中 率 : SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 如 果 misses 比 上 executions 大 于 1%, 那 就 应 该 尝 试 着 通 过 加 大 共 享 池 来 减 少 库 高 速 缓 冲 的 丢 失 Shared Pool Size Calculation 要 计 算 最 适 合 当 前 工 作 负 荷 的 共 享 池 大 小, 参 考 : <Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.
4. 对 ORA-04031 的 高 级 分 析 如 果 使 用 如 上 的 解 决 办 法, 这 个 错 误 仍 然 出 现, 在 initsid.ora 文 件 中 设 定 如 下 的 事 件 并 重 新 启 动 实 例 : event = "4031 trace name errorstack level 3" 会 在 下 一 次 错 误 发 生 的 时 候 产 生 一 个 跟 踪 文 件 这 个 跟 踪 文 件 可 以 提 供 给 Oracle 支 持 人 员 来 解 决 问 题 相 关 文 档 <Note:151790.1> : Oracle8 Tuning Documentation Guide <Note:62143.1>: Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i. <Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE <Note:1012049.6>: TUNING LIBRARY CACHE LATCH CONTENTION <Note:61623.1>: Resolving Shared Pool Fragmentation In Oracle7 <Note: 146599.1>: 就 是 这 篇 文 档 的 英 文 原 稿. About: 这 篇 文 章 的 翻 译 是 本 着 学 习 交 流 的 目 的, 当 然, 原 英 文 作 者 保 留 版 权. 如 果 对 译 文 上 的 技 术 细 节 不 是 很 满 意, 可 以 参 考 原 英 文 文 档. 有 其 它 问 题 请 通 过 Fenng@itpub.net 和 我 联 系.