在 Oracle 数据库中，SQL 性能分析是一项非常重要的任务。通过性能分析，我们可以了解 SQL 语句的执行情况，从而优化其性能。常用的方法包括使用 EXPLAIN PLAN、自动工作负载库 (AWR)、SQL Trace 等工具。EXPLAIN PLAN 是最常用的工具之一，它生成 SQL 语句的执行计划并提供重要的执行指标。
1. EXPLAIN PLAN 简介
EXPLAIN PLAN 命令用于显示 SQL 语句的执行计划，即 Oracle 如何执行该查询。通过分析执行计划中的步骤，可以了解 Oracle 执行查询时使用的索引、表扫描方式、连接顺序等，从而找到优化 SQL 的潜在方法。
基本语法
EXPLAIN PLAN FOR SQL语句;
执行后，可以通过查询 PLAN_TABLE 来查看执行计划：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
使用实例
查询语句：
SELECT emp_name, job_title FROM employees WHERE dept_id = 10;
可以用 EXPLAIN PLAN 来查看该语句的执行计划：
EXPLAIN PLAN FOR
SELECT emp_name, job_title FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
2. EXPLAIN PLAN 输出解释
EXPLAIN PLAN 的输出通常包含以下字段：
- Operation：表示 Oracle 执行查询时使用的操作类型（如表扫描、索引扫描、连接操作）。
- Options：显示特定操作的选项（如表扫描是全表扫描还是索引扫描）。
- Object Name：表示查询操作所涉及的对象（如表或索引）。
- Cost：表示 Oracle 估算的该操作的相对成本，值越大表示该操作的成本越高。
- Cardinality：估算的行数，表示该操作处理的行数。
- Bytes：估算的字节数，表示该操作处理的字节数。
- Time：Oracle 估计的完成该操作所需的时间。
- Predicate Information：显示查询条件和过滤谓词，帮助理解优化器如何应用 WHERE 子句。
输出
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL | EMPLOYEES | 1 | 15 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
3. 关键性能指标
1. Cost（成本）
Cost是 Oracle 优化器估算的执行计划相对成本，是一个权重值，不直接表示实际执行时间，但可以作为不同执行计划之间的相对比较指标。Cost值越低，表示 Oracle 认为这个计划更有效率。- 影响成本的因素包括 CPU 使用、磁盘 I/O 以及内存使用情况。
2. Cardinality（基数）
Cardinality表示某个操作返回的估计行数。Oracle 通过统计数据和谓词条件来估算基数。- 如果基数估算不准确，可能会导致不合理的执行计划。例如，Oracle 可能会选择全表扫描而不是索引扫描。
3. Rows（返回行数）
Rows列表示 Oracle 预估某个操作会返回的行数。它与Cardinality类似，用于估算操作的行数。- 该估算值直接影响到 Oracle 选择的执行路径。通常需要尽量使返回的行数尽可能接近实际值。
4. Time（执行时间）
Time列表示 Oracle 估计的某个操作所需的时间，通常以HH:MM:SS的格式显示。- 它是基于系统统计信息计算的，主要考虑 CPU 和 I/O 的开销。
5. Operation（操作类型）
Operation列描述了查询中的操作步骤，常见操作类型包括：TABLE ACCESS FULL：全表扫描，通常在表上没有合适索引时执行。INDEX RANGE SCAN：索引范围扫描，针对范围查询或部分匹配的索引使用。NESTED LOOPS：嵌套循环连接，通常用于小表和大表的连接操作。HASH JOIN：哈希连接，适用于大数据量的表连接操作。
6. Predicate Information（谓词信息）
- 谓词信息展示了 Oracle 优化器在执行过程中使用的过滤条件。
- 了解谓词信息可以帮助理解哪些条件得到了应用，以及这些条件是如何影响执行计划的。
4. 如何使用执行计划优化 SQL 性能
1. 关注高成本操作
- 对于高成本操作（如
TABLE ACCESS FULL或SORT操作），需要考虑是否可以通过添加索引、优化 SQL 语句来降低成本。
2. 检查索引使用
- 如果查询包含过滤条件，但执行计划中没有显示索引使用（如
INDEX RANGE SCAN），则需要考虑创建合适的索引以提高查询性能。
3. 避免全表扫描
- 如果执行计划显示全表扫描（
TABLE ACCESS FULL），而且查询的数据量较大，可以考虑通过创建索引或优化 WHERE 子句来避免全表扫描。
4. 检查连接策略
- 对于复杂的多表连接，Oracle 可能会使用
NESTED LOOPS或HASH JOIN。如果连接的行数较多且性能较差，可以尝试使用提示（Hint）强制 Oracle 使用不同的连接方法。
5. 常见的 SQL 优化建议
1. 使用合适的索引
- 在 WHERE 子句中频繁使用的列上创建索引。
- 确保索引使用了与查询条件匹配的数据类型。
2. 避免不必要的排序
- 避免
ORDER BY和GROUP BY操作，如果没有必要，不要在查询中使用它们。
3. 简化查询条件
- 合理使用子查询、连接和谓词，尽量减少复杂度，避免冗余的计算。
4. 使用批量操作
- 对于需要大量插入、更新或删除的操作，尽量使用批量操作，而不是逐行处理。
总结
通过 EXPLAIN PLAN，可以清晰地看到 Oracle 执行 SQL 语句的每一步细节。关键性能指标如 Cost、Cardinality、Rows 等为优化 SQL 提供了重要参考。优化 SQL 语句时，应该关注索引的使用、全表扫描的避免以及合适的连接方式，从而提高查询效率。