How to Find SHOUG?

ORACLE 数 据 库 性 能 优 化 的 常 规 方 法 by SHOUG. 周 亮

How to Find SHOUG?

数 据 库 性 能 优 化 的 常 规 方 法 对 于 有 明 显 提 示 的 错 误, 只 要 时 间 相 对 充 足, 大 多 数 DBA 都 能 通 过 错 误 提 示 对 未 知 问 题 做 出 基 本 的 判 断, 找 到 解 决 错 误 的 方 向 但 是 数 据 库 的 性 能 问 题 却 没 有 明 显 的 错 误 提 示 Oracle 的 性 能 问 题 通 常 只 是 表 现 出 系 统 整 体 吞 吐 量 下 降 关 键 业 务 模 块 响 应 变 慢 突 发 的 负 载 增 加 某 项 操 作 系 统 资 源 耗 尽 等 表 象 但 引 起 此 类 现 象 的 原 因 很 多, 通 常 不 能 简 单 地 通 过 互 联 网 来 寻 求 解 决 方 案 比 如 很 多 数 据 库 的 性 能 问 题 跟 SGA 中 的 资 源 争 用 相 关, 但 SGA 内 部 组 件 的 结 构 异 常 复 杂, 再 加 上 还 有 众 多 稍 纵 即 逝 且 相 互 关 联 的 等 待 事 件, 因 此, 即 使 通 过 互 联 网 知 道 了 每 个 等 待 事 件 的 含 义, 仍 然 很 难 将 等 待 事 件 关 联 起 来, 找 到 问 题 的 方 向 基 于 此, 本 节 将 主 要 讨 论 数 据 库 性 能 优 化 的 步 骤 和 常 见 手 段 1.1 数 据 库 性 能 调 整 的 基 本 流 程 当 数 据 库 发 生 性 能 问 题 时,DBA 如 果 能 够 通 过 各 种 途 径 准 确 及 时 地 知 道 系 统 的 变 更 情 况, 及 时 地 收 集 能 反 映 数 据 库 的 运 行 环 境 和 运 行 状 态 的 信 息, 如 收 集 主 机 资 源 数 据 库 的 AWR/STATASPACK 报 告 等, 那 么 分 析 性 能 问 题 就 会 顺 利 很 多, 因 为 这 些 信 息 能 为 事 后 进 行 性 能 诊 断 提 供 强 有 力 的 依 据 很 多 DBA 会 尝 试 重 启 数 据 库 来 解 决 性 能 问 题 的 确, 部 分 数 据 库 性 能 问 题 可 以 通 过 重 启 来 解 决 ( 如 某 些 场 合 下 的 library cache lock 业 务 程 序 死 锁 等 ) 但 是 在 重 启 数 据 库 后, 性 能 视 图 中 的 统 计 数 据 也 会 随 之 重 置, 这 会 给 性 能 问 题 的 事 后 分 析 带 来 很 大 的 困 难, 甚 至 无 法 找 到 故 障 的 根 本 原 因 虽 然 Oracle 10g 推 出 了 ASH(Active Session History) 特 性, 我 们 可 以 从 ASH 报 告 或 者 DBA_HIST_ACTIVE_SESS_HISTORY 视 图 中 找 到 一 丝 蛛 丝 马 迹 来 分 析 推 断 数 据 库 数 据 库 性 能 问 题 的 原 因, 但 事 后 分 析 仍 不 是 监 控 数 据 库 性 能 的 优 先 手 段 提 示 很 多 单 位 的 DBA 都 有 绩 效 考 核, 所 以 尽 量 要 将 故 障 处 理 的 时 间 缩 短 在 影 响 绩 效 的 范 围 内 收 集 完 故 障 现 场 数 据 后, 首 先 要 做 的 是 恢 复 业 务, 研 究 性 的 事 后 分 析 工 作 让 二 线 工 程 师 去 做 吧 性 能 优 化 最 难 的 是 能 够 在 海 量 信 息 中 找 到 优 化 的 方 向, 抓 住 要 点 当 找 到 优 化 方 向 后, 采 用 的 优 化 技 术 往 往 非 常 简 单, 甚 至 不 起 眼 进 行 性 能 优 化 时, 不 在 于 你 掌 握 了 多 少 种 技 术, 使 用 了 多 少 种 工 具, 而 在 于 找 到 一 种 适 合 自 己 的 实 用 的 方 法 比 如 查 看 SQL 的 执 行 计

划 有 很 多 种 方 法, 但 我 们 不 需 要 掌 握 每 种 方 法, 通 常 只 需 要 掌 握 其 中 一 到 两 种 自 己 最 擅 长 的 方 法 即 可 以 下 为 笔 者 的 性 能 问 题 处 理 流 程, 供 读 者 参 考 : (1) 制 定 一 个 简 单 可 行 的 沟 通 机 制, 了 解 系 统 的 变 更 情 况 (2) 不 要 让 系 统 意 外 宕 机 (3) 明 确 优 化 目 标, 切 忌 无 止 境 的 优 化 (4) 不 要 一 个 人 战 斗, 了 解 你 能 够 获 得 的 其 他 支 持 力 量 (5) 部 署 性 能 监 控 工 具 ( 如 OSW), 记 录 下 当 前 主 机 资 源 的 使 用 情 况 (6) 查 看 V$SESSION_WAIT 视 图, 获 取 STATSPACK/AWR 报 告 (7) 快 速 分 析, 制 定 优 化 方 案 和 实 施 计 划 (8) 定 位 故 障 (9) 再 次 沟 通, 审 核 优 化 方 案 和 实 施 计 划 (10) 实 施 优 化 方 案, 做 好 回 退 机 制 (11) 优 化 效 果 评 估, 如 果 没 有 达 到 预 期, 则 重 复 6~10 步 骤 (12) 记 录 下 这 个 成 功 案 例, 做 到 举 一 反 三 尽 信 书, 不 如 无 书 我 们 需 灵 活 运 用 以 上 各 种 优 化 手 段, 切 忌 照 搬 照 抄 优 化 完 成 之 后, 再 针 对 优 化 成 果 建 立 新 的 数 据 库 的 性 能 基 线 注 意 上 述 优 化 步 骤 并 不 是 简 单 地 串 行 化 操 作, 而 是 一 个 相 互 迭 代, 循 序 渐 进 的 过 程 某 些 调 整 可 能 会 产 生 意 外 的 性 能 结 果, 如 果 在 调 整 之 前 保 存 了 以 前 的 设 置, 那 么 回 退 不 正 确 的 调 整 就 变 得 非 常 简 单 数 据 库 性 能 优 化 涉 及 面 很 广 实 践 经 验 表 明, 在 数 据 库 性 能 优 化 的 过 程 中, 如 果 仅 仅 调 整 Oracle 参 数, 一 般 只 能 提 高 20% 左 右 的 性 能 而 仅 仅 调 整 SQL 执 行 计 划, 也 往 往 也 无 法 全 面 提 高 系 统 的 性 能 只 有 针 对 应 用 程 序 的 代 码 级 优 化 才 能 真 正 提 高 数 据 库 的 性 能, 当 然 这 个 优 化 成 本 也 是 最 高 的 注 意 在 实 际 性 能 优 化 过 程 中, 都 需 要 知 道 每 个 优 化 动 作 后 的 后 果 ( 好 的 和 坏 的 ) 还 需 指 出 的 是, 在 性 能 优 化 时 尽 量 少 使 用 隐 含 参 数 ( 除 非 Oracle 新 特 性 带 来 的 bug, 针 对 这 类 情 况 Oracle 往 往 可 以 通 过 设 置 隐 含 参 数 来 关 闭 这 个 新 特 性 ) 目 前 为 止 还 没 有 FAST=TRUE 的 参 数 Oracle 设 置 隐 含 参 数 主 要 有 以 下 几 个 目 的 : 代 码 的 debug 开 启 或 关 闭 某 项 数 据 库 特 性 跟 踪 会 话 修 复 某 些 Oracle bug 特 定 场 合 下 的 特 定 用 途 如 设 置 _minimum_giga_scn 隐 含 参 数 提 升 系 统 SCN

设 置 隐 含 参 数 来 优 化 数 据 库 通 常 是 某 些 Oracle 高 手 (Oracle 发 烧 友 ) 所 采 用 的 方 法, 但 这 样 做 往 往 会 犯 捡 芝 麻 丢 西 瓜 的 错 误 比 如 当 系 统 大 量 出 现 latch:library cache 等 待 事 件 时, 并 不 能 简 单 地 设 置 隐 含 参 数 _kgl_latch_count 来 增 加 library cache latch 的 数 量, 从 而 达 到 数 据 库 性 能 优 化 的 目 的 大 量 的 实 践 表 明, 数 据 库 优 化 仍 然 需 要 从 常 规 的 技 术 手 段 入 手, 如 SQL 优 化 表 碎 片 整 理 打 散 热 点 块 等 图 7-1 为 某 数 据 库 中 让 人 纠 结 的 隐 含 参 数 和 event 事 件 图 7-1 AWR 报 告 中 的 部 分 数 据 提 示 某 些 Oracle 发 烧 友 习 惯 于 纠 结 细 节, 不 能 从 宏 观 上 把 握 问 题 的 方 向

解 析 来 几 节 主 要 介 绍 的 是 笔 者 经 常 使 用 的 数 据 库 级 别 性 能 调 优 方 法 1.2 调 整 Oracle 内 存 参 数 调 整 Oracle 内 存 参 数 是 相 对 简 单 的 优 化 动 作, 主 要 包 含 以 下 几 方 面 : 调 整 Oracle 内 存 参 数 主 要 包 含 以 下 几 个 方 面 : 调 整 不 合 理 的 SGA_TARGET PGA_ PGA_AGGREGATE_TARGET 参 数 值 过 大 的 SGA 容 易 导 致 系 统 内 存 交 换, 过 小 的 SGA 容 易 导 致 内 存 争 用 或 者 内 存 不 足 设 置 SGA_TARGET 参 数 为 0(Oracle 10g), 禁 止 SGA 内 存 自 动 管 理, 防 止 SGA 组 件 内 存 抖 动 内 存 抖 动 指 的 是 SGA 中 的 内 存 在 各 内 存 组 件 之 间 频 繁 移 动 在 SGA 内 存 自 动 管 理 模 式 下,SHARED POOL 大 小 会 倾 向 于 自 动 调 整 到 较 大 值, 在 极 端 情 况 下, 反 而 容 易 性 能 问 题 1.3 调 整 数 据 库 在 线 日 志 数 据 库 的 在 线 日 志 是 循 环 利 用 的 但 在 线 日 志 被 重 用 之 前, 其 状 态 必 须 是 INACTIVE 状 态, 表 示 Oracle 异 常 关 闭 启 动 之 后 不 需 要 应 用 该 日 志 如 果 在 线 日 志 状 态 为 ACTIVE, 那 么 重 用 该 日 志 前,LGWR 进 程 必 须 等 待 该 日 志 对 应 的 数 据 块 已 写 至 数 据 文 件 中 LGWR 进 程 等 待 DBWR 写 数 据 块 的 过 程 中, 系 统 表 现 为 HANG, 这 就 会 影 响 数 据 库 的 性 能 从 以 上 分 析 可 以 看 出, 当 在 线 日 志 状 态 出 现 大 量 ACTIVE 时, 可 能 是 由 以 下 因 素 引 起 的 : 系 统 正 在 运 行 大 量 的 DML 语 句 或 者 正 在 大 规 模 加 载 数 据 存 储 I/O 问 题, 导 致 DBWR 进 程 写 数 据 块 速 度 缓 慢 DBWR 进 程 数 不 够 在 线 日 志 组 数 过 少 或 者 其 大 小 过 小, 导 致 在 线 日 志 不 停 地 被 重 用

1.4 调 整 SQL 的 执 行 计 划 在 OLTP 系 统 中, 调 整 SQL 的 目 标 是 降 低 Oracle 逻 辑 读 写 的 数 量, 减 少 CPU 资 源 消 耗 可 以 通 过 以 下 几 个 方 面 进 行 调 整 : 如 果 数 据 库 对 象 的 统 计 信 息 和 真 实 数 据 偏 差 较 大, 优 先 考 虑 重 新 收 集 表 统 计 信 息 来 改 善 SQL 执 行 计 划 如 果 表 某 列 数 据 分 布 偏 差 较 大, 且 出 现 在 SQL 选 择 谓 词 中 ( 没 有 绑 定 变 量 ), 则 建 议 对 该 列 收 集 柱 状 图 来 改 善 执 行 计 划 构 建 最 佳 的 索 引 来 改 善 SQL 执 行 计 划 比 如 通 过 分 析 所 有 处 理 类 型 来 全 面 构 建 最 佳 索 引 以 系 统 中 的 主 要 表 为 单 位, 搜 集 系 统 中 所 包 含 的 全 部 SQL 语 句 的 Access Path, 并 通 过 对 搜 集 到 的 Access Path 的 综 合 分 析, 设 计 出 能 够 满 足 所 有 读 取 要 求 的 最 佳 索 引 分 析 和 查 找 在 SQL 语 句 执 行 过 程 中 能 够 减 少 逻 辑 读 的 列, 将 其 添 加 到 索 引 中, 使 其 发 挥 过 滤 的 作 用 创 建 由 PK 索 引 列 + 过 滤 列 所 构 成 的 索 引, 避 免 表 连 接 时 所 执 行 的 大 量 随 机 读 取, 从 而 提 高 执 行 效 率 如 果 全 表 扫 描 和 索 引 扫 描 的 消 耗 的 时 间 相 同, 在 OLTP 系 统 中, 则 建 议 使 用 索 引 扫 描 作 为 SQL 的 执 行 计 划, 因 为 一 般 来 讲 索 引 扫 描 读 取 的 数 据 块 较 少,CPU 资 源 消 耗 较 少 使 用 HINT 来 改 善 SQL 执 行 计 划 要 注 意 的 是, 使 用 HINT 固 定 执 行 计 划 时, 不 仅 需 要 开 发 人 员 配 合 修 改 代 码, 而 且 HINT 产 生 的 执 行 计 划 不 能 保 证 该 执 行 计 划 长 期 最 优 因 为 HINT 不 够 灵 活, 它 不 能 根 据 数 据 量 的 大 小 和 分 布 情 况 来 改 变 原 有 的 执 行 计 划 改 写 SQL, 如 调 整 表 连 接 顺 序 设 计 正 确 的 驱 动 (DRIVING) 表 添 加 选 择 性 高 的 过 滤 列, 实 现 最 佳 表 连 接 需 要 注 意 的 是, 在 上 线 之 后 的 系 统 中 改 写 SQL 往 往 成 本 很 高 优 化 SQL 有 个 总 原 则, 就 是 先 优 化 资 源 消 耗 最 高 的 子 查 询 如 果 SQL 的 执 行 计 划 不 稳 定, 则 检 查 SQL 中 是 否 含 有 绑 定 变 量, 且 涉 及 的 表 中 含 有 柱 状 图 (HISTOGRAM), 则 如 果 有 则 考 虑 去 除 表 的 柱 状 图 增 强 执 行 计 划 的 稳 定 性 也 可 以 考 虑 使 用 SQL Profile 或 者 OUTLINE 加 固 执 行 计 划 提 示 如 果 是 程 序 自 己 拼 装 的 SQL, 那 么 往 往 导 致 该 SQL 很 长 生 成 长 SQL 的 执 行 计 划 就 需 要 消 耗 较 长 时 间

1.5 优 化 对 象 的 I/O 读 取 在 这 里, 优 化 对 象 的 I/O 操 作 指 的 是 减 少 对 象 I/O 读 取, 避 免 I/O 争 用, 从 而 提 高 数 据 库 的 运 行 效 率 但 需 要 注 意 的 是 减 少 I/O 读 取, 避 免 I/O 争 用 往 往 是 两 项 互 斥 的 技 术, 比 如 减 少 行 数 据 存 放 空 间 可 以 减 少 I/O 读 取, 但 容 易 引 起 I/O 争 用 所 以 我 们 需 要 活 学 活 用 优 化 技 术 1. 减 少 I/O 读 取 以 下 为 在 数 据 库 级 别 优 化 对 象 I/O 的 常 用 方 法 : 在 选 择 列 上 创 建 索 引 如 果 从 索 引 上 读 取 数 据 所 消 耗 的 I/O 数 比 从 表 中 读 取 少, 则 可 以 考 虑 使 用 此 方 法 重 建 索 引 主 要 针 对 碎 片 较 多, 索 引 层 数 较 高,CLUSTER 因 子 较 大 的 索 引 重 建 时 建 议 将 索 引 的 PCTFREE 参 数 设 置 为 0, 这 样 一 个 索 引 块 中 就 可 以 保 存 更 多 的 数 据 行 信 息 删 除 索 引 这 个 操 作 主 要 针 对 无 效 索 引 过 多 的 无 效 索 引 不 仅 容 易 引 起 CBO 优 化 器 选 择 错 误, 从 而 导 致 执 行 计 划 变 差, 而 且 会 降 低 DML 操 作 效 率 重 组 表 表 重 组 要 针 对 碎 片 严 重 行 迁 移 和 行 链 接 较 严 重 的 表 表 重 组 之 后,Oracle 一 次 I/O 操 作 可 以 读 取 的 更 多 的 数 据 将 大 表 变 成 小 表 如 迁 移 历 史 数 据 并 重 组 表 格, 由 于 SQL 的 WHERE 条 件 过 滤 性 不 佳, 导 致 执 行 计 划 只 能 选 择 全 表 扫 描 随 着 时 间 的 推 移, 当 表 中 的 数 据 可 能 越 来 越 多, 全 表 扫 描 的 代 价 可 能 越 来 越 高, 从 而 导 致 数 据 库 运 行 缓 慢 压 缩 表 灵 活 使 用 数 据 压 缩 存 储 技 术, 可 以 大 幅 度 缩 减 大 量 数 据 处 理 时 的 I/O 量 以 内 存 换 取 I/O 如 果 内 存 足 够, 可 以 将 小 的 热 表 KEEP 进 KEEP BUFFER CACHE 中 热 表 指 的 是 频 繁 使 用 的 表 将 表 设 置 为 NO LOGGING 模 式 NO LOGGING 模 式 能 有 效 地 加 快 数 据 处 理 速 度 但 需 要 注 意 的 是, 由 于 NO LOGGING 模 式 的 操 作 记 录 不 会 完 全 记 录 在 REDOLOG 中, 因 此 并 不 能 用 物 理 的 备 份 恢 复 技 术 来 恢 复 NOLOGGING 的 数 据 ( 除 非 数 据 库 级 别 或 者 表 所 在 表 空 间 设 置 为 FORCE LOGGING 模 式 ) 对 于 其 值 不 断 增 加 的 表 ( 如 日 志 表 ), 因 为 这 些 表 很 少 更 新, 所 以 最 好 设 置 一 个 非 常 低 的 PCTFREE( 甚 至 可 以 为 0), 从 而 节 省 存 储 空 间

2. 减 少 I/O 争 用 以 下 为 在 数 据 库 级 别 减 少 I/O 争 用 的 常 用 技 术 : 分 散 热 块 中 的 数 据 最 常 见 的 优 化 手 段 是 使 用 HASH 分 区 HASH CLUSTER TABLE 反 转 键 索 引 加 大 表 PCTFREE 参 数 使 用 较 小 BLOCK SIZE 的 数 据 块, 如 4KB 1.6 降 低 CURSOR 解 析 成 本 降 低 Oracle CURSOR 解 析 成 本, 可 以 使 用 以 下 几 个 常 用 的 技 术 : 为 防 止 将 CURSOR 交 换 出 SHARED POOL, 可 以 将 常 用 的 SQL 或 者 PL/SQL 代 码 KEEP 进 SHARED POOL 中 设 置 绑 定 变 量, 减 少 硬 解 析 但 设 置 绑 定 变 量 时 需 要 注 意 绑 定 变 量 窥 视 (bind peeking) 的 问 题 绑 定 变 量 窥 视 会 导 致 执 行 计 划 不 稳 定 (Oracle 11g 使 用 自 适 应 游 标 共 享 之 后 该 问 题 会 有 所 缓 解 ) 设 置 CURSOR_SHARING 参 数 SESSION_CACHED_CURSORS 参 数, 减 少 硬 解 析 需 要 注 意 的 是, 在 低 版 本 的 数 据 库 ( 如 Oracle 9.2.0.1) 中 设 置 CURSOR_SHARING 参 数 为 similar 可 能 会 带 来 比 较 多 的 bug, 所 以 一 般 建 议 将 其 设 置 为 force 检 查 是 否 存 在 高 版 本 (high version) 的 SQL 高 版 本 SQL 容 易 引 起 library cache 争 用, 如 出 现 LATCH:LIBRARY CACHE 等 待 事 件 1.7 其 他 数 据 库 性 能 调 整 手 段 除 了 采 用 以 上 数 据 库 优 化 技 术, 在 数 据 库 性 能 优 化 过 程 中 还 经 常 使 用 以 下 常 用 的 技 术 : 开 启 并 行 其 目 的 是 为 了 充 分 利 用 系 统 的 CPU 资 源 和 I/O 资 源, 使 得 其 在 最 短 的 时 间 内 完 成 大 量 的 数 据 处 理 并 行 绕 过 了 BUFFER CACHE 使 用 直 接 路 径 读 取 数 据 文 件 数 据 块 减 少 排 序 操 作 减 少 排 序 最 有 效 的 手 段 是 在 需 要 排 序 的 列 上 创 建 索 引 当 然 在 创 建 索 引 之 前 需 要 评 估 执 行 计 划 变 更 DML 操 作 变 慢 所 带 来 的 影 响

使 用 直 接 路 径 读 写 直 接 路 径 读 写 可 以 避 免 数 据 块 经 过 BUFFER CACHE, 从 而 缓 减 BUFFER CACHE 的 争 用 调 整 段 管 理 方 式, 比 如 将 手 动 段 管 理 方 式 变 成 自 动 段 管 理 方 式 自 动 段 管 理 可 以 有 效 地 避 免 段 头 的 争 用 而 导 致 的 buffer busy waits 等 待 事 件 调 整 段 的 空 间 管 理 参 数, 如 调 整 表 的 freelist 参 数 initrans 参 数 等 调 整 SEQUENCE 的 CACHE 大 小, 避 免 产 生 row cache lock 和 enq: SQ - contention 等 待 事 件 过 小 的 CACHE 在 RAC 节 点 之 间 的 影 响 更 大 减 少 高 频 SQL 的 执 行 次 数 使 用 该 方 法 的 前 提 是 能 满 足 业 务 要 求, 业 务 特 性 决 定 了 SQL 的 执 行 次 数 调 整 SQL 的 执 行 时 间 窗 口 使 用 该 方 法 的 前 提 是 能 满 足 业 务 要 求, 业 务 特 性 决 定 了 SQL 执 行 时 间 窗 口 当 系 统 资 源 紧 张 时, 使 用 该 方 法 能 有 效 地 避 开 资 源 高 峰 期

作 者 个 人 简 介 周 亮 个 人 微 博 :http://www.weibo.com/dbathinker Oracle DBA 实 战 攻 略 : 运 维 管 理 诊 断 优 化 高 可 用 与 最 佳 实 践 一 书 作 者 目 前 已 定 稿,2013 年 6 月 份 出 版 杭 州 美 创 科 技 Oracle 技 术 服 务 团 队 负 责 人,Oracle 10g OCM 精 通 Oracle 数 据 库 原 理 拥 专 职 Oracle 数 据 库 管 理 经 验, 对 于 数 据 库 架 构 设 计 运 维 调 优 排 故 有 着 丰 富 的 实 战 经 验, 擅 长 在 极 端 环 境 下 进 行 数 据 库 灾 难 挽 救 带 领 Oracle 技 术 服 务 团 队, 为 公 司 客 户 提 供 上 百 套 数 据 库 维 护 工 作 其 中 涉 及 政 府 通 信 金 融 公 安 电 力 交 通 医 疗 制 造 等 行 业 主 要 工 作 内 容 有 : 日 常 运 维 故 障 诊 断 性 能 优 化 容 灾 实 施 灾 难 挽 救 系 统 割 接 恢 复 测 试 数 据 迁 移 业 务 上 线 护 航 Oracle 技 术 培 训 解 决 方 案 提 供 7*24 小 时 电 话 或 远 程 支 持 等 工 作 熟 悉 AIX,HP-UX,SOLARIS,LINUX 等 主 流 操 作 系 统 平 台, 熟 悉 主 流 存 储 卷 组 管 理 在 各 种 场 合 下 多 次 主 讲 Oracle 10g OCP/OCM 培 训 课 程 案 例 分 析 课 程