Oracle执行计划介绍
👉 本文共约5213个字，系统预计阅读时间或需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) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
若为具体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。
⑤ 若想获取到表的访问次数，则推荐/*+ GATHER_PLAN_STATISTICS*/。
⑥ 若数据库版本大于10g，则对执行时间较长的SQL语句推荐使用SQL实时监控特性查看html报告。
下表对这几种获取执行计划的方法给予总结：
有关Oracle查看执行计划的几种方法的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2136865/
如何得到真实的执行计划？
在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行过，真正执行过的SQL所对应的执行计划就是准确的，反之则有可能不准，因此，通过10046事件及如下的几种方式得到的执行计划是最准确的，而从其它方式获取到的执行计划都有可能不准确。
1 2 3 | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id')); |
这里需要注意的是，虽然SQL*Plus的AUTOTRACE功能有部分是真实执行了SQL语句的（例如所有DML语句），但是，由于该命令所显示的执行计划来源于调用EXPLAIN PLAN命令，所以，其得到的执行计划依然可能不准确（特别是在使用了绑定变量的情况下）。那么，为什么EXPLAIN PLAN命令里显示的预估执行计划与该SQL真实的执行计划不一样呢？原因有多个方面，常见的情况包括以下几个方面：
① 绑定变量窥视（Bind Peeking）：EXPLAIN PLAN里不会进行绑定变量窥视，但是Runtime Plan里会进行绑定变量窥视，所以，如果发生这种情况，那么会使这两个执行计划产生差异。
② 隐式转换：Explain Plan里不会考虑绑定变量的类型，但是Runtime Plan里会考虑类型，从而有可能会根据绑定变量的类型出现隐式转换，所以谓词（Predicate）会发生变化，使得执行计划也会产生差异。
③ 优化器参数：执行Explain Plan的Session与Runtime Plan的Session不是同一个。如果各个Session之间存在优化器参数差异，那么执行计划也会产生差异。
④ 统计信息收集参数：Explain Plan始终是用最新的统计信息产生执行计划，但是，Runtime Plan不一定会用最新的统计信息。因此也会产生执行计划差异。在收集统计信息时，一个与缓存的游标是否失效的很重要的参数为NO_INVALIDATE。在重新收集统计信息时，可以指定NO_INVALIDATE选项。该选项有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE这3个值。如果取值为TRUE，那么表示收集统计信息后不进行游标失效动作，原有的Shared Cursor保持原有状态。如果取值为FALSE，那么表示将统计信息对象相关的所有Cursor全部失效，目标SQL语句在下次执行时就会使用硬解析。如果设置为AUTO_INVALIDATE，那么Oracle自己决定Shared Cursor失效动作，当SQL再次执行时间距离上次收集统计信息的时间超过5小时（隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定）则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时，没有使用NO_INVALIDATE=>FALSE选项，所以，即使收集了统计信息，执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效，设置方法为：
1 2 | -- 在收集SH.SALES表上的统计信息时，让所有依赖于该表的游标不失效 EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE'); |
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE TABLE TEST_EXPLAIN_LHR AS SELECT * FROM DBA_OBJECTS; INSERT INTO TEST_EXPLAIN_LHR SELECT * FROM TEST_EXPLAIN_LHR; COMMIT; SELECT COUNT(*) FROM TEST_EXPLAIN_LHR; CREATE INDEX IDX_OBJ_LHR ON TEST_EXPLAIN_LHR(OBJECT_ID); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'test_explain_lhr',ESTIMATE_PERCENT => 100,CASCADE => TRUE); VAR X NUMBER; VAR Y NUMBER; EXEC :X := 0; EXEC :Y := 100000; EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); SET AUTOT ON SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SET AUTOT OFF SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); |
下面实验验证了使用EXPLAIN PLAN FOR和SET AUTOT ON方式获取到的执行计划都是不准确的：
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 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | SYS@PROD1> clear scr SYS@PROD1> CREATE TABLE test_explain_lhr AS SELECT * FROM Dba_Objects; Table created. SYS@PROD1> INSERT INTO test_explain_lhr SELECT * FROM test_explain_lhr; 72503 rows created. SYS@PROD1> COMMIT; Commit complete. SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr; COUNT(*) ---------- 145006 SYS@PROD1> CREATE INDEX idx_obj_lhr ON test_explain_lhr(object_id); Index created. SYS@PROD1> EXEC dbms_stats.gather_table_stats(USER,'test_explain_lhr',estimate_percent => 100,cascade => TRUE); PL/SQL procedure successfully completed. SYS@PROD1> VAR x NUMBER; SYS@PROD1> VAR y NUMBER; SYS@PROD1> EXEC :x := 0; PL/SQL procedure successfully completed. SYS@PROD1> EXEC :y := 100000; PL/SQL procedure successfully completed. SYS@PROD1> EXPLAIN PLAN FOR SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; Explained. SYS@PROD1> set line 9999 SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------- Plan hash value: 3299589416 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 363 | 1815 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND "T"."OBJECT_ID"<=TO_NUMBER(:Y)) 17 rows selected. SYS@PROD1> set autot on SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; COUNT(*) ---------- 145006 Execution Plan ---------------------------------------------------------- Plan hash value: 3299589416 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| IDX_OBJ_LHR | 363 | 1815 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND "T"."OBJECT_ID"<=TO_NUMBER(:Y)) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 329 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SYS@PROD1> SET AUTOT OFF SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; COUNT(*) ---------- 145006 SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID 1r87sg98rdkuf, child number 0 ------------------------------------- SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y Plan hash value: 2428225634 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 90 (100)| | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX FAST FULL SCAN| IDX_OBJ_LHR | 145K| 708K| 90 (2)| 00:00:02 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("TEST_EXPLAIN_LHR"."OBJECT_ID")) END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :X (NUMBER): 0 2 - :Y (NUMBER): 100000 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:X<=:Y) 3 - filter(("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 53 rows selected. |
有关真实的执行计划的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152884/
如何在不执行SQL的情况下获取执行计划
1、“EXPLAIN PLAN FOR SQL”不实际执行SQL语句，生成的计划未必是真实执行的计划。但是，必须要有PLAN_TABLE表，可以执行脚本“@?/rdbms/admin/utlxplan.sql”来创建。
2、SQL*Plus的AUTOTRACE功能，命令：SET AUTOTRACE TRACEONLY EXPLAIN。除SET AUTOTRACE TRACEONLY EXPLAIN外其它的AUTOTRACE方式均实际执行SQL。但是，如果该命令后执行的是DML语句，那么该DML语句是确实被Oracle实际执行过的。
如何获取SQL历史执行计划？
历史执行计划只能从AWR中获取，如果AWR没有记录的话，那么就无法获取历史执行计划了，获取历史执行计划的命令如下所示：
1 | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid')); |
对于历史计划，可以生成SQL报告，命令如下所示：
1 | SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(L_DBID => , L_INST_NUM => , L_BID => , L_EID => , L_SQLID => )) ; |
其中，L_DBID代表数据库的DBID，L_INST_NUM代表数据库的实例号，单机环境为1，RAC环境填写具体的实例号，L_BID为开始的快照号，L_EID为结束的快照号，L_SQLID为要查看SQL的SQL_ID。
下面的例子可以直接从AWR中获取SQL_ID为“bsa0wjtftg3uw”的执行计划，可以看到历史有2种执行计划，一个是全表扫描，一个是索引范围扫描：
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 | SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID => 'bsa0wjtftg3uw' )) ; SQL_ID bsa0wjtftg3uw -------------------- SELECT file# FROM file$ WHERE ts#=:1 Plan hash value: 690176192 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| FILE$ | | 2 | INDEX RANGE SCAN | I_FILE2 | ----------------------------------------------- Note ----- - rule based optimizer used (consider using cbo) SQL_ID bsa0wjtftg3uw -------------------- SELECT file# FROM file$ WHERE ts#=:1 Plan hash value: 3494626068 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS FULL| FILE$ | 1 | 6 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- 31 rows selected. |
给出一个执行计划的执行顺序
阅读如下的执行计划，给出SQL的执行顺序。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | ----------------------------------------- | Id | Operation ----------------------------------------- | 0 | SELECT STATEMENT | 1 | SORT AGGREGATE | 2 | VIEW | 3 | UNION-ALL |* 4 | FILTER |* 5 | HASH JOIN | 6 | TABLE ACCESS FULL |* 7 | TABLE ACCESS FULL |* 8 | TABLE ACCESS BY INDEX ROWID |* 9 | INDEX UNIQUE SCAN | 10 | NESTED LOOPS | 11 | INDEX FULL SCAN | 12 | TABLE ACCESS CLUSTER |* 13 | INDEX UNIQUE SCAN ------------------------------------------- |
分析：采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID，那么就最先执行，首先，6、7、9、13最右，所以，6，7最先执行做HASH JOIN，为6，7，5。
第二，8有子节点，接下来是9，8。
第三，HASH的结果和8的结果做FILTER过滤。
第四，10这个节点根据原则是11，13，12，10。
第五，剩下依次是3，2，1，0。
所以，该图的执行顺序是6，7，5，9，8，4，11，13，12，10，3，2，1，0。
如何从执行计划初步判断SQL的性能问题？
可以从下表所示的几个方面去考虑：
此外，还有一些其它需要注意的地方，例如COST花费特别大的步骤、全表扫描的步骤、FILTER的操作等等，都是需要特别关注的地方，这里就不详细列举了，总之，看执行计划和看AWR报告一样，需要具有一双敏锐的鹰眼，最主要的是找出SQL的性能瓶颈。
