印刻数据库

外观

Oracle 执行计划分析与优化 ​

获取执行计划的方法 ​

EXPLAIN PLAN 命令 ​

sql
-- 生成执行计划
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE deptno = 10;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看详细执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

AUTOTRACE 工具 ​

sql
-- 启用AUTOTRACE
SET AUTOTRACE ON;

-- 执行SQL并查看执行计划
SELECT * FROM emp WHERE deptno = 10;

-- 只查看执行计划，不执行SQL
SET AUTOTRACE TRACEONLY;

-- 禁用AUTOTRACE
SET AUTOTRACE OFF;

V$SQL_PLAN 视图 ​

sql
-- 查找SQL_ID
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%SELECT * FROM emp%';

-- 查看执行计划
SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id' ORDER BY id;

-- 使用DBMS_XPLAN查看格式化执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id'));

SQL Developer 工具 ​

  • 在SQL Worksheet中执行SQL语句
  • 点击"Explain Plan"按钮查看执行计划
  • 可以查看可视化的执行计划图表
  • 提供详细的成本和统计信息

执行计划解读 ​

执行计划结构 ​

执行计划是一个树形结构，从根节点开始，每个节点代表一个操作步骤。执行顺序通常是从下往上，从右到左。

常用操作类型 ​

  • TABLE ACCESS FULL：全表扫描
  • TABLE ACCESS BY INDEX ROWID：通过索引ROWID访问表
  • INDEX UNIQUE SCAN：唯一索引扫描
  • INDEX RANGE SCAN：索引范围扫描
  • INDEX FULL SCAN：全索引扫描
  • INDEX FAST FULL SCAN：快速全索引扫描
  • NESTED LOOPS：嵌套循环连接
  • HASH JOIN：哈希连接
  • MERGE JOIN：合并连接
  • SORT ORDER BY：排序操作
  • FILTER：过滤操作
  • VIEW：视图操作

成本信息解读 ​

  • COST：操作的估计成本
  • CARDINALITY：估计返回的行数
  • BYTES：估计返回的数据量
  • CPU_COST：估计的CPU成本
  • IO_COST：估计的I/O成本

执行计划中的提示信息 ​

  • Starts：操作执行的次数
  • E-Rows：估计返回的行数
  • A-Rows：实际返回的行数
  • A-Time：实际执行时间
  • Buffer Gets：实际使用的缓冲区数量

执行计划优化技巧 ​

索引使用优化 ​

  • 确保SQL语句使用了适当的索引
  • 避免索引失效的情况（如使用函数、类型不匹配等）
  • 考虑创建复合索引优化多列查询
  • 定期重建和收集索引统计信息

连接方法选择 ​

  • NESTED LOOPS：适用于小结果集连接
  • HASH JOIN：适用于大结果集连接
  • MERGE JOIN：适用于已排序数据连接
  • 根据实际情况选择合适的连接方法

连接顺序优化 ​

  • 优化器会自动选择连接顺序，但有时需要手动调整
  • 小表应该作为驱动表
  • 使用Hint强制连接顺序

排序操作优化 ​

  • 避免不必要的排序操作
  • 利用索引避免排序
  • 考虑使用并行排序
  • 调整PGA_AGGREGATE_TARGET参数

子查询优化 ​

  • 避免多层嵌套子查询
  • 考虑使用WITH子句（CTE）
  • 对于 EXISTS 子查询，考虑使用 IN 替代
  • 对于大结果集，考虑使用连接替代子查询

常见执行计划问题 ​

全表扫描问题 ​

  • 原因：没有合适的索引、索引失效、数据量过小
  • 解决方案：创建适当的索引、修改SQL语句避免索引失效、考虑分区表

索引范围扫描效率低 ​

  • 原因：索引选择性差、返回结果集过大
  • 解决方案：创建更具选择性的索引、添加额外的过滤条件、考虑分区索引

哈希连接成本高 ​

  • 原因：结果集过大、PGA内存不足
  • 解决方案：增加PGA内存、添加过滤条件减少结果集、考虑使用嵌套循环连接

排序操作过多 ​

  • 原因：ORDER BY、GROUP BY、DISTINCT等操作
  • 解决方案：利用索引避免排序、调整排序参数、考虑使用并行排序

嵌套循环连接效率低 ​

  • 原因：驱动表过大、连接条件不合适
  • 解决方案：选择更小的驱动表、创建适当的索引、考虑使用哈希连接

执行计划Hint使用 ​

索引Hint ​

sql
-- 强制使用指定索引
SELECT /*+ INDEX(emp emp_deptno_idx) */ * FROM emp WHERE deptno = 10;

-- 强制不使用索引
SELECT /*+ NO_INDEX(emp) */ * FROM emp WHERE deptno = 10;

连接方法Hint ​

sql
-- 强制使用嵌套循环连接
SELECT /*+ USE_NL(emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

-- 强制使用哈希连接
SELECT /*+ USE_HASH(emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

-- 强制使用合并连接
SELECT /*+ USE_MERGE(emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

连接顺序Hint ​

sql
-- 强制连接顺序
SELECT /*+ ORDERED */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

并行执行Hint ​

sql
-- 强制并行执行
SELECT /*+ PARALLEL(emp 4) */ * FROM emp;

其他常用Hint ​

sql
-- 强制全表扫描
SELECT /*+ FULL(emp) */ * FROM emp;

-- 强制索引快速全扫描
SELECT /*+ INDEX_FFS(emp emp_deptno_idx) */ deptno, count(*) FROM emp GROUP BY deptno;

-- 强制不排序
SELECT /*+ NO_SORT */ * FROM emp WHERE deptno = 10;

执行计划监控 ​

实时监控执行计划 ​

sql
-- 启用SQL监控
ALTER SESSION SET statistics_level = all;

-- 执行SQL
SELECT * FROM emp WHERE deptno = 10;

-- 查看实时执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

监控长时间运行的SQL ​

sql
-- 查找长时间运行的SQL
SELECT sid, serial#, sql_id, elapsed_time, status FROM v$session_longops WHERE time_remaining > 0;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'));

AWR报告分析 ​

  • 生成AWR报告：@$ORACLE_HOME/rdbms/admin/awrrpt.sql
  • 查看TOP SQL部分的执行计划
  • 分析SQL执行统计信息
  • 识别性能趋势和问题

性能优化最佳实践 ​

SQL语句优化 ​

  • 使用绑定变量避免硬解析
  • 编写简洁高效的SQL语句
  • 避免使用SELECT *
  • 合理使用WHERE条件过滤数据
  • 避免在WHERE子句中使用函数

索引优化 ​

  • 为经常查询的列创建索引
  • 为经常用于连接的列创建索引
  • 为经常用于排序和分组的列创建索引
  • 定期重建和收集索引统计信息
  • 避免创建过多索引

统计信息管理 ​

  • 定期收集表和索引的统计信息
  • 使用DBMS_STATS包管理统计信息
  • 考虑使用动态采样
  • 确保统计信息的准确性

内存配置优化 ​

  • 调整PGA_AGGREGATE_TARGET参数
  • 调整SGA相关参数
  • 合理设置排序区大小
  • 监控内存使用情况

分区表和索引 ​

  • 对于大表考虑使用分区
  • 选择合适的分区策略
  • 利用分区修剪提高查询性能
  • 考虑分区索引的使用

版本差异 ​

Oracle 10g ​

  • 引入了自动工作负载仓库(AWR)
  • 提供了DBMS_XPLAN.DISPLAY_CURSOR函数
  • 支持绑定变量窥视

Oracle 11g ​

  • 引入了SQL Plan Management
  • 提供了自适应游标共享
  • 增强了执行计划稳定性
  • 引入了实时SQL监控

Oracle 12c及以上 ​

  • 引入了多租户架构的执行计划管理
  • 提供了自动SQL优化建议
  • 增强了并行执行功能
  • 引入了SQL Performance Analyzer

常见问题（FAQ） ​

Q1: 为什么执行计划显示全表扫描而不是索引扫描？ ​

A1: 可能的原因包括：

  • 没有为查询列创建索引
  • 索引存在但统计信息过时
  • 查询返回的数据量过大，全表扫描成本更低
  • WHERE子句中使用了函数导致索引失效
  • 索引选择性差，优化器认为全表扫描更高效

Q2: 如何强制Oracle使用特定的索引？ ​

A2: 可以使用索引Hint强制使用特定索引：

sql
SELECT /*+ INDEX(table_name index_name) */ * FROM table_name WHERE condition;

但请注意，只有在你确定使用该索引确实更高效时才应该使用Hint，否则可能会导致性能下降。

Q3: 执行计划中的成本值是什么意思？ ​

A3: 执行计划中的成本是优化器估计的执行成本，是一个相对值，不是实际的时间或资源消耗。成本值越低，表示优化器认为该执行计划越高效。成本计算基于统计信息，包括CPU成本和I/O成本。

Q4: 如何识别执行计划中的性能瓶颈？ ​

A4: 识别性能瓶颈的方法：

  • 查看执行计划中成本最高的操作
  • 检查是否有全表扫描、排序操作等耗时操作
  • 比较估计行数(E-Rows)和实际行数(A-Rows)，差异过大可能表示统计信息不准确
  • 查看实际执行时间(A-Time)较长的操作
  • 检查是否有过多的嵌套循环连接或哈希连接

Q5: 为什么相同的SQL语句会有不同的执行计划？ ​

A5: 可能的原因包括：

  • 绑定变量窥视导致的执行计划变化
  • 统计信息更新
  • 数据量变化
  • 索引状态变化
  • 数据库参数变更
  • SQL语句文本微小差异导致的硬解析

Q6: 如何稳定执行计划？ ​

A6: 稳定执行计划的方法：

  • 使用SQL Plan Baselines
  • 使用Stored Outlines
  • 使用绑定变量
  • 定期收集统计信息
  • 避免在WHERE子句中使用函数
  • 考虑使用Hint固定执行计划

Q7: 执行计划中的连接方法有什么区别？ ​

A7: 三种主要连接方法的区别：

  • 嵌套循环连接：适用于小结果集，通过循环驱动表的每一行，然后在被驱动表中查找匹配行，类似于双层循环
  • 哈希连接：适用于大结果集，将较小的表构建哈希表，然后扫描较大的表进行匹配
  • 合并连接：适用于已排序的数据，将两个已排序的结果集进行合并操作

Q8: 如何使用执行计划优化复杂SQL语句？ ​

A8: 优化复杂SQL语句的步骤：

  • 分解复杂SQL为多个简单SQL
  • 分析每个子查询的执行计划
  • 为关键表创建适当的索引
  • 调整连接顺序和连接方法
  • 避免使用复杂的子查询和视图
  • 考虑使用WITH子句（CTE）提高可读性和性能
  • 利用并行执行提高复杂查询的性能
  • 定期收集统计信息确保执行计划的准确性