当前位置：首页 > news >正文

优化02-执行计划

news 来源：原创 2025/8/23 14:00:17

Oracle 的执行计划（Execution Plan）是数据库优化器（Optimizer）为执行 SQL 语句而选择的操作路径和资源分配方案的详细描述。它记录了数据库如何访问表、索引、连接数据以及执行排序、过滤等操作的步骤。理解执行计划是性能调优的核心技能之一。

执行计划的作用

指导 SQL 执行：明确数据库如何读取数据（全表扫描、索引扫描）、如何连接表（嵌套循环、哈希连接）、如何排序（SORT）等。
评估性能：通过成本（Cost）和行数（Rows）估算，判断执行路径的效率。
诊断性能问题：发现全表扫描、低效连接方法、缺失索引等问题。

获取执行计划的方法

explain plan for 方式

SQL> set linesize 200
SQL> set pagesize 999
SQL> explain plan for  select * from emp where empno=7788;Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("EMPNO"=7788)14 rows selected.

优点：无需真正执行，效率高

缺点：

没有运行时的一些统计信息，如逻辑读、物理的等。
无法判断表的访问次数、每一步的真正处理行数

set autorace 方式

set autotrace on :会输出执行结果和执行计划
set autotrace traceonly: 不输出执行结果，输出执行计划
set autotrace traceonly explain: 不输出执行结果，只输出执行计划部分
set autotrace traceonly statistic:不输出执行结果，只输出统计信息部分

SQL>  set autotrace traceonly;
SQL> select * from emp where empno=7788;Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("EMPNO"=7788)Statistics
----------------------------------------------------------1  recursive calls0  db block gets2  consistent gets0  physical reads0  redo size899  bytes sent via SQL*Net to client596  bytes received via SQL*Net from client1  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

优点：可以输出运行时的统计信息，而且有开关参数控制显示想要的部分

缺点：语句真正执行，如果语句执行时间较长会浪费时间，无法看到表被访问的次数。

statistic_level=all方式

 alter session set statistics_level=all;
SQL>  select * from scott.emp where empno=7788;EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20SQL> select * from table (dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  bkacg30nr9ada, child number 0
-------------------------------------select * from scott.emp where empno=7788Plan hash value: 2949544139------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("EMPNO"=7788)
--关键指标解释：
--Starts:该sql执行的次数。
--E-Rows:执行计划预计的行数。
--A-Rows:实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
--A-Time:每一步实际执行的时间(HH:MM:SS.FF)，根据这一行可以知道该sq!耗时在了哪个地方。
--Buffers:每一步实际执行的逻辑读或一致性读。
--Reads:物理读。
--OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
--1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作就称一次通过，One-Pass;否则为多次通过，Multi Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
--User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi Pass,如果没有使用磁盘，则显示OPTIMAL)
--OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存

优点:

可以清晰的从starts得出表被访问多少
可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确
虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少

缺陷:

必须要等到语句真正执行完毕后，才可以出结果。
无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出
看不出递归调用的次数，看不出物理读的多少

根据SQL_ID获取

使用dbms_xplan.display_cursor从共享池获取

SQL> select * from table(dbms_xplan.display_cursor('azysqasqbkkva'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  azysqasqbkkva, child number 0
-------------------------------------
select * from scott.emp where empno=7788Plan hash value: 2949544139--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("EMPNO"=7788)

使用bms_xplan.display_awr从awr视图中获取

SQL> select * from table(dbms_xplan.display_awr('azysqasqbkkva'));no rows selected--这里没有显示是因为执行的SQL还未生成快照，比如没30分钟生成一次快照，9点09执行SQL，想要通过awr获取执行计划，需要等到9点30之后才会查到。

优点：

知道sql id立即可得到执行计划，和explain plan for 一样无需执行;
可以得到真实的执行计划。

缺陷：

没有输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况);
无法判断是处理了多少行;
无法判断表被访问了多少次。

通过trace获取

详解10046和10053

awrsqrpt.sql获取

 @?/rdbms/admin/awrsqrpt.sql --按提示输入SQL_ID 最后生成HTML格式的SQL报告，里面包含执行计划

PLSQL/deveploer查看

在SQL界面按F5获取

使用场景

如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1，如果之前执行完成过，并且知道sqlid,可以考虑使用方式4和6;
跟踪某条SQL最简单的方法是方法1，其次就是方法2;
如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6;
如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5;
要想确保看到真实的执行计划，不能用方法1
要想获取表被访问的次数，只能使用方法3;

执行计划的关键组成部分

操作（Operation）

描述数据访问方式，例如：
- TABLE ACCESS：表访问（全表扫描 FULL 或索引扫描 INDEX）。
- INDEX：索引操作（范围扫描 RANGE SCAN、唯一扫描 UNIQUE SCAN）。
- HASH JOIN：哈希连接。
- NESTED LOOPS：嵌套循环连接。

选项（Options）

补充操作细节，例如：
- FULL：全表扫描。
- RANGE SCAN：索引范围扫描。
- USE_NL：使用嵌套循环连接。

对象（Object Name）

涉及的表、索引或中间结果集名称。

成本（Cost）

优化器估算的总成本（越低越好）。
包含 CPU 和 I/O 成本。

行数（Rows）

优化器估算的每一步返回的行数（可能不准确，需结合实际数据分布）。

谓词（Predicate）

过滤条件（PREDICATE INFORMATION部分）：
ACCESS`：用于定位数据的条件（如索引列的值）。
FILTER：用于过滤数据的条件（如 WHERE department_id = 10）。

总结

执行计划是 SQL 性能调优的核心工具。通过分析操作类型、成本、行数和谓词，可以定位全表扫描、低效连接、缺失索引等问题。结合统计信息维护、索引优化和 SQL 重写，能够显著提升数据库性能。对于复杂场景，需借助 SQL Trace、AWR 报告和自适应优化工具进行深度诊断。