SQL执行计划详解-优快云博客

本文示例SQL为：

--set lines 1000 pages 1000
select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

1.explain plan for SQL;

通过explain plan for命令查看SQL的执行计划，这种方法SQL并不真实执行，一般适用于上线前的SQL预审，尤其对DML语句，由于SQL不执行，不用担心对生产数据造成影响。这种方法查看的执行计划有Predicate Information，无Statistics，查看到的执行计划不一定真实，第三方工具PL/SQL Developer中F5查看执行计划也是调用的这个方法。
使用explain plan for命令查看SQL的执行计划：

explain plan for 
select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; select * from table(dbms_xplan.display);

示例如下：

JINGYU@jyzhao1 >explain plan for 
  2  select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; Explained. Elapsed: 00:00:00.01 JINGYU@jyzhao1 >select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 74 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) Note ----- - dynamic sampling used for this statement (level=2) 20 rows selected. Elapsed: 00:00:00.03 JINGYU@jyzhao1 >

2.set autotrace

通过set autotrace查看SQL的执行计划，这种方法SQL真实执行（除set autot trace exp针对select语句也不执行，但对DML语句还是会执行的），这种方法查看的执行计划有Predicate Information，有Statistics，查看的执行计划是准确的。但由于SQL需要真实执行，所以对于有绑定变量值的SQL，还需要输入对应的变量值才可以执行，比较麻烦。
使用set autotrace查看SQL的执行计划：

set autotrace on
select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; set autotrace off

示例如下：

JINGYU@jyzhao1 >set autotrace on
JINGYU@jyzhao1 >select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; EMPNO ENAME DNAME JOB SAL ---------- ---------- -------------- --------- ---------- 7788 SCOTT RESEARCH ANALYST 3000 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 74 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 814 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed JINGYU@jyzhao1 >set autotrace off JINGYU@jyzhao1 >

3.dbms_xplan

dbms_xplan下面有很多函数可以调用，其中最常用的是display_cursor和display_awr函数，下面依次介绍。

3.1 dbms_xplan.display_cursor(null,null,'allstats last')

通过dbms_xplan.display_cursor(null,null,'allstats last')查看SQL的执行计划，SQL会真实执行（对应的缺点不再赘述），这种方法查看的执行计划有Predicate Information，无Statistics，查看的执行计划是准确的，并且有每一步真实处理行数和时间。
使用dbms_xplan.display_cursor(null,null,'allstats last')查看SQL的执行计划：

--需要确认statistics_level设置为ALL，否则SQL语句第一个关键字后就需要加上/*+ gather_plan_statistics */，我习惯用前者
show parameter statistics_level（一般数据库默认是TYPICAL）
alter session set statistics_level = ALL; select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

示例如下：

JINGYU@jyzhao1 >alter session set statistics_level = ALL;

Session altered.

JINGYU@jyzhao1 >select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788; EMPNO ENAME DNAME JOB SAL ---------- ---------- -------------- --------- ---------- 7788 SCOTT RESEARCH ANALYST 3000 JINGYU@jyzhao1 >select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 39dv3d8jkzyuw, child number 1 ------------------------------------- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1123238657 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 | | | | |* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 7 | 1214K| 1214K| 762K (0)| |* 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 3 | | | | | 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | | ---------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. JINGYU@jyzhao1 >

3.2 dbms_xplan.display_cursor('&sql_id',null,'advanced')

通过dbms_xplan.display_cursor('&sql_id',null,'advanced')查看SQL的执行计划，这种方法查看的执行计划有Predicate Information，无Statistics，SQL是数据库中之前真实执行过的，对应执行计划是之前SQL真实执行过的执行计划，是准确的。使用这种方法的前提是需要SQL的cursor没有被刷出shared pool，否则查不到结果。
使用dbms_xplan.display_cursor('&sql_id',null,'advanced')查看SQL的执行计划：

--查询sql_id
select sql_id, sql_text from v$sql where sql_text like 'select a.empno%';
9dv3d8jkzyuw
--根据查询的sql_id查看执行计划
select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced')); --附：函数DISPLAY_CURSOR的参数说明 FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 IN DEFAULT CURSOR_CHILD_NO NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT

示例如下：

JINGYU@jyzhao1 >select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
Enter value for sql_id: 39dv3d8jkzyuw
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced')) new 1: select * from table(dbms_xplan.display_cursor('39dv3d8jkzyuw',null,'advanced')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 39dv3d8jkzyuw, child number 0 ------------------------------------- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / A@SEL$1 3 - SEL$1 / B@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "A"@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") USE_HASH(@"SEL$1" "B"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10], "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14] 2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10], "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22] 3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14] Note ----- - dynamic sampling used for this statement (level=2) SQL_ID 39dv3d8jkzyuw, child number 1 ------------------------------------- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 52 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / A@SEL$1 3 - SEL$1 / B@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "A"@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") USE_HASH(@"SEL$1" "B"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO") 2 - filter("EMPNO"=7788) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10], "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "B"."DNAME"[VARCHAR2,14] 2 - "EMPNO"[NUMBER,22], "A"."ENAME"[VARCHAR2,10], "A"."JOB"[VARCHAR2,9], "A"."SAL"[NUMBER,22], "A"."DEPTNO"[NUMBER,22] 3 - "B"."DEPTNO"[NUMBER,22], "B"."DNAME"[VARCHAR2,14] Note ----- - dynamic sampling used for this statement (level=2) SQL_ID 39dv3d8jkzyuw, child number 2 ------------------------------------- select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788 Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | |* 1 | HASH JOIN | | 1 | 74 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP |