合 Oracle执行计划介绍
👉 本文共约5227个字，系统预计阅读时间或需20分钟。
目录
简介
执行计划指示Oracle如何获取和过滤数据、产生最终结果集，这是影响SQL语句执行性能的关键因素。在深入了解执行计划之前，首先需要知道执行计划是在什么时候产生的，以及如何让SQL引擎为语句生成执行计划。
在Oracle中，任何一条语句在解析过程中都会生成一个唯一的数值标识，即SQL_ID。而同一条语句，在解析过程中，可能会因为执行环境的改变（例如某些优化参数被改变）而生成多个版本的游标，不同的游标会有不同的执行计划。每个游标都会按顺序赋予一个序列号，即CHILD_NUMBER，一条语句生成的第一个游标的CHILD_NUMBER为0；相应的，Oracle会为每个执行计划生成一个HASH值以作区分。而多个不同版本的游标，其执行计划可能会相同，也可能不同。
获取执行计划有哪几种方法？
一般来说，有如下几种获取执行计划的方式：
1、AUTOTRACE方式
AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*Plus会自动收集执行过的SQL语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。
DBA用户可以直接使用AUTOTRACE功能，但是如果用户没有DBA权限，那么需要在SYS用户下执行plustrce.sql脚本，自动创建PLUSTRACE角色，再把PLUSTRACE权限赋给普通用户即可。
1 2 | $ORACLE_HOME/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO USER_LHR; |
另外，若启用AUTOTRACE报“SP2-0611”的错误，则可以执行utlxplan.sql脚本来创建表PLAN_TABLE，如下所示：
1 2 3 4 | SQL> set autot on SP2-0613: 无法验证 PLAN_TABLE 格式或实体 SP2-0611: 启用EXPLAIN报告时出错 SQL> @?/rdbms/admin/utlxplan.sql |
在执行如下脚本后，每个用户（包括以后新建的用户）都可以使用AUTOTRACE命令：
1 2 3 4 5 | @?/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; GRANT ALL ON PLAN_TABLE TO PUBLIC; @?/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO PUBLIC; |
AUTOTRACE的语法如下所示：
1 | SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS] |
其中，AUTOTRACE可简写为AUTOT，TRACEONLY可简写为TRACE，EXPLAIN可简写为EXP，STATISTICS可简写为STAT。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL> SET AUTOT ON SQL> SELECT COUNT(*) FROM PLAN_TABLE; COUNT(*) ---------- 68 Execution Plan ---------------------------------------------------------- Plan hash value: 1751138260 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| PLAN_TABLE$ | 68 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 487 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
AUTOTRACE STATISTICS含义见下表：
| 序号 | 列名 | 解释 |
|---|---|---|
| 1 | recursive calls | 递归调用，表示执行SQL的时候的产生的递归调用的次数。Oracle在执行SQL的时候，有时候会生成很多额外的SQL语句，这个就称为递归调用。这个参数和访问数据字典的次数有很大的关系，一般来说，这个参数值不会很大。 |
| 2 | db block gets | DB块取，表示当前读。在发生INSERT、DELETE、UPDATE和SELECT FOR UPDATE的时候，数据库缓冲区中的数据库块的个数。在SELECT语句中一般为0。 |
| 3 | consistent gets | 一致性读，表示除了SELECT FOR UPDATE的时候，从数据库缓冲区中读取的数据块的个数（注意，实际上并不是块的个数），可能会读取回滚段的信息，一般来说，逻辑读（Logical Reads） = 当前读（db block gets） + 一致性读（consistent gets）。 |
| 4 | physical reads | 物理读，在执行SQL的过程中，从硬盘上读取的数据块个数。 |
| 5 | redo size | SQL语句在执行过程中产生的Redo的字节数。 |
| 6 | bytes sent via SQL*Net to client | 服务器利用SQL*Net发送到客户端的字节数。 |
| 7 | bytes received via SQL*Net from client | 服务器利用SQL*Net从客户端接收的字节数。 |
| 8 | SQL*Net roundtrips to/from client | 从客户端发送和接收的SQL*Net消息的总数，包括从多行的结果集中提取的往返消息。 |
| 9 | sorts (memory) | 在内存执行的排序次数。 |
| 10 | sorts (disk) | 在磁盘上执行的排序次数，如果内存空间不足，那么会使用磁盘空间。 |
| 11 | rows processed | 更改或选择返回的行数。 |
2、EXPLAIN PLAN FOR方式
1 2 3 4 5 6 7 8 9 10 11 | SQL> EXPLAIN PLAN FOR SELECT * FROM T017_LHRO; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3200443156 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1363 | 177K| 9 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T017_LHRO | 1363 | 177K| 9 (0)| 00:00:01 | ------------------------------------------------------------------------------- |
3、DBMS_XPLAN.DISPLAY_CURSOR方式
1 2 3 4 5 6 7 8 9 10 11 | SYS@RAC2LHR1> SELECT * FROM V$VERSION WHERE ROWNUM<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.EMPNO = 7788; ENAME DNAME LOC ---------- -------------- ------------- SCOTT RESEARCH DALLAS |
如果不传递任何参数给DISPLAY_CURSOR函数，那么默认显示当前会话最后一条SQL语句的执行计划，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID 315xan8zgvtbm, child number 0 ------------------------------------- SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7788 Plan hash value: 1674520956 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("E"."EMPNO"=7788) 5 - access("E"."DEPTNO"="D"."DEPTNO") 24 rows selected. |
传递SQL_ID以及FORMAT参数给DISPLAY_CURSOR函数，并配合修饰符控制执行计划的输出，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('315XAN8ZGVTBM',NULL,'ALL')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- SQL_ID 315xan8zgvtbm, child number 0 ------------------------------------- SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7788 Plan hash value: 1674520956 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / E@SEL$1 3 - SEL$1 / E@SEL$1 4 - SEL$1 / D@SEL$1 5 - SEL$1 / D@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("E"."EMPNO"=7788) 5 - access("E"."DEPTNO"="D"."DEPTNO") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "ENAME"[VARCHAR2,10], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13] 2 - "ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22] 3 - "E".ROWID[ROWID,10] 4 - "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13] 5 - "D".ROWID[ROWID,10] 42 rows selected. |
利用STATISTICS_LEVEL或/*+ GATHER_PLAN_STATISTICS*/可以知道表访问的次数，也可以查看真实执行计划并获得统计信息。如下所示：
1 2 3 4 5 | SET SERVEROUTPUT OFF ALTER SESSION SET STATISTICS_LEVEL=ALL; 执行SQL语句 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>'',CURSOR_CHILD_NO =>1,FORMAT => 'ADVANCED ALLSTATS')); |
其中参数SQL_ID为父游标，如果为NULL，那么表示显示该会话之前的SQL执行计划。CURSOR_CHILD_NO为子游标的序号，默认为0，如果设定为NULL，那么所有该父游标下所有的子游标的执行计划都将返回。参数FORMAT指定要显示哪些信息，常用的有：IOSTATS（I/O信息显示）、ALLSTATS（I/O信息显示+PGA信息）、ADVANCED（显示所有统计信息）、IOSTATS LAST或ALLSTATS LAST（只显示最后一次执行的统计信息）。默认值TYPICAL只能显示一个普通的执行计划，不能显示出实际返回的行。
☞ 这种方式也是SQL调优中常用的方法，但使用该方法的前提是如下两个条件必须同时满足：
① 一般在会话级别设置参数STATISTICS_LEVEL为ALL，也可以使用/*+ GATHER_PLAN_STATISTICS*/提示。
② 若DBMS_XPLAN.DISPLAY_CURSOR中的入参SQL_ID输入值为NULL的话，则SERVEROUTPUT必须设置为OFF（SET SERVEROUTPUT OFF），否则会报类似如下的错误：
1 2 3 4 5 6 7 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9m7787camwh4m, child number 0 begin :id := sys.dbms_transaction.local_transaction_id; end; NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) |
若为具体SQL_ID的值的话，则无论SERVEROUTPUT的值如何都可以正常执行。
示例如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | SYS@RAC2LHR1> SHOW PARAMETER STATISTICS_LEVEL NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL SYS@RAC2LHR1> ALTER SESSION SET STATISTICS_LEVEL=ALL; Session altered. SYS@RAC2LHR1> SHOW SERVEROUTPUT serveroutput OFF SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.EMPNO = 7369; ENAME DNAME LOC ---------- -------------- ------------- SMITH RESEARCH DALLAS SYS@RAC2LHR1> SET PAGESIZE 0 SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS LAST -PREDICATE -NOTE')); SQL_ID g3mx9hdyrhus7, child number 0 ------------------------------------- SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7369 Plan hash value: 1674520956 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 | | 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | | 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | -------------------------------------------------------------------------------------------------- SYS@RAC2LHR1> SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO = D.DEPTNO 4 AND E.EMPNO = 7369; SMITH RESEARCH DALLAS SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC LAST ALLSTATS')); EXPLAINED SQL STATEMENT: ------------------------ SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7369 Plan hash value: 1674520956 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("E"."EMPNO"=7369) 5 - access("E"."DEPTNO"="D"."DEPTNO") |
4、其它跟踪方法
除了上述方法外，还可以通过其它一些途径获取到语句的执行计划，例如10046，10053事件等，但在这些方法所产生的数据里，执行计划通常仅是辅助解决问题的一个部分，而非重点。
5、第三方工具
利用第三方工具，如PL/SQL DEV、TODO等开发工具，在PL/SQL DEV中选定SQL后，按F5即可查看执行计划：
此外，还可以通过写脚本从V$SQL_PLAN、DBA_HIST_SQL_PLAN、V$SQL_PLAN_MONITOR等视图中来获取执行计划。
总结
对于这几种获取执行计划的方法有如下结论：
① 若目标SQL需要执行很长时间才能返回结果，则推荐使用EXPLAIN PLAN FOR来获取执行计划。
② 若要查询目标SQL的所有子游标的执行计划，则推荐使用DBMS_XPLAN.DISPLAY_CURSOR('&SQLID', NULL,'ADVANCED ALLSTATS')或awrsqrpt.sql来获取执行计划。
③ 若要分析SQL语句的内部调用详情，则推荐使用10046事件。
④ 若想确保看到真实的执行计划，则不能使用EXPLAIN PLAN FOR和SET AUTOTRACE TRACEONLY EXPLAIN。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
