外观
DM SQL执行计划
执行计划的作用
- 分析查询性能：识别SQL语句的性能瓶颈
- 优化查询语句：根据执行计划调整SQL语句
- 验证索引效果：确认索引是否被有效使用
- 理解查询逻辑：了解数据库如何执行SQL语句
- 预测执行成本：评估SQL语句的执行开销
执行计划的组成部分
- 操作类型：如全表扫描、索引扫描、连接操作等
- 操作对象：如表名、索引名
- 访问方法：如顺序访问、随机访问
- 连接方式：如嵌套循环连接、哈希连接、排序合并连接
- 排序方式：如升序、降序
- 成本估算：执行计划的估计成本
- 行数估算：估计返回的行数
执行计划生成原理
DM数据库的执行计划由优化器生成，优化器根据以下因素生成最优执行计划：
优化器类型
DM数据库支持两种优化器：
- 基于规则的优化器（RBO）：根据预定义的规则生成执行计划
- 基于成本的优化器（CBO）：根据统计信息和成本模型生成执行计划
优化器选择的影响因素
- 表的统计信息：包括表的行数、列的基数、直方图等
- 索引信息：索引的类型、大小、选择性等
- SQL语句的复杂度：简单查询和复杂查询的优化策略不同
- 系统资源状况：CPU、内存、IO等资源状况
- 初始化参数：如优化器模式、并行度等
- 表的大小：大表和小表的访问方式不同
执行计划生成流程
- 解析SQL语句：将SQL语句解析为内部表示
- 生成逻辑执行计划：根据SQL逻辑生成初步的执行计划
- 优化逻辑执行计划：应用优化规则优化执行计划
- 生成物理执行计划：将逻辑执行计划转换为物理执行计划
- 选择最优执行计划：根据成本模型选择最优执行计划
- 执行并监控：执行SQL语句并监控执行情况
查看执行计划的方法
使用EXPLAIN语句
EXPLAIN语句是查看执行计划的最常用方法，它可以显示SQL语句的执行计划，但不实际执行SQL语句。
基本语法
sql
EXPLAIN [PLAN FOR] sql_statement;使用示例
sql
-- 查看简单查询的执行计划
EXPLAIN SELECT * FROM emp WHERE deptno = 10;
-- 查看连接查询的执行计划
EXPLAIN SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno;
-- 查看包含子查询的执行计划
EXPLAIN SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');
-- 查看包含聚合函数的执行计划
EXPLAIN SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;使用EXPLAIN PLAN语句
EXPLAIN PLAN语句可以将执行计划存储到指定的表中，便于后续分析。
基本语法
sql
EXPLAIN PLAN [SET STATEMENT_ID = 'string'] FOR sql_statement;使用示例
sql
-- 将执行计划存储到默认表
EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10;
-- 查看存储的执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-- 使用自定义STATEMENT_ID
EXPLAIN PLAN SET STATEMENT_ID = 'test_plan' FOR SELECT * FROM emp;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'test_plan'));使用V$SQL_PLAN视图
V$SQL_PLAN视图存储了已执行SQL语句的执行计划，可以查看历史执行计划。
sql
-- 查看所有SQL执行计划
SELECT * FROM V$SQL_PLAN;
-- 查看特定SQL的执行计划
SELECT * FROM V$SQL_PLAN WHERE SQL_ID = '1234567890ABCDEF' ORDER BY ID;
-- 查看使用索引的SQL执行计划
SELECT DISTINCT SQL_ID, OPERATION, OPTIONS, OBJECT_NAME
FROM V$SQL_PLAN
WHERE OPERATION LIKE '%INDEX%';使用V$SQL_PLAN_STATISTICS_ALL视图
V$SQL_PLAN_STATISTICS_ALL视图提供了执行计划的实际执行统计信息。
sql
-- 查看执行计划的实际执行统计
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL WHERE SQL_ID = '1234567890ABCDEF' ORDER BY ID;使用DM管理工具
- 启动DM管理工具，连接到数据库实例
- 在SQL编辑器中输入SQL语句
- 点击"执行计划"按钮（或按F5键）
- 在执行计划窗口中查看执行计划
- 点击执行计划节点查看详细信息
使用DM性能监控工具
- 启动DM性能监控工具，连接到数据库实例
- 在左侧导航树中选择"性能" -> "SQL监控"
- 在右侧窗口中查看正在执行的SQL语句
- 点击SQL语句，查看其执行计划
- 分析执行计划并进行优化
执行计划结构分析
执行计划的基本结构
DM数据库的执行计划通常以树状结构显示，每个节点代表一个操作，包含以下信息：
- ID：操作节点的唯一标识符
- PARENT_ID：父操作节点的ID
- OPERATION：操作类型
- OPTIONS：操作选项
- OBJECT_NAME：操作对象名称
- OBJECT_TYPE：操作对象类型
- COST：估计成本
- CARDINALITY：估计返回行数
- BYTES：估计返回字节数
- ACCESS_PREDICATES：访问谓词
- FILTER_PREDICATES：过滤谓词
执行计划示例分析
以一个简单的查询为例，分析执行计划结构：
sql
EXPLAIN SELECT * FROM emp WHERE deptno = 10 AND sal > 2000;执行计划输出：
ID | PARENT_ID | OPERATION | OPTIONS | OBJECT_NAME | OBJECT_TYPE | COST | CARDINALITY | BYTES | ACCESS_PREDICATES | FILTER_PREDICATES
---|-----------|-------------------|-----------------|-------------|-------------|------|-------------|-------|-------------------------|-------------------
1 | 0 | SELECT STATEMENT | | | | 5 | 2 | 200 | |
2 | 1 | TABLE ACCESS | BY INDEX ROWID | EMP | TABLE | 5 | 2 | 200 | | SAL > 2000
3 | 2 | INDEX RANGE SCAN | | IDX_EMP_DEPTNO | INDEX | 3 | 5 | | DEPTNO = 10 |执行计划分析
- SELECT STATEMENT：根节点，表示SELECT语句
- TABLE ACCESS BY INDEX ROWID：通过索引ROWID访问表EMP
- FILTER_PREDICATES：SAL > 2000，表示在表访问后过滤sal > 2000的行
- INDEX RANGE SCAN：对索引IDX_EMP_DEPTNO进行范围扫描
- ACCESS_PREDICATES：DEPTNO = 10，表示使用索引访问deptno = 10的行
- 估计返回5行，然后过滤出sal > 2000的行，最终返回2行
执行计划操作类型
表访问操作
FULL TABLE SCAN
描述：全表扫描，扫描表的所有行 适用场景：表很小，或者没有合适的索引 示例：
sql
EXPLAIN SELECT * FROM emp;TABLE ACCESS BY INDEX ROWID
描述：通过索引ROWID访问表 适用场景：使用索引找到ROWID，然后访问表数据 示例：
sql
EXPLAIN SELECT * FROM emp WHERE deptno = 10;TABLE ACCESS BY GLOBAL INDEX ROWID
描述：通过全局索引ROWID访问分区表 适用场景：分区表的全局索引访问
索引访问操作
INDEX FULL SCAN
描述：全索引扫描，扫描索引的所有行 适用场景：索引包含查询所需的所有列（覆盖索引） 示例：
sql
EXPLAIN SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;INDEX RANGE SCAN
描述：索引范围扫描，扫描索引的特定范围 适用场景：等值查询、范围查询 示例：
sql
EXPLAIN SELECT * FROM emp WHERE deptno = 10;INDEX UNIQUE SCAN
描述：唯一索引扫描，扫描唯一索引的一行 适用场景：唯一索引的等值查询 示例：
sql
EXPLAIN SELECT * FROM emp WHERE empno = 7839;INDEX SKIP SCAN
描述：索引跳跃扫描，适用于复合索引 适用场景：复合索引的前缀列没有指定条件
连接操作
NESTED LOOPS
描述：嵌套循环连接 适用场景：小表连接，或有合适索引的连接 示例：
sql
EXPLAIN SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno;HASH JOIN
描述：哈希连接 适用场景：大表连接，没有合适索引 示例：
sql
EXPLAIN SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno;SORT MERGE JOIN
描述：排序合并连接 适用场景：两个表都已排序，或需要排序输出
排序操作
SORT AGGREGATE
描述：聚合排序，用于聚合函数 适用场景：COUNT、SUM、AVG等聚合函数 示例：
sql
EXPLAIN SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;SORT ORDER BY
描述：排序操作，用于ORDER BY子句 适用场景：ORDER BY子句 示例：
sql
EXPLAIN SELECT * FROM emp ORDER BY deptno, sal DESC;SORT GROUP BY
描述：分组排序，用于GROUP BY子句 适用场景：GROUP BY子句 示例：
sql
EXPLAIN SELECT deptno, job, COUNT(*) FROM emp GROUP BY deptno, job;执行计划优化技巧
1. 识别全表扫描
问题：全表扫描（FULL TABLE SCAN）通常效率较低，特别是对于大表 解决方法：
- 添加合适的索引
- 优化WHERE条件，减少返回行数
- 考虑使用分区表
2. 优化索引使用
问题：索引没有被有效使用 解决方法：
- 检查索引是否存在
- 检查WHERE条件是否使用了索引列
- 避免在索引列上使用函数
- 考虑修改索引列顺序
3. 优化连接操作
问题：连接操作效率低下 解决方法：
- 为连接列添加索引
- 优化连接顺序
- 选择合适的连接方式
- 考虑使用并行连接
4. 优化排序操作
问题：排序操作消耗大量资源 解决方法：
- 避免不必要的排序
- 使用索引避免排序
- 优化ORDER BY和GROUP BY子句
- 考虑增加排序区大小
5. 优化子查询
问题：子查询效率低下 解决方法：
- 考虑使用连接替代子查询
- 优化子查询的WHERE条件
- 考虑使用物化视图
6. 优化聚合操作
问题：聚合操作效率低下 解决方法：
- 为聚合列添加索引
- 考虑使用分区表
- 考虑使用并行聚合
- 优化GROUP BY子句
执行计划相关视图
V$SQL_PLAN
作用：存储已执行SQL语句的执行计划 主要列：SQL_ID, PLAN_ID, OPERATION, OPTIONS, OBJECT_NAME, COST, CARDINALITY
V$SQL_PLAN_STATISTICS_ALL
作用：提供执行计划的实际执行统计信息 主要列：SQL_ID, PLAN_ID, OPERATION, ACTUAL_ROWS, ACTUAL_TIME, PHYSICAL_READS
V$SQL
作用：存储已执行SQL语句的信息 主要列：SQL_ID, SQL_TEXT, EXECUTIONS, TOTAL_TIME, ELAPSED_TIME
V$SQL_STATISTICS
作用：存储SQL语句的执行统计信息 主要列：SQL_ID, SQL_TEXT, EXECUTIONS, TOTAL_TIME, CPU_TIME, IO_WAIT_TIME
DBA_HIST_SQL_PLAN
作用：存储历史SQL执行计划 主要列：SQL_ID, PLAN_HASH_VALUE, OPERATION, OPTIONS, OBJECT_NAME
执行计划最佳实践
1. 定期分析执行计划
- 定期分析频繁执行的SQL语句的执行计划
- 分析AWR报告中的TOP SQL执行计划
- 在SQL语句上线前分析执行计划
2. 使用绑定变量
- 使用绑定变量可以提高执行计划的重用率
- 避免硬解析，提高SQL执行效率
3. 收集统计信息
- 定期收集表和索引的统计信息
- 确保优化器有准确的统计信息生成最优执行计划
4. 测试不同执行计划
- 使用HINT提示强制使用不同的执行计划
- 比较不同执行计划的性能差异
- 选择最优执行计划
5. 监控执行计划变化
- 监控执行计划的变化
- 分析执行计划变化的原因
- 及时调整SQL语句或索引
6. 结合实际执行统计
- 不仅要看估计执行计划，还要看实际执行统计
- 比较估计值和实际值的差异
- 调整统计信息或执行计划
版本差异说明
| 版本 | 主要变化 |
|---|---|
| DM 7 | 支持基本的执行计划查看和分析功能 |
| DM 8 | 增强了执行计划功能，支持更多操作类型和优化策略 |
| DM 8.1 | 优化了执行计划的生成性能，支持并行执行计划 |
常见问题（FAQ）
Q1: 为什么执行计划与实际执行情况不符？
A1: 执行计划与实际执行情况不符的原因：
- 统计信息不准确：需要重新收集统计信息
- 绑定变量窥探：使用绑定变量可能导致执行计划不准确
- 系统资源变化：系统资源变化可能影响执行计划
- 执行计划过时：需要重新生成执行计划
Q2: 如何强制使用特定的执行计划？
A2: 可以使用HINT提示强制使用特定的执行计划：
sql
-- 强制使用索引
SELECT /*+ INDEX(emp idx_emp_deptno) */ * FROM emp WHERE deptno = 10;
-- 强制使用哈希连接
SELECT /*+ USE_HASH(e d) */ e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno;Q3: 如何查看执行计划的成本计算？
A3: 可以通过以下方法查看执行计划的成本计算：
- 使用EXPLAIN语句查看COST列
- 分析V$SQL_PLAN视图的COST列
- 使用DM管理工具查看执行计划的成本信息
Q4: 执行计划中的"CARDINALITY"和"ACTUAL_ROWS"有什么区别？
A4: "CARDINALITY"是优化器估计的行数，"ACTUAL_ROWS"是实际执行返回的行数。两者的差异反映了优化器统计信息的准确性。
Q5: 如何优化复杂查询的执行计划？
A5: 优化复杂查询执行计划的方法：
- 分解复杂查询为多个简单查询
- 优化每个子查询的执行计划
- 考虑使用物化视图
- 调整查询逻辑
- 添加合适的索引
Q6: 如何识别执行计划中的性能瓶颈？
A6: 识别执行计划性能瓶颈的方法：
- 查看执行计划的成本分布
- 分析实际执行统计信息
- 关注耗时最长的操作
- 查看等待事件信息
Q7: 为什么添加索引后执行计划没有使用？
A7: 添加索引后执行计划没有使用的原因：
- 索引选择性不高：索引的选择性太低，优化器认为全表扫描更高效
- 统计信息不准确：需要重新收集统计信息
- SQL语句没有使用索引列：WHERE条件中没有使用索引列
- 索引列上使用了函数：索引列上的函数导致索引失效
Q8: 如何比较不同执行计划的性能？
A8: 比较不同执行计划性能的方法：
- 比较执行计划的成本
- 比较实际执行时间
- 比较资源消耗（CPU、内存、IO）
- 比较返回行数
Q9: 如何查看历史执行计划？
A9: 查看历史执行计划的方法：
- 查询DBA_HIST_SQL_PLAN视图
- 分析AWR报告中的执行计划
- 使用DM性能监控工具查看历史执行计划
Q10: 执行计划中的"BY INDEX ROWID"和"INDEX RANGE SCAN"有什么区别？
A10: "INDEX RANGE SCAN"是索引范围扫描，用于找到符合条件的ROWID；"BY INDEX ROWID"是通过ROWID访问表数据。两者通常一起使用，先通过索引找到ROWID，然后通过ROWID访问表数据。
在实际工作中，DBA应该结合多种方法查看执行计划，包括EXPLAIN语句、系统视图、管理工具等，并结合实际执行统计信息，全面分析执行计划。同时，需要定期收集统计信息，使用绑定变量，优化索引设计，确保优化器生成最优执行计划。
通过合理使用执行计划，DBA可以显著提高DM数据库的查询性能，降低系统资源消耗，为业务提供高效、稳定的数据服务。