11.2 overview

Transcription

1 1

2 < 在 此 处 插 入 图 片 > Explain Plan 命 令 说 明 Maria Colgan

3 免 责 声 明 本 讲 座 旨 在 为 您 提 供 有 关 如 何 阅 读 SQL 执 行 计 划 的 说 明, 并 帮 助 您 确 定 该 计 划 是 否 满 足 您 的 要 求 本 讲 座 并 不 能 使 您 一 举 成 为 优 化 器 专 家, 也 无 法 使 您 具 备 轻 松 调 整 SQL 语 句 的 能 力!

4 议 题 什 么 是 执 行 计 划, 如 何 生 成 执 行 计 划? 一 个 优 秀 的 优 化 器 计 划 是 什 么 样 的? 理 解 执 行 计 划 基 数 访 问 方 法 联 接 顺 序 联 接 类 型 分 区 修 剪 并 行 度 执 行 计 划 示 例

5 什 么 是 执 行 计 划, 如 何 生 成 执 行 计 划? < 在 此 处 插 入 图 片 >

6 什 么 是 执 行 计 划? 执 行 计 划 显 示 在 执 行 一 条 SQL 语 句 时 必 须 执 行 的 详 细 步 骤 这 些 步 骤 表 示 为 一 组 数 据 库 运 算 符, 这 些 运 算 符 将 使 用 和 生 成 行 这 些 运 算 符 及 其 实 施 的 顺 序 由 优 化 器 使 用 查 询 转 换 及 物 理 优 化 技 术 的 组 合 来 确 定 执 行 计 划 通 常 以 表 格 的 形 式 显 示, 但 它 实 际 上 为 树 形

7 什 么 是 执 行 计 划? 查 询 SELECT prod_category, avg(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category; 执 行 计 划 的 表 格 表 示 Id Operation Name SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES 执 行 计 划 的 树 形 表 示 TABLE ACCESS PRODUCTS Group By HASH JOIN TABLE ACCESS SALES

8 如 何 获 取 执 行 计 划 可 以 使 用 两 种 方 法 查 看 执 行 计 划 1.EXPLAIN PLAN 命 令 显 示 一 条 SQL 语 句 的 执 行 计 划, 而 不 实 际 执 行 此 语 句 2.V$SQL_PLAN 在 Oracle 9i 中 引 入 的 字 典 视 图, 它 可 显 示 已 编 译 到 游 标 缓 存 中 一 个 游 标 的 一 条 SQL 语 句 的 执 行 计 划 使 用 DBMS_XPLAN 包 来 显 示 执 行 计 划 在 某 些 情 况 下, 使 用 EXPLAIN PLAN 显 示 的 计 划 可 能 与 使 用 V$SQL_PLAN 显 示 的 计 划 不 同

9 如 何 获 取 执 行 计 划 示 例 1 EXPLAIN PLAN 命 令 和 dbms_xplan.display 函 数 SQL> EXPLAIN PLAN FOR SELECT prod_category, avg(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category; Explained SQL> SELECT plan_table_output FROM table(dbms_xplan.display('plan_table',null,'basic')); Id Operation Name SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES

10 如 何 获 取 执 行 计 划 示 例 2 生 成 并 显 示 在 会 话 中 最 后 执 行 的 SQL 语 句 的 执 行 计 划 SQL>SELECT prod_category, avg(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category; no rows selected SQL> SELECT plan_table_output FROM table(dbms_xplan.display_cursor(null,null,'basic')); Id Operation Name SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES

11 一 个 优 秀 的 优 化 器 计 划 是 什 么 样 的? < 在 此 处 插 入 图 片 >

12 一 个 优 秀 的 优 化 器 计 划 是 什 么 样 的? 优 化 器 有 两 个 不 同 的 目 标 串 行 执 行 : 其 关 注 的 是 开 销 开 销 越 低 越 好 并 行 执 行 : 其 关 注 的 是 性 能 速 度 越 快 越 好 两 个 基 本 问 题 : 什 么 是 开 销? 什 么 是 性 能?

13 什 么 是 开 销? 优 化 器 生 成 的 神 奇 数 字? 执 行 SQL 语 句 所 需 的 资 源? 复 杂 计 算 的 结 果? 执 行 语 句 所 需 时 间 的 估 计? 实 际 定 义 开 销 指 的 是 所 使 用 的 工 作 单 元 或 资 源 的 数 量 优 化 器 用 CPU 内 存 使 用 和 IO 作 为 工 作 单 元 开 销 是 对 执 行 操 作 时 要 使 用 的 CPU 和 内 存 量 以 及 磁 盘 I/O 数 的 估 计 开 销 是 Oracle 的 一 个 内 部 量 度

14 性 能 是 什 么? 完 成 尽 可 能 多 的 查 询? 使 用 最 少 的 资 源 获 得 尽 可 能 快 的 运 行 速 度? 获 得 最 佳 的 并 发 率? 实 际 定 义 性 能 指 的 是 对 查 询 的 最 快 响 应 时 间 目 标 是 尽 可 能 快 地 完 成 查 询 操 作 优 化 器 不 关 注 执 行 计 划 所 需 的 资 源

15 理 解 执 行 计 划 < 在 此 处 插 入 图 片 >

16 SQL 执 行 计 划 您 在 查 看 计 划 时 能 否 确 定 以 下 项 是 否 正 确? 基 数 每 个 对 象 是 否 生 成 正 确 的 行 数? 访 问 方 法 是 否 以 最 好 的 方 式 访 问 数 据? 扫 描? 索 引 查 找? 联 接 顺 序 是 否 以 正 确 的 顺 序 联 接 各 表 以 便 尽 早 尽 多 地 消 除 数 据? 联 接 类 型 是 否 使 用 了 正 确 的 联 接 类 型? 分 区 修 剪 我 执 行 过 分 区 修 剪 吗? 是 否 消 除 了 足 够 多 的 数 据? 并 行 度

17 基 数 什 么 是 基 数? 估 算 将 返 回 的 行 数 单 值 谓 词 的 基 数 = 行 的 总 数 / 不 同 值 的 总 数 例 如 : 共 100 行, 共 10 个 不 同 值 => 基 数 = 10 行 或 者, 如 果 为 柱 状 图 表 示, 则 是 行 数 * 密 度 为 什 么 要 关 注? 它 将 影 响 所 有 方 面! 访 问 方 法 联 接 类 型 联 接 顺 序 等 哪 些 因 素 会 导 致 基 数 出 错? 统 计 信 息 陈 旧 / 缺 少 数 据 偏 差 一 个 表 有 多 个 单 列 谓 词 where 子 句 谓 词 中 包 含 函 数 复 杂 表 达 式, 其 中 包 含 来 自 不 同 表 的 列

18 基 数 或 选 择 度 估 算 返 回 行 数 的 基 数 使 用 简 单 的 SELECT COUNT(*) 从 每 个 表 应 用 任 何 属 于 该 表 的 WHERE 子 句 谓 词 确 定 正 确 的 基 数

19 使 用 以 下 代 码 查 看 基 数 SELECT /*+ gather_plan_statistics */ p.prod_name as product, sum(s.quantity_sold) as units, FROM sales s, products p WHERE s.prod_id =p.prod_id GROUP BY p.prod_name; SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); 比 较 计 划 中 每 个 操 作 的 估 算 返 回 行 数 与 实 际 返 回 行 数

20 使 用 SQL 监 视 器 查 看 基 数 利 用 SQL 监 视 器, 您 可 以 比 较 计 划 中 每 个 操 作 的 估 算 返 回 行 数 与 实 际 返 回 行 数

21 有 关 解 决 基 数 问 题 的 建 议 原 因 统 计 信 息 陈 旧 / 缺 少 解 决 方 法 DBMS_STATS 数 据 偏 差 创 建 一 个 柱 状 图 * 一 个 表 有 多 个 单 列 谓 词 在 一 个 联 接 中 使 用 多 个 列 包 含 函 数 的 列 复 杂 表 达 式, 其 中 包 含 来 自 多 个 表 的 列 使 用 DBMS_STATS.CREATE_EXTENDED_STATS 创 建 一 个 列 组 使 用 DBMS_STATS.CREATE_EXTENDED_STATS 创 建 一 个 列 组 使 用 DBMS_STATS.CREATE_EXTENDED_STATS 创 建 有 关 包 含 函 数 的 列 的 统 计 信 息 使 用 4 级 或 更 高 的 动 态 抽 样 级 别 * 柱 状 图 会 对 具 有 11g 之 前 的 绑 定 的 语 句 产 生 令 人 注 目 的 副 作 用 请 谨 慎 使 用

22 访 问 方 法 获 取 数 据 访 问 方 法 完 整 表 扫 描 按 ROWID 访 问 表 索 引 唯 一 扫 描 索 引 范 围 扫 描 索 引 跳 过 扫 描 完 整 索 引 扫 描 快 速 完 整 索 引 扫 描 索 引 联 接 位 图 索 引 解 释 读 取 表 中 所 有 行 并 过 滤 掉 那 些 不 符 合 WHERE 子 句 谓 词 的 行 用 于 索 引 DOP 集 等 ROWID 指 定 含 有 所 需 行 的 数 据 文 件 和 数 据 块 以 及 该 行 在 该 块 中 的 位 置 当 在 索 引 或 WHERE 子 句 中 提 供 rowid 时 使 用 将 只 返 回 一 行 当 语 句 中 包 含 UNIQUE 或 PRIMARY KEY 约 束 条 件 时 使 用, 这 些 约 束 条 件 用 于 保 证 只 访 问 一 行 访 问 相 邻 索 引 项, 可 返 回 多 个 ROWID 值 与 等 式 一 起 用 于 非 唯 一 索 引, 或 与 范 围 谓 词 一 起 用 于 唯 一 索 引 (<.> between 等 ) 如 果 前 导 列 中 只 有 很 少 的 不 同 值, 而 非 前 导 列 中 有 许 多 不 同 的 值, 则 跳 过 索 引 的 前 导 部 分, 使 用 其 余 有 用 的 部 分 处 理 索 引 的 所 有 叶 块, 但 只 有 经 过 足 够 多 的 分 支 块 才 能 找 到 第 1 个 叶 块 当 所 有 需 要 的 列 都 位 于 索 引 中 且 order by 子 句 与 索 引 结 构 匹 配, 或 者 排 序 合 并 联 接 已 完 成 时, 即 可 使 用 扫 描 索 引 中 的 所 有 块, 用 来 在 所 有 需 要 的 列 都 在 索 引 中 时 代 替 FTS 使 用 多 块 IO, 可 以 并 行 运 行 散 列 联 接 多 个 索 引, 这 些 索 引 一 起 包 含 有 查 询 中 引 用 的 所 有 表 列 不 会 消 除 排 序 操 作 使 用 键 值 位 图 和 映 射 函 数, 映 射 函 数 可 将 每 个 比 特 的 位 置 转 换 成 一 个 rowid 可 以 有 效 地 合 并 对 应 于 WHERE 子 句 中 的 多 个 条 件 的 索 引

23 访 问 方 法 如 果 发 现 使 用 了 错 误 的 访 问 方 法, 请 检 查 基 数 联 接 顺 序... 查 看 Operation 部 分 以 了 解 对 象 的 访 问 方 式

24 访 问 方 法 示 例 一 个 countries 表 包 含 10K 行, 并 且 有 一 个 country_id 主 键 您 希 望 对 以 下 查 询 使 用 什 么 计 划? Select country_id, name from countries where country_id in ('AU','FR','IE ); Select country_id, name from countries where country_id between 'AU' and 'IE'; Select country_id, name from countries where name='usa';

25 有 关 解 决 访 问 的 建 议 问 题 使 用 表 扫 描, 而 不 是 索 引 扫 描 采 用 错 误 的 索 引 原 因 DOP 针 对 表, 而 不 是 索 引 或 MBRC 值 统 计 信 息 陈 旧 / 缺 少 采 用 了 匹 配 最 多 列 的 索 引 完 整 索 引 访 问 方 式 的 开 销 要 比 索 引 查 找 后 跟 表 访 问 方 式 的 开 销 低 * 柱 状 图 会 对 具 有 11g 之 前 的 绑 定 的 语 句 产 生 令 人 注 目 的 副 作 用 请 谨 慎 使 用

26 联 接 类 型 联 接 可 从 多 个 表 中 检 索 数 据 访 问 方 法 解 释 嵌 套 循 环 联 接 散 列 联 接 对 于 外 部 表 中 的 每 一 行,Oracle 访 问 内 部 表 中 的 所 有 行 当 联 接 多 个 小 型 数 据 子 集, 并 且 有 一 个 高 效 的 方 法 ( 索 引 查 找 ) 来 访 问 第 二 个 表 时, 这 非 常 有 用 对 两 个 表 中 较 小 的 表 执 行 扫 描, 使 用 结 果 行 根 据 内 存 中 的 联 接 键 创 建 散 列 表 然 后 扫 描 较 大 的 表, 对 结 果 行 的 联 接 列 执 行 散 列 操 作, 并 用 其 值 探 测 散 列 表 以 查 找 匹 配 的 行 对 于 较 大 的 表 和 if equality 谓 词, 这 非 常 有 用 排 序 合 并 联 接 包 括 两 个 步 骤 : 1. 排 序 联 接 操 作 : 基 于 联 接 键 对 两 个 输 入 都 进 行 排 序 2. 合 并 联 接 操 作 : 将 排 序 的 列 表 合 并 在 一 起 当 两 个 表 之 间 的 联 接 条 件 是 不 相 等 条 件 时, 这 非 常 有 用 笛 卡 尔 联 接 外 联 接 将 来 自 一 个 数 据 源 的 每 一 行 与 来 自 另 一 个 数 据 源 的 每 一 行 进 行 联 接, 生 成 这 两 个 数 据 集 的 笛 卡 尔 乘 积 只 有 在 表 非 常 小 时 才 适 用 如 果 没 有 在 查 询 中 指 定 任 何 联 接 条 件, 则 这 是 唯 一 的 选 择 返 回 所 有 满 足 联 接 条 件 的 行, 并 从 没 有 (+) 的 表 中 返 回 所 有 这 样 的 行 : 在 另 一 个 表 中 没 有 满 足 联 接 条 件 的 行

27 联 接 类 型 查 看 Operation 部 分 以 检 查 是 否 使 用 了 正 确 联 接 类 型 如 果 使 用 了 错 误 的 联 接 类 型, 则 返 回 并 检 查 所 编 写 的 语 句 是 否 正 确, 以 及 估 算 的 基 数 是 否 正 确

28 联 接 类 型 示 例 1 应 为 此 查 询 使 用 什 么 联 接 类 型? SELECT e.name, e.salary, d.dept_name FROM hr.employees e, hr.departments d WHERE d.dept_name IN ('Marketing,'Sales') AND e.department_id=d.department_id; Employees 有 107 行 Departments 有 27 行 Employees 和 Departments 之 间 基 于 dept_id 的 外 键 关 系

29 联 接 类 型 示 例 2 应 为 此 查 询 使 用 什 么 联 接 类 型? SELECT o.customer_id, l.unit_price * l.quantity FROM oe.orders o,oe.order_items l WHERE l.order_id = o.order_id; Orders 有 105 行 Order Items 有 665 行

30 联 接 类 型 示 例 3 应 为 此 查 询 使 用 什 么 联 接 类 型? SELECT o.order_id,0.order_date,e.name FROM oe.orders o, hr.employees e; Orders 有 105 行 Employees 有 107 行

31 联 接 类 型 示 例 4 应 为 此 查 询 使 用 什 么 联 接 类 型? SELECT d.department_id,e.emp_id FROM hr.employees e FULL OUTER JOIN hr.departments d ON e.department_id = d.department_id ORDER BY d.department_id; Employees 有 107 行 Departments 有 27 行 Employees 和 Departments 之 间 基 于 dept_id 的 外 键 关 系

32 联 接 顺 序 在 多 表 语 句 中 对 多 个 表 进 行 联 接 的 顺 序 应 该 从 可 消 除 最 多 行 数 的 表 开 始 操 作 将 受 可 用 访 问 方 法 的 很 大 影 响 一 些 基 本 规 则 总 是 最 先 执 行 最 多 生 成 一 行 的 联 接 当 使 用 外 联 接 时, 在 谓 词 中, 含 有 此 外 联 接 运 算 符 的 表 必 须 位 于 其 他 表 之 后 如 果 不 能 执 行 视 图 合 并, 在 联 接 视 图 外 部 的 表 之 前 联 接 视 图 内 部 的 所 有 表

33 连 接 顺 序 如 果 联 接 顺 序 不 正 确, 请 检 查 统 计 信 息 基 数 及 访 问 方 法 需 要 从 可 最 大 程 度 减 少 结 果 集 行 数 的 表 开 始 操 作

34 分 区 修 剪 问 :2008 年 5 月 日 这 几 天 的 总 销 售 额 是 多 少? Sales 表 2008 年 5 月 18 日 2008 年 5 月 19 日 Select sum(sales_amount) From SALES Where sales_date between to_date( 05/20/2008, MM/DD/YYYY ) and to_date( 05/23/2008, MM/DD/YYYY ); 仅 访 问 3 个 相 关 的 分 区 2008 年 5 月 20 日 2008 年 5 月 21 日 2008 年 5 月 22 日 2008 年 5 月 23 日 2008 年 5 月 24 日

35 分 区 修 剪 如 果 显 示 了 单 词 KEY, 则 意 味 着 将 在 运 行 时 确 定 所 访 问 的 分 区 Pstart 和 Pstop 列 出 了 查 询 操 作 所 访 问 的 分 区

36 并 行 度 目 标 是 并 行 执 行 计 划 的 所 有 方 面 确 定 是 使 用 一 组 还 是 多 组 并 行 服 务 器 进 程 生 产 者 和 使 用 者 确 定 计 划 中 是 否 有 任 何 串 行 运 行 的 部 分

37 并 行 度 IN-OUT 列 显 示 出 哪 些 步 骤 是 并 行 运 行 的, 以 及 使 用 的 是 一 组 还 是 多 组 并 行 服 务 器 进 程 如 果 任 何 行 显 示 为 以 字 母 "S" 开 头, 则 表 明 将 以 串 行 方 式 运 行, 检 查 所 使 用 的 每 个 表 和 索 引 的 DOP

38 确 定 在 计 划 的 扫 描 过 程 中 的 并 行 度 粒 度 数 据 被 划 分 为 以 下 粒 度 块 范 围 分 区 将 为 每 个 并 行 服 务 器 分 配 一 个 或 多 个 粒 度 该 粒 度 方 法 在 Operation 部 分 中 的 扫 描 内 容 的 上 一 行 中 指 定

39 确 定 计 划 中 扫 描 过 程 中 的 并 行 度 粒 度

40 访 问 方 法 及 其 并 行 执 行 方 式 访 问 方 法 完 整 表 扫 描 按 ROWID 访 问 表 索 引 唯 一 扫 描 索 引 范 围 扫 描 ( 降 序 ) 索 引 跳 过 扫 描 完 整 索 引 扫 描 快 速 完 整 索 引 扫 描 位 图 索 引 ( 以 星 型 转 换 方 式 ) 并 行 化 方 法 块 迭 代 器 分 区 分 区 分 区 分 区 分 区 块 迭 代 器 块 迭 代 器

41 并 行 分 发 当 使 用 生 产 者 与 使 用 者 组 时 是 必 需 的 生 产 者 必 须 将 其 数 据 传 递 或 分 发 到 使 用 者 将 行 传 递 到 的 运 算 符 负 责 确 定 分 发 分 发 可 以 在 本 地 执 行, 也 可 以 在 RAC 中 的 其 他 节 点 上 执 行 五 种 常 见 的 重 新 分 发

42 并 行 分 发 散 列 假 定 其 中 一 个 表 是 散 列 分 区 形 式 对 联 接 列 的 值 应 用 散 列 函 数 分 发 到 基 于 相 应 散 列 分 区 而 工 作 的 使 用 者 广 播 其 中 一 个 结 果 集 的 是 小 型 结 果 集 向 所 有 使 用 者 发 送 数 据 副 本 范 围 通 常 用 于 并 行 排 序 操 作 各 并 行 服 务 器 基 于 数 据 范 围 而 工 作 QC 无 需 进 行 排 序, 即 可 以 正 确 的 顺 序 显 示 并 行 服 务 器 结 果 分 区 键 分 发 PART (KEY) 假 定 目 标 表 已 进 行 分 区 目 标 表 的 分 区 被 映 射 到 并 行 服 务 器 生 产 者 基 于 分 区 列 将 扫 描 的 每 行 映 射 到 使 用 者 循 环 随 机 但 均 匀 地 在 使 用 者 中 分 发 数 据

43 并 行 分 发 显 示 PQ 服 务 器 如 何 在 彼 此 之 间 分 发 行

44 阅 读 计 划 示 例 < 在 此 处 插 入 图 片 >

45 示 例 SQL 语 句 和 方 框 图 SELECT '(' pcode ')' pcode_desc AS PRODUCT, CNT FROM (SELECT a.pcode, b.pcode_desc, count(a.pcode) CNT FROM t_acct_master_hd a,hogan_pcode_hd_ref b, t_tran_detail_hd c WHERE a.pcode = b.pcode AND a.acct_num=c.acct_num AND a.co_id=c.co_id AND c.asof_yyyymm= AND c.tran_amt < GROUP BY a.pcode, b.pcode_desc ORDER BY a.pcode, b.pcode_desc ) HOGAN_PCODE_HD_REF T_TRAN_DETAIL_HD ACCT_NUM CO_ID PCOD E T_ACCT_MASTER_HD 数 TB 大 小 1 GB 大 小

46 执 行 计 划 示 例 ( 续 ) 1. 检 查 所 返 回 的 行 数 是 否 大 致 正 确 2. 基 数 估 算 是 否 正 确? 3. 访 问 方 法 是 否 正 确? 意 味 着 没 有 收 集 到 统 计 信 息, 强 烈 表 明 这 不 是 最 佳 计 划

47 执 行 计 划 示 例 ( 续 ) 是 否 使 用 了 正 确 的 联 接 方 法? 6. 联 接 顺 序 是 否 正 确? 是 否 首 先 访 问 可 消 除 最 多 行 的 表? 4. 是 否 已 发 生 分 区 修 剪?

48 执 行 计 划 示 例 ( 续 ) 7. 检 查 是 否 并 行 执 行 计 划 的 所 有 方 面 8. 检 查 分 发 方 法, 确 保 不 会 广 播 大 型 表

49 执 行 计 划 示 例 ( 续 ) 解 决 方 案 1. 现 在 实 际 只 返 回 了 一 行, 开 销 降 低 为 原 来 的 四 分 之 一 基 数 是 正 确 的, 并 且 对 于 每 个 联 接, 行 数 减 少 4. 分 区 修 剪, 一 个 范 围 分 区, 四 个 散 列 分 区 7. 并 行 执 行 计 划 的 所 有 方 面 8. 现 在 的 行 分 布 都 是 散 列 分 布 6. 联 接 顺 序 已 改 变 PWJ, 将 散 列 联 接 到 查 找 表 5. 联 接 类 型 仍 然 是 是 散 列 联 接, 但 现 在 是 PWJ 3. 访 问 方 法 保 持 不 变

50 查 询 确 定 是 否 获 得 了 合 适 的 计 划 SELECT quantity_sold FROM sales s, customers c WHERE s.cust_id =c.cust_id;id 您 期 望 此 语 句 的 计 划 应 是 什 么 样 的?S NOT NULL) 说 明 联 接 到 customers 是 多 余 的, 因 为 没 有 选 择 列 存 在 主 键 外 键 关 系 意 味 着 我 们 可 以 删 除 表

51 问 答