Oracle执行计划及性能调优详解使用

在 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：哈希连接，适用于大数据量的表连接操作。