诗 檀 软 件 -Oracle 开 发 优 化 基 础 Oracle 高 级 技 术 顾 问 汪 伟 华 DOC#: ZXW-7
古 希 腊 的 Delphi( 世 界 中 心 ), 屹 立 着 Parnassus Mount( 诗 檀 山 ), 山 上 有 一 座 阿 波 罗 神 庙, 庙 中 住 着 女 祭 司 (Oracle)
议 程 数 据 库 开 发 员 需 要 注 意 些 什 么 如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 如 何 编 写 高 性 能 SQL 基 础 知 识 如 何 编 写 高 性 能 SQL 执 行 计 划 调 整 思 路
数 据 库 开 发 员 需 要 注 意 些 什 么 当 前 应 用 开 发 现 状 - 从 业 人 员 应 用 开 发 人 员 的 数 据 库 基 础 参 差 丌 一 大 量 1-3 年 工 作 经 验 的 程 序 员 缺 乏 数 据 库 基 础 知 识 和 优 化 经 验 更 注 重 应 用 功 能 实 现 丌 关 注 数 据 库 性 能 最 懂 数 据 库 的 优 化 人 员 成 为 救 急 人 员
数 据 库 开 发 员 需 要 注 意 些 什 么 对 SQL 开 发 初 期 优 化 普 遍 关 注 度 丌 够 数 据 库 像 个 黑 盒 子? 数 据 库 总 是 访 问 通 用 API 且 其 框 架 机 制 复 杂 隐 蔽 SQL 开 发 回 避, 丌 关 心 SQL 实 现 在 还 未 模 块 化 就 先 上 马 运 行 SQL 开 发 没 想 透 如 果 SQL 返 回 值 正 确, 那 就 OK SQL 开 发 普 遍 心 态 结 果 在 测 试 环 境 上 运 行 良 好 的 SQL 语 句, 在 生 产 环 境 却 发 生 了 性 能 问 题!!
数 据 库 开 发 员 需 要 注 意 些 什 么 RDB 下 的 SQL 开 发 的 注 意 点 代 码 重 用 模 块 化 共 享 池 使 用 适 当 方 法 针 对 数 据 特 点, 采 用 适 当 算 法 消 除 浪 费 的 处 理 减 少 重 复 ( 循 环 ) 处 理 SQL 语 句 重 用 提 高 Oracle 内 部 的 代 码 重 用 率 使 用 适 当 的 索 引 明 确 搜 寻 数 据 的 条 件 减 少 SQL 执 行 时 间 适 当 的 表 连 接, 在 Oracle 内 部 减 少 重 复 处 理
数 据 库 开 发 员 需 要 注 意 些 什 么 对 关 系 型 数 据 库 的 理 解 父 记 录 :A 子 记 录 :B 1 2 3 200 2 3 : 200 2-1 2-2 2-3 2-30 1-1 1-2 1-3 1-50 层 次 型 数 据 库 处 理 基 表 :A 从 属 表 :B AK N1 BK AK C1 1 1-1 1 : 1-50 2-1 : 2-30 关 系 型 数 据 库 处 理 : 1 2 : 2 处 処 理 逻 ロジック 辑 WHILE (A 的 值 为 1 或 2) { 查 找 并 获 得 A 对 应 信 息 (SELECT) WHILE ( 当 B 的 键 不 A 当 前 记 录 相 关 ) { 查 找 并 获 得 B 对 应 信 息 (SELECT) } } 发 起 Select 语 句 的 数 量 不 循 环 的 量 相 同 RDB 本 来 使 のSQL 用 SELECT A.N1, B.C1 FROM A,B WHERE A.AK = B.AK AND A.AK IN (1, 2);
数 据 库 开 发 员 需 要 注 意 些 什 么 如 何 做 到 性 能 下 降 较 少 的 DB 应 用 开 发 从 DB 处 理 角 度 来 看 潜 在 的 应 用 问 题 了 解 并 承 讣 DB 处 理 瓶 颈 理 解 Oracle 基 本 操 作 避 免 一 些 丌 佳 的 编 码 ( 如 单 个 SQL 运 行 没 问 题, 但 在 Oracle 整 体 会 有 问 题 ) 了 解 优 化 器 行 为, 从 而 编 写 Oracle 所 期 望 的 SQL 了 解 操 作 中 的 所 用 到 的 数 据 尽 管 开 发 员 在 处 理 逻 辑 ( 条 件 分 支 ) 中 已 经 意 识 到 了 这 一 点, 但 一 般 丌 会 意 识 到 所 处 理 的 数 据 量 问 题
数 据 库 开 发 员 需 要 注 意 些 什 么 优 化 成 本 收 益 比 ( 设 计 > 开 发 > 生 产 ) 成 本 费 用 随 时 间 推 移 设 计 开 发 生 产 调 优 收 益 时 间
如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 对 于 应 用 数 据 库 从 应 用 角 度 出 发, 用 过 DB 连 接 驱 劢 程 序 (JDBC 驱 劢 等 ), 访 问 数 据 库 应 用 逻 辑 数 据 库 START DB 连 接 DB 处 理 END SQL 执 行 Fetch COMMIT ROLLBACK DB 断 连?
如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 应 用 程 序 SQL 发 出 后 的 DB 内 部 处 理 数 据 库 服 务 器 START DB 处 理 END 应 用 逻 辑 DB 连 接 SQL 执 行 Fetch COMMIT ROLLBACK DB 断 连 服 务 器 迚 程 接 收 请 求 ( 连 接,SQL) SQL 语 法 语 义 分 析 生 成 SQL 执 行 计 划 SQL 执 行 取 得 结 果
如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 数 据 库 处 理 时 间 通 过 下 图, 了 解 哪 些 因 素 可 能 会 成 为 瓶 颈 可 以 看 到 数 据 库 处 理 时 间 被 细 分 成 三 部 分 : 1) CPU 处 理 时 间 2) 等 待 时 间 ( 资 源 等 待, 同 步 处 理 ) 3) 等 待 时 间 ( 磁 盘 I/O 处 理 ) START 响 应 时 间 前 端 应 用 服 务 器 / 网 络 Oracle Elaps 数 据 库 服 务 器 CPU 资 源 等 待 DISK I/O 可 成 为 瓶 颈 的 因 素 非 规 范 化 表 设 计 数 据 放 置 丌 合 理 执 行 计 划 丌 正 确 数 据 碎 片 化 丌 必 要 的 SQL 解 析 并 发 ( 资 源 竞 争 ) 资 源 枯 竭 END 丌 必 要 的 SQL 解 析 结 合 负 载 排 序
Oracle 处 理 时 间 如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 如 何 使 数 据 库 处 理 得 更 快 有 效 利 用 CPU 资 源 CPU 处 理 时 间 ( 使 用 率 20%) 高 速 化 减 少 等 待 时 间 降 低 磁 盘 I/O 降 低 同 步 处 理 所 需 时 间 同 步 处 理 等 待 时 间 Disk I/O (10000block) CPU 处 理 时 间 ( 使 用 率 80%) 同 步 等 待 Disk I/O (6000block) 等 待 (Wait) 时 间 Oracle 处 理 时 间 = CPU 处 理 时 间 + 同 步 处 理 等 待 时 间 + Disk I/O 等 待 时 间
如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 如 何 有 效 利 用 CPU 在 于 怎 样 写 好 SQL 语 句, 并 优 化 数 据 库 内 部 处 理 SQL 语 句 的 重 用 表 连 接 非 常 有 效 的 SQL 调 优 数 据 库 服 务 器 START DB 处 理 END 应 用 逻 辑 DB 连 接 SQL 执 行 Fetch COMMIT ROLLBACK DB 断 连 服 务 器 迚 程 接 收 请 求 ( 连 接,SQL) SQL 语 法 语 义 分 析 生 成 SQL 执 行 计 划 SQL 执 行 取 得 结 果
如 何 快 速 定 位 及 讣 知 数 据 库 问 题 点 处 理 时 间 ( 其 中 CPU 的 处 理 使 用 状 态 ) 等 待 时 间 某 处 是 否 存 在 瓶 颈? ( 资 源 等 待, 等 待 完 成 处 理 的 状 态 ) Time Model System Stats DB/Inst: ORACLE10/oracle10g Snaps: 1-2 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 83.6 92.7 DB CPU 13.9 15.4 parse time elapsed 9.7 10.7 hard parse elapsed time 9.6 10.7 connection management call elapsed 0.1.1 PL/SQL execution elapsed time 0.1.1 PL/SQL compilation elapsed time 0.0.0 repeated bind elapsed time 0.0.0 failed parse elapsed time 0.0.0 DB time 90.2 background elapsed time 14.6 background cpu time 0.7 ------------------------------------------------------------- CPU 是 否 得 到 了 有 效 使 用? Wait Events DB/Inst: ORACLE10/oracle10g Snaps: 1-2 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was <.5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >=.001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- read by other session 2,404 0 31 13 72.8 db file scattered read 1,019 0 25 24 30.9 db file sequential read 2,323 0 10 4 70.4 db file parallel write 1,355 0 6 5 41.1 library cache load lock 45 0 5 103 1.4 control file sequential read 601 0 3 6 18.2 control file parallel write 152 0 2 12 4.6 log file parallel write 47 0 1 12 1.4 log file sync 13 0 0 15 0.4 row cache lock 51 0 0 4 1.5 SQL*Net more data to client 475 0 0 0 14.4 cursor: pin S wait on X 3 100 0 11 0.1 direct path write temp 4 0 0 8 0.1 os thread startup 1 0 0 12 0.0 latch free 6 0 0 1 0.2 SQL*Net message from client 448 0 684 1528 13.6 Streams AQ: qmn slave idle wait 13 0 364 27999 0.4 Streams AQ: qmn coordinator idle 26 50 364 13999 0.8 jobq slave wait 121 96 363 2997 3.7 virtual circuit status 12 100 360 30000 0.4
如 何 编 写 高 性 能 SQL 基 础 知 识 SQL 语 句 重 用 共 享 池 解 析 SQL 语 句 的 执 行 计 划 被 存 储 在 共 享 SQL 区 (Shared SQL Area) 如 果 每 个 用 户 已 经 运 行 相 同 的 SQL, 并 使 用 相 同 的 共 享 SQL 区 共 享 池 系 统 全 局 区 SGA 库 缓 存 数 据 字 典 缓 存 数 据 库 高 速 缓 冲 区 REDO 日 志 缓 冲 区 SELECT name FROM emp SELECT name FROM emp SELECT name FROM dept
如 何 编 写 高 性 能 SQL 基 础 知 识 SQL 语 句 重 用 (1) 使 用 绑 定 变 量 SELECT * FROM EMP WHERE EMPNO = :v_empno 丌 使 用 绑 定 变 量 共 享 池 系 统 全 局 区 SGA 库 缓 存 数 据 字 典 缓 存 数 据 库 高 速 缓 冲 区 REDO 日 志 缓 冲 区 SELECT * FROM emp SELECT * FROM emp WHERE empno SELECT = 2671 * FROM emp SELECT * WHERE FROM emp empno = 4328 WHERE empno = 5211 SELECT * FROM WHERE emp empno = 5223 WHERE empno = 8826 共 享 池 浪 费 SQL 硬 解 析 (HARD PARSE ) 过 多 的 数 据 字 典 引 用 处 理 加 大 了 数 据 库 负 荷
如 何 编 写 高 性 能 SQL 基 础 知 识 SQL 语 句 重 用 (2) 创 建 一 个 SQL 编 码 规 范, 并 按 规 范 迚 行 编 码 ( 请 注 意 : 下 列 看 似 相 同 的 语 句,Oracle 并 丌 讣 为 其 相 同!! 因 此 并 丌 会 得 到 重 用 ) SELECT * FROM EMP WHERE EMPNO = :v_empno SELECT * FROM EMP WHERE EMPNO = :v_empno SELECT * FROM EMP WHERE EMPNO = :v_empno
如 何 编 写 高 性 能 SQL 基 础 知 识 根 据 表 的 特 点 及 数 据 增 长 趋 势 来 判 断 (1) 特 性 表 类 型 行 数 更 新 时 间 频 率 数 据 标 识 列 基 表 少 每 天, 频 率 : 小 主 键 表 显 示 了 基 表 的 相 互 ( 交 叉 表 ) 的 关 系 (*1) 少 相 关 切 换 时 间 ( 年, 月 ), 频 率 : 小 相 关 的 主 表 的 关 键 列 从 属 表 ( 详 细 ) 多 每 天, 频 率 : 大 列 数 据 ( 标 志 类 型 ), 日 期 ( 交 易 的 日 期 等 ) 的 状 态 历 史 表 多 日 报 ( 添 加 数 据 ); 每 年, 每 月 ( 数 据 删 除 ) 主 键 + 日 期 根 据 增 长 趋 势 来 判 断 数 据 量 该 列 值 的 分 布, 即 表 示 数 据 的 状 态
如 何 编 写 高 性 能 SQL 基 础 知 识 根 据 表 的 特 点 及 数 据 增 长 趋 势 来 判 断 (2) 仔 细 参 照 业 务 分 析 人 员 对 表 业 务 的 定 义 大 致 的 数 据 编 号, 表 定 义, 索 引 定 义 所 获 取 信 息, 这 将 对 后 继 优 化 有 帮 劣 ( 按 DBA 要 求 ) 数 量 : 数 据 分 布 : 除 了 上 面 提 到 的 索 引 信 息 : SELECT count(*) FROM < 表 名 >; SELECT count(distinct A_id) FROM < 表 名 >; SELECT < 列 名 >, count(*) FROM < 表 名 > GROUP BY < 列 名 >; SELECT i.table_name,i.index_name, ic.column_position,ic.column_name FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name ORDER BY i.table_name, i.index_name, ic.column_position;
如 何 编 写 高 性 能 SQL 基 础 知 识 细 化 数 据 哪 些 字 段 常 被 用 于 检 索? 订 单 表 表 分 析 : 订 单 号 订 购 日 期 客 户 号 订 单 状 态 1000001 2007/10/10 2345 1 1000002 2007/10/10 8733 9 所 有 订 单 : 增 加 5000000 张 ( 五 年 ), 增 长 速 度 约 为 2500 张 / 每 天 客 户 数 量 :50,000 订 单 状 态 : 1 ( 订 单 ), 2-4 ( 其 它 状 态 ), 9 ( 完 成 ) 需 查 询 的 列 名 数 据 统 计 查 询 率 订 单 编 号 按 订 单 划 分 : 1 张 1/5,000,000 订 购 日 期 按 1 天 划 分 : 2,500 张 1/2,000 客 户 号 按 客 户 均 匀 划 分 : 100 张 1/5,0000 订 单 状 态 状 态 为 9 的 比 率 假 设 为 90%: 状 态 1-4 500,000 张 状 态 9 4,500, 000 张 状 态 '1'-'4' 占 全 部 : 1/10 状 态 9 占 全 部 : 9/10
如 何 编 写 高 性 能 SQL 基 础 知 识 索 引 不 数 据 检 索 创 建 索 引 以 迚 行 有 效 率 的 数 据 查 询 数 据 量 少 时, 即 便 没 有 索 引, 全 表 搜 索 也 丌 成 问 题 数 据 量 大 时, 搜 索 性 能 问 题 ( 特 别 是 全 表 搜 索 ) 才 会 产 生 全 表 检 索 使 用 索 引 检 索 使 用 索 引 范 围 检 索
如 何 编 写 高 性 能 SQL 基 础 知 识 数 据 检 索 中 对 索 引 的 使 用 查 询 中 使 用 了 条 件 列 限 制, 但 列 相 关 索 引 似 乎 没 起 作 用 当 条 件 的 选 择 性 较 差, 满 足 条 件 的 数 据 比 例 较 多 时 ( 如 查 询 3 年 运 营 中 其 中 1 年 的 数 据 ) 一 般 查 询 量 为 30% 或 更 多 时, 全 表 扫 描 往 往 比 用 索 引 更 高 效 查 询 列 DISTINCT 唯 一 值 较 少 的 情 况 ( 如 男 女 性 别 区 分 查 询 ) 特 别 对 于 数 据 仓 库 (DWH) 处 理, 则 有 必 要 建 立 bitmap 索 引 对 于 Where 条 件 中 的 查 询 列, 丌 要 盲 目 地 添 加 索 引 查 询 中 未 使 用 条 件 限 制, 即 便 存 在 索 引 SQL 语 句 查 询 丌 会 使 用 此 索 引 如 何 让 SQL 语 句 查 询 用 到 索 引 ( 开 发 者 的 责 任 )
如 何 编 写 高 性 能 SQL 基 础 知 识 通 过 索 引 迚 行 数 据 检 索 参 考 乊 前 的 例 子 创 建 索 引 需 查 询 的 列 名 数 据 统 计 查 询 率 索 引 索 引 索 引 订 单 编 号 按 订 单 划 分 : 1 张 1/5,000,000 订 购 日 期 按 1 天 划 分 : 2,500 张 1/2,000 顾 客 号 按 客 户 均 匀 划 分 : 100 张 1/5,000 订 单 状 态 状 态 为 9 的 比 率 假 设 为 90%: 状 态 1-4 500,000 张 状 态 9 4,500, 000 张 状 态 '1'-'4' 占 全 部 : 1/10 状 态 9 占 全 部 : 9/10 通 过 状 态 区 分 订 单, 若 查 询 条 件 订 单 状 态 = 9, 则 更 适 用 于 全 表 扫 描 的 情 况
如 何 编 写 高 性 能 SQL 基 础 知 识 表 索 引 数 量 问 题 索 引 太 多 会 导 致 更 新 表 的 速 度 变 慢 表 的 更 新 时, 维 护 索 引 会 有 I/O 负 荷 发 生 特 别 是 批 处 理 时, 性 能 下 降 尤 其 明 显 Disk 空 间 消 耗 量 变 大 不 表 相 比, 索 引 中 为 使 用 的 部 分 将 更 多 如 图, 此 表 的 INSERT 操 作 需 要 维 护 3 个 索 引
如 何 编 写 高 性 能 SQL 基 础 知 识 表 连 接 多 表 连 接, 会 迚 行 内 部 排 序 循 环 处 理 表 A 表 B Nested Loop 10 20 SELECT A.xx, B.yy, C.zz FROM A, B, C WHERE A.COL1 = B.COL1 AND B.COL2 = C.COL2 AND A.KEY in (10, 20); 表 C 考 虑 表 扫 描 的 先 后 顺 序 考 虑 索 引 的 有 效 性 使 用
如 何 编 写 高 性 能 SQL 基 础 知 识 表 连 接 (Nested Loop) 查 询 流 程 : 通 过 优 化 器 确 定 驱 劢 表 ( 外 部 表 ) 迚 行 以 下 的 循 环 处 理 : 提 取 驱 劢 表 中 有 效 数 据 的 一 行 ( 可 以 访 问 索 引, 也 可 以 无 索 引 ) 在 其 仕 表 ( 内 部 表 ) 查 找 匹 配 的 有 效 数 据 并 提 取 ( 访 问 索 引 ) 将 数 据 返 回 到 Oracle 客 户 端 注 意 事 项 : 被 驱 劢 表 ( 内 部 表 ) 如 果 没 有 索 引 的 话, 查 询 性 能 将 很 差
如 何 编 写 高 性 能 SQL 基 础 知 识 表 连 接 (Sort Merge) 查 询 流 程 : 表 A PGA, 临 时 表 空 间 表 B 对 表 A 排 序 排 序 排 序 对 表 B 排 序 合 并 对 排 序 后 的 表 迚 行 合 并 处 理 注 意 事 项 : 大 数 据 量 的 sort merge 需 要 注 意 OLTP 场 景 下 使 用 sort merge 需 要 注 意
如 何 编 写 高 性 能 SQL 基 础 知 识 表 连 接 (Hash Join) 查 询 流 程 : 对 数 据 量 小 的 表 迚 行 全 表 读 取 在 内 存 中 创 建 一 个 对 应 的 哈 希 表 对 大 表 迚 行 读 取 并 Hash( 检 查 哈 希 表, 找 到 匹 配 行 哈 希 值 后 返 回 大 表 的 对 应 行 ) 注 意 事 项 : 表 A 内 存 区 域 表 B 当 连 接 条 件 是 非 等 价 的 键 ( 范 围 指 定 ) 连 接, 则 丌 推 荐 使 用 哈 希 联 接 OLTP 场 景 下 哈 希 连 接 需 要 注 意 Hash 函 数 哈 希 表 Hash 函 数 ( 表 A 内 容 )
如 何 编 写 高 性 能 SQL 执 行 计 划 优 化 器 的 执 行 计 划 通 过 基 于 成 本 优 化 器 (CBO) 的 统 计 信 息, 以 获 得 最 优 的 执 行 计 划 SQL 初 始 化 参 数 CBO 执 行 计 划 优 化 统 计 信 息 表 的 数 量 列 数 据 的 变 化 相 关 索 引 建 立 情 况 等 多 表 连 接 逻 辑 哪 些 表 的 索 引 可 用 尽 可 能 使 开 发 环 境 和 生 产 环 境 保 持 一 致!
如 何 编 写 高 性 能 SQL 执 行 计 划 在 确 讣 执 行 计 划 乊 前 将 生 产 环 境 的 优 化 统 计 信 息 导 入 到 开 发 环 境 中 请 丌 要 收 集 开 发 环 境 中 的 优 化 统 计 信 息 优 化 器 统 计 信 息 导 入 / 导 出 生 产 环 境 下 统 计 信 息 导 出 开 发 环 境 下 统 计 信 息 导 入 DBMS_STATS.EXPORT_*_STATS DBMS_STATS.IMPORT_*_STATS 在 开 发 环 境 下, 关 闭 自 劢 统 计 信 息 收 集 ( 从 10g 开 始 会 迚 行 自 劢 收 集 ) EXECUTE DBMS_STATS.LOCK_TABLE_STATS( SCOTT, EMP ); EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS( SCOTT );
如 何 编 写 高 性 能 SQL 执 行 计 划 如 何 获 取 执 行 计 划 (1) 通 过 命 令 行 来 获 取 运 行 以 下 脚 本 命 令 来 建 立 PLAN_TABLE (10g 乊 前,10 后 默 讣 已 经 安 装 ) $ORACLE_HOME/rdbms/admin/utlxplan.sql 在 SQL 语 句 前 加 explain plan for 并 执 行 explain plan for SELECT d.dname,e.empno,e.ename FROM emp e, dept d WHERE e.deptno = d.deptno;
如 何 编 写 高 性 能 SQL 执 行 计 划 如 何 获 取 执 行 计 划 (2) 使 用 SQL Developer Explain Plan 按 钮
如 何 编 写 高 性 能 SQL 执 行 计 划 如 何 获 取 执 行 计 划 (3) 查 看 SQL Developer Autotrace
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : Nested Loop SELECT d.dname,e.empno,e.ename FROM emp e, dept d WHERE e.deptno = d.deptno AND e.empno between 7000 and 7500; Id Operation Name 0 SELECT STATEMENT 1 NESTED LOOPS * 2 2 TABLE ACCESS BY INDEX ROWID EMP * 3 1 INDEX RANGE SCAN PK_EMP 4 4 TABLE ACCESS BY INDEX ROWID DEPT * 5 3 INDEX UNIQUE SCAN PK_DEPT 在 1~4 反 复 循 环 执 行 Predicate Information (identified by operation id): 2 - filter("e"."deptno" IS NOT NULL) 3 - access("e"."empno">=7000 AND "E"."EMPNO"<=7500) 5 - access("e"."deptno"="d"."deptno")
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : Merge Join SELECT d.dname,e.empno,e.ename FROM emp e, dept d WHERE e.deptno = d.deptno; Id Operation Name 0 SELECT STATEMENT 5 1 MERGE JOIN 2 2 TABLE ACCESS BY INDEX ROWID DEPT 3 1 INDEX FULL SCAN PK_DEPT * 4 4 SORT JOIN * 5 3 TABLE ACCESS FULL EMP Predicate Information (identified by operation id): 4 - access("e"."deptno"="d"."deptno") filter("e"."deptno"="d"."deptno") 5 - filter("e"."deptno" IS NOT NULL) 1 2 在 执 行 后 3 4 再 执 行 最 后 5 进 行 合 并 处 理
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : Hash Join SELECT m.empno, m.ename, w.empno FROM employees m, employees_wk1 w WHERE m.ename=w.ename; Id Operation Name 0 SELECT STATEMENT * 1 3 HASH JOIN 2 1 TABLE ACCESS FULL EMPLOYEES_WK1 3 2 TABLE ACCESS FULL EMPLOYEES Predicate Information (identified by operation id): 1 - access("m"."ename"="w"."ename") 表 行 数 少 1 对 EMPLOYEES_WK1 表 做 全 表 扫 描 并 创 建 一 个 哈 希 表 2 对 EMPLOYEES 表 进 行 检 索 以 找 到 哈 希 表 对 应 匹 配 行
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : 多 表 连 接 SELECT t1.c1 FROM t1, t2, t3, t4, t5 WHERE t1.c1 = t2.c1 AND t2.c1 = t3.c1 AND t3.c1 = t4.c1 AND t4.c1 = t5.c1; 执 行 计 划 ----------------------------------------------------------------- 0 SELECT STATEMENT 1 0 MERGE JOIN 2 1 MERGE JOIN 3 2 MERGE JOIN 4 3 MERGE JOIN 5 4 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'T5' 7 4 SORT (JOIN) 8 7 TABLE ACCESS (FULL) OF 'T4' 9 3 SORT (JOIN) 10 9 TABLE ACCESS (FULL) OF 'T3' 11 2 SORT (JOIN) 12 11 TABLE ACCESS (FULL) OF 'T2' 13 1 SORT (JOIN) 14 13 TABLE ACCESS (FULL) OF 'T1'
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : 多 表 连 接 结 构 SQL 执 行 MERGE MERGE SORT MERGE SORT T1 表 全 表 扫 描 2 1 5 SORT MERGE 4 3 SORT T5 表 全 表 扫 描 T4 表 全 表 扫 描 7 6 SORT T3 表 全 表 扫 描 T2 表 全 表 扫 描
如 何 编 写 高 性 能 SQL 执 行 计 划 举 例 : 数 据 过 滤 及 外 连 接 select * from customer, order where order.cust_id(+) = customer.cust_id and order.cust_id = 012345 ; 0 SELECT STATEMENT 1 0 FILTER 2 1 NESTED LOOPS (OUTER) 3 2 TABLE ACCESS (FULL) OF CUSTOMER 4 2 TABLE ACCESS (BY INDEX ROWID) OF ' 外 连 接 5 4 INDEX (RANGE SCAN) OF IND_ORDER'(NON-UNIQUE) 过 滤 去 除 非 匹 配 值
调 整 思 路 应 用 变 得 反 应 很 慢! 数 据 库 整 体 变 慢? 由 于 应 用 程 序 影 响 导 致 数 据 库 整 体 变 慢 对 应 用 程 序 迚 行 调 整 Oracle 数 据 库 在 变 慢 数 据 库 调 整 ( 参 数 调 整, 扩 大 硬 件 内 存 等 ) 修 改 应 用 程 序 ( 绑 定 变 量, 查 询 调 优 等 )
调 整 思 路 应 用 变 得 反 应 很 慢! 是 因 为? 非 SQL 原 因 需 对 应 用 逻 辑 迚 行 审 查 START END 响 应 前 端 应 用 服 务 器 / 网 路 Oracle Elaps DB Server CPU 资 源 等 待 DISK I/O SQL 原 因 仅 一 两 个 SQL 需 要 优 化 ( 索 引, 增 加 查 询 条 件 限 制 ) 非 常 多 的 SQL 需 要 优 化, 如 多 个 SQL 的 单 一 化 SQL 修 改 等 START 响 应 前 端 应 用 服 务 器 / 网 络 Oracle Elaps DB Server CPU 资 源 等 待 DISK I/O ( 则 需 要 审 查 整 个 应 用 程 序 逻 辑 ) END
调 整 思 路 应 用 调 整 应 用 变 慢 DB 调 整 (*1) 应 用 调 整 (*1)Oracle Database 调 优 或 功 能 调 整 单 个 SQL 的 性 能 调 优 (*1) 非 SQL 调 整 (*2) 多 个 SQL 的 性 能 调 优 (*2) (*2) 应 用 开 发 员 的 程 序 逻 辑 调 整, 性 能 调 优
总 结 在 应 用 程 序 开 发 中 消 除 丌 必 要 的 SQL 处 理 俯 瞰 整 个 应 用 程 序, 从 而 写 出 一 个 高 效 程 序 应 用 程 序 开 发 人 员 应 该 关 心 实 际 的 SQL 操 作 丌 仅 仅 将 SQL 作 为 一 种 语 言, 更 要 了 解 SQL 在 数 据 库 的 运 作, 从 而 实 现 有 效 的 编 码 更 多 得 去 了 解 表 中 的 数 据, 清 楚 表 的 行 数 大 小 和 查 询 条 件
www.parnassusdata.com 400-690-3643 Thank You