♣题目 部分
在Oracle中，获取执行计划有哪几种方法？
♣答案部分
一般来说，有如下几种获取执行计划的方式：
1、AUTOTRACE方式
AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*Plus会自动收集执行过的SQL语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。
DBA用户可以直接使用AUTOTRACE功能，但是如果用户没有DBA权限，那么需要在SYS用户下执行plustrce.sql脚本，自动创建PLUSTRACE角色，再把PLUSTRACE权限赋给普通用户即可。
1$ORACLE_HOME/sqlplus/admin/plustrce.sql2GRANT PLUSTRACE TO USER_LHR; 2GRANT PLUSTRACE TO USER_LHR;
另外，若启用AUTOTRACE报“SP2-0611”的错误，则可以执行utlxplan.sql脚本来创建表PLAN_TABLE，如下所示：
1SQL> set autot on2SP2-0613: 无法验证 PLAN_TABLE 格式或实体3SP2-0611: 启用EXPLAIN报告时出错4SQL> @?/rdbms/admin/utlxplan.sqlset autot on 2SP2-0613: 无法验证 PLAN_TABLE 格式或实体 3SP2-0611: 启用EXPLAIN报告时出错 4SQL> @?/rdbms/admin/utlxplan.sql
在执行如下脚本后，每个用户（包括以后新建的用户）都可以使用AUTOTRACE命令：
1@?/rdbms/admin/utlxplan.sql2CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;3GRANT ALL ON PLAN_TABLE TO PUBLIC;4@?/sqlplus/admin/plustrce.sql5GRANT PLUSTRACE TO PUBLIC; 2CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; 3GRANT ALL ON PLAN_TABLE TO PUBLIC; 4@?/sqlplus/admin/plustrce.sql 5GRANT PLUSTRACE TO PUBLIC;
AUTOTRACE的语法如下所示：
1SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS]SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS]
其中，AUTOTRACE可简写为AUTOT，TRACEONLY可简写为TRACE，EXPLAIN可简写为EXP，STATISTICS可简写为STAT。
1SQL> SET AUTOT ON 2SQL> SELECT COUNT(*) FROM PLAN_TABLE; 3 COUNT(*) 4---------- 5 68 6Execution Plan 7---------------------------------------------------------- 8Plan hash value: 1751138260 9--------------------------------------------------------------------------10| Id | Operation | Name | Rows | Cost (%CPU)| Time |11--------------------------------------------------------------------------12| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |13| 1 | SORT AGGREGATE | | 1 | | |14| 2 | TABLE ACCESS FULL| PLAN_TABLE$ | 68 | 3 (0)| 00:00:01 |15--------------------------------------------------------------------------16Note17-----18 - dynamic sampling used for this statement19Statistics20----------------------------------------------------------21 27 recursive calls22 0 db block gets23 15 consistent gets24 0 physical reads25 0 redo size26 515 bytes sent via SQL*Net to client27 487 bytes received via SQL*Net from client28 2 SQL*Net roundtrips to/from client29 0 sorts (memory)30 0 sorts (disk)31 1 rows processedSET AUTOT ON 2SQL> SELECT COUNT(*) FROM PLAN_TABLE; 3 COUNT(*) 4---------- 5 68 6Execution Plan 7---------------------------------------------------------- 8Plan hash value: 1751138260 9-------------------------------------------------------------------------- 10| Id | Operation | Name | Rows | Cost (%CPU)| Time | 11-------------------------------------------------------------------------- 12| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | 13| 1 | SORT AGGREGATE | | 1 | | | 14| 2 | TABLE ACCESS FULL| PLAN_TABLE$ | 68 | 3 (0)| 00:00:01 | 15-------------------------------------------------------------------------- 16Note 17----- 18 - dynamic sampling used for this statement 19Statistics 20---------------------------------------------------------- 21 27 recursive calls 22 0 db block gets 23 15 consistent gets 24 0 physical reads 25 0 redo size 26 515 bytes sent via SQL*Net to client 27 487 bytes received via SQL*Net from client 28 2 SQL*Net roundtrips to/from client 29 0 sorts (memory) 30 0 sorts (disk) 31 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方式
1SQL> EXPLAIN PLAN FOR SELECT * FROM T017_LHRO; 2SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 3PLAN_TABLE_OUTPUT 4-------------------------------------------------------------------------------- 5Plan hash value: 3200443156 6------------------------------------------------------------------------------- 7| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 8------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 1363 | 177K| 9 (0)| 00:00:01 |10| 1 | TABLE ACCESS FULL| T017_LHRO | 1363 | 177K| 9 (0)| 00:00:01 |11-------------------------------------------------------------------------------EXPLAIN PLAN FOR SELECT * FROM T017_LHRO; 2SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 3PLAN_TABLE_OUTPUT 4-------------------------------------------------------------------------------- 5Plan hash value: 3200443156 6------------------------------------------------------------------------------- 7| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 8------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 1363 | 177K| 9 (0)| 00:00:01 | 10| 1 | TABLE ACCESS FULL| T017_LHRO | 1363 | 177K| 9 (0)| 00:00:01 | 11-------------------------------------------------------------------------------
3、DBMS_XPLAN.DISPLAY_CURSOR方式
1SYS@RAC2LHR1> SELECT * FROM V$VERSION WHERE ROWNUM<2; 2BANNER 3-------------------------------------------------------------------------------- 4Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 5SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 6 2 FROM SCOTT.EMP E,SCOTT.DEPT D 7 3 WHERE E.DEPTNO = D.DEPTNO 8 4 AND E.EMPNO = 7788; 9ENAME DNAME LOC10---------- -------------- -------------11SCOTT RESEARCH DALLASSELECT * FROM V$VERSION WHERE ROWNUM<2; 2BANNER 3-------------------------------------------------------------------------------- 4Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 5SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 6 2 FROM SCOTT.EMP E,SCOTT.DEPT D 7 3 WHERE E.DEPTNO = D.DEPTNO 8 4 AND E.EMPNO = 7788; 9ENAME DNAME LOC 10---------- -------------- ------------- 11SCOTT RESEARCH DALLAS
如果不传递任何参数给DISPLAY_CURSOR函数，那么默认显示当前会话最后一条SQL语句的执行计划，如下所示：
1SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL)); 2PLAN_TABLE_OUTPUT 3----------------------------------------------------------------------------- 4SQL_ID 315xan8zgvtbm, child number 0 5------------------------------------- 6SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = 7D.DEPTNO AND E.EMPNO = 7788 8Plan hash value: 1674520956 9----------------------------------------------------------------------------------------10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |11----------------------------------------------------------------------------------------12| 0 | SELECT STATEMENT | | | | 2 (100)| |13| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 |14| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 |15|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |16| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |17|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |18----------------------------------------------------------------------------------------19Predicate Information (identified by operation id):20---------------------------------------------------21 3 - access("E"."EMPNO"=7788)22 5 - access("E"."DEPTNO"="D"."DEPTNO")2324 rows selected.SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL));
2PLAN_TABLE_OUTPUT
3-----------------------------------------------------------------------------
4SQL_ID 315xan8zgvtbm, child number 0
5-------------------------------------
6SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =
7D.DEPTNO AND E.EMPNO = 7788
8Plan hash value: 1674520956
9----------------------------------------------------------------------------------------
10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
11----------------------------------------------------------------------------------------
12| 0 | SELECT STATEMENT | | | | 2 (100)| |
13| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 |
14| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 |
15|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
16| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
17|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
18----------------------------------------------------------------------------------------
19Predicate Information (identified by operation id):
20---------------------------------------------------
21 3 - access("E"."EMPNO"=7788)
22 5 - access("E"."DEPTNO"="D"."DEPTNO")
2324 rows selected.
传递SQL_ID以及FORMAT参数给DISPLAY_CURSOR函数，并配合修饰符控制执行计划的输出，如下所示：
1SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('315XAN8ZGVTBM',NULL,'ALL')); 2PLAN_TABLE_OUTPUT 3---------------------------------------------------------------------------- 4SQL_ID 315xan8zgvtbm, child number 0 5------------------------------------- 6SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO = 7D.DEPTNO AND E.EMPNO = 7788 8Plan hash value: 1674520956 9----------------------------------------------------------------------------------------10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |11----------------------------------------------------------------------------------------12| 0 | SELECT STATEMENT | | | | 2 (100)| |13| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 |14| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 |15|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |16| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |17|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |18----------------------------------------------------------------------------------------19Query Block Name / Object Alias (identified by operation id):20-------------------------------------------------------------21 1 - SEL$122 2 - SEL$1 / E@SEL$123 3 - SEL$1 / E@SEL$124 4 - SEL$1 / D@SEL$125 5 - SEL$1 / D@SEL$126Predicate Information (identified by operation id):27---------------------------------------------------28 3 - access("E"."EMPNO"=7788)29 5 - access("E"."DEPTNO"="D"."DEPTNO")30Column Projection Information (identified by operation id):31-----------------------------------------------------------32 1 - "ENAME"[VARCHAR2,10], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]33 2 - "ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]34 3 - "E".ROWID[ROWID,10]35 4 - "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]36 5 - "D".ROWID[ROWID,10]3742 rows selected.SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('315XAN8ZGVTBM',NULL,'ALL'));
2PLAN_TABLE_OUTPUT
3----------------------------------------------------------------------------
4SQL_ID 315xan8zgvtbm, child number 0
5-------------------------------------
6SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =
7D.DEPTNO AND E.EMPNO = 7788
8Plan hash value: 1674520956
9----------------------------------------------------------------------------------------
10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
11----------------------------------------------------------------------------------------
12| 0 | SELECT STATEMENT | | | | 2 (100)| |
13| 1 | NESTED LOOPS | | 1 | 33 | 2 (0)| 00:00:01 |
14| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 1 (0)| 00:00:01 |
15|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
16| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
17|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
18----------------------------------------------------------------------------------------
19Query Block Name / Object Alias (identified by operation id):
20-------------------------------------------------------------
21 1 - SEL$1
22 2 - SEL$1 / E@SEL$1
23 3 - SEL$1 / E@SEL$1
24 4 - SEL$1 / D@SEL$1
25 5 - SEL$1 / D@SEL$1
26Predicate Information (identified by operation id):
27---------------------------------------------------
28 3 - access("E"."EMPNO"=7788)
29 5 - access("E"."DEPTNO"="D"."DEPTNO")
30Column Projection Information (identified by operation id):
31-----------------------------------------------------------
32 1 - "ENAME"[VARCHAR2,10], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
33 2 - "ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]
34 3 - "E".ROWID[ROWID,10]
35 4 - "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
36 5 - "D".ROWID[ROWID,10]
3742 rows selected.
利用STATISTICS_LEVEL或/*+ GATHER_PLAN_STATISTICS*/可以知道表访问的次数，也可以查看真实执行计划并获得统计信息。如下所示：
1SET SERVEROUTPUT OFF2ALTER SESSION SET STATISTICS_LEVEL=ALL; 3执行SQL语句4SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));5SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID =>'',CURSOR_CHILD_NO =>1,FORMAT => 'ADVANCED ALLSTATS'));SET SERVEROUTPUT OFF 2ALTER SESSION SET STATISTICS_LEVEL=ALL; 3执行SQL语句 4SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST')); 5SELECT * 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），否则会报类似如下的错误：
1PLAN_TABLE_OUTPUT2--------------------------------------------------------------------------------3SQL_ID 9m7787camwh4m, child number 04begin :id := sys.dbms_transaction.local_transaction_id; end;5NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 06 Please verify value of SQL_ID and CHILD_NUMBER;7 It could also be that the plan is no longer in cursor cache (check v$sql_plan) 2-------------------------------------------------------------------------------- 3SQL_ID 9m7787camwh4m, child number 0 4begin :id := sys.dbms_transaction.local_transaction_id; end; 5NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0 6 Please verify value of SQL_ID and CHILD_NUMBER; 7 It could also be that the plan is no longer in cursor cache (check v$sql_plan)
若为具体SQL_ID的值的话，则无论SERVEROUTPUT的值如何都可以正常执行。
示例如下所示：
1SYS@RAC2LHR1> SHOW PARAMETER STATISTICS_LEVEL 2NAME TYPE VALUE 3------------------------------------ ----------- ------------------------------ 4statistics_level string TYPICAL 5SYS@RAC2LHR1> ALTER SESSION SET STATISTICS_LEVEL=ALL; 6Session altered. 7SYS@RAC2LHR1> SHOW SERVEROUTPUT 8serveroutput OFF 9SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC 10 2 FROM SCOTT.EMP E,SCOTT.DEPT D 11 3 WHERE E.DEPTNO = D.DEPTNO 12 4 AND E.EMPNO = 7369; 13ENAME DNAME LOC14---------- -------------- -------------15SMITH RESEARCH DALLAS16SYS@RAC2LHR1> SET PAGESIZE 0 17SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS LAST -PREDICATE -NOTE'));18SQL_ID g3mx9hdyrhus7, child number 019-------------------------------------20SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =21D.DEPTNO AND E.EMPNO = 736922Plan hash value: 167452095623--------------------------------------------------------------------------------------------------24| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |25--------------------------------------------------------------------------------------------------26| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |27| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |28| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |29| 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |30| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |31| 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |32--------------------------------------------------------------------------------------------------33SYS@RAC2LHR1> SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC 34 2 FROM SCOTT.EMP E,SCOTT.DEPT D 35 3 WHERE E.DEPTNO = D.DEPTNO 36 4 AND E.EMPNO = 7369; 37SMITH RESEARCH DALLAS38SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC LAST ALLSTATS'));39EXPLAINED SQL STATEMENT:40------------------------41SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC FROM SCOTT.EMP42E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 736943Plan hash value: 167452095644--------------------------------------------------------------------------------------------------45| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |46--------------------------------------------------------------------------------------------------47| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |48| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |49| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |50|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |51| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |52|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |53--------------------------------------------------------------------------------------------------54Predicate Information (identified by operation id):55---------------------------------------------------56 3 - access("E"."EMPNO"=7369)57 5 - access("E"."DEPTNO"="D"."DEPTNO")SHOW PARAMETER STATISTICS_LEVEL
2NAME TYPE VALUE
3------------------------------------ ----------- ------------------------------
4statistics_level string TYPICAL
5SYS@RAC2LHR1> ALTER SESSION SET STATISTICS_LEVEL=ALL;
6Session altered.
7SYS@RAC2LHR1> SHOW SERVEROUTPUT
8serveroutput OFF
9SYS@RAC2LHR1> SELECT ENAME,DNAME,LOC
10 2 FROM SCOTT.EMP E,SCOTT.DEPT D
11 3 WHERE E.DEPTNO = D.DEPTNO
12 4 AND E.EMPNO = 7369;
13ENAME DNAME LOC
14---------- -------------- -------------
15SMITH RESEARCH DALLAS
16SYS@RAC2LHR1> SET PAGESIZE 0
17SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS LAST -PREDICATE -NOTE'));
18SQL_ID g3mx9hdyrhus7, child number 0
19-------------------------------------
20SELECT ENAME,DNAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO =
21D.DEPTNO AND E.EMPNO = 7369
22Plan hash value: 1674520956
23--------------------------------------------------------------------------------------------------
24| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
25--------------------------------------------------------------------------------------------------
26| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
27| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
28| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
29| 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
30| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |
31| 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
32--------------------------------------------------------------------------------------------------
33SYS@RAC2LHR1> SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC
34 2 FROM SCOTT.EMP E,SCOTT.DEPT D
35 3 WHERE E.DEPTNO = D.DEPTNO
36 4 AND E.EMPNO = 7369;
37SMITH RESEARCH DALLAS
38SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC LAST ALLSTATS'));
39EXPLAINED SQL STATEMENT:
40------------------------
41SELECT /*+ GATHER_PLAN_STATISTICS */ ENAME,DNAME,LOC FROM SCOTT.EMP
42E,SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = 7369
43Plan hash value: 1674520956
44--------------------------------------------------------------------------------------------------
45| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
46--------------------------------------------------------------------------------------------------
47| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
48| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
49| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
50|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:00.01 | 1 |
51| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |
52|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
53--------------------------------------------------------------------------------------------------
54Predicate Information (identified by operation id):
55---------------------------------------------------
56 3 - access("E"."EMPNO"=7369)
57 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语句是否真实执行过 |
是否真实执行计划 |
物理读、逻辑读、递归调用 |
运行时间 |
处理行数 |
表访问次数 |
等待事件 |
解析时间 |
|
set autotrace |
SET AUTOTRACE OFF |
此为默认值，即关闭AUTOTRACE |
||||||||
SET AUTOTRACE ON |
包含SQL语句的执行结果、SQL语句执行结果的数量、执行计划和统计信息内容 |
是 |
不确定 |
有 |
有 |
有 |
无 |
无 |
无 |
|
SET AUTOTRACE ON EXPLAIN |
包含SQL语句的执行结果、SQL语句执行结果的数量和执行计划 |
无 |
有 |
有 |
无 |
无 |
无 |
|||
SET AUTOTRACE ON STATISTICS |
包含SQL语句的执行结果、SQL语句执行结果的数量和统计信息内容 |
有 |
有 |
有 |
无 |
无 |
无 |
|||
SET AUTOTRACE TRACEONLY |
包含SQL执行结果的数量、执行计划和统计信息内容，但不显示SQL语句的执行结果 |
有 |
有 |
有 |
无 |
无 |
无 |
|||
SET AUTOTRACE TRACEONLY EXPLAIN |
同EXPLAIN PLAN命令，对于SELECT语句不会执行，只显示目标SQL的执行计划，但是对于DML语句还是会执行的，而且显示SQL语句执行结果的数量和执行计划 |
否 |
无 |
有 |
有 |
无 |
无 |
无 |
||
SET AUTOTRACE TRACEONLY STATISTICS |
显示SQL语句执行结果的数量和统计信息，不显示执行计划和SQL执行结果 |
是 |
有 |
有 |
有 |
无 |
无 |
无 |
||
DBMS_XPLAN |
EXPLAIN PLAN FOR DBMS_XPLAN.DISPLAY |
EXPLAIN PLAN FOR SQL语句; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ADVANCED')); |
否 |
不确定 |
无 |
无 |
无 |
无 |
无 |
无 |
|
STATISTICS_LEVEL=ALL SELECT /*+ GATHER_PLAN_STATISTICS*/ ... DBMS_XPLAN.DISPLAY_CURSOR |
ALTER SESSION SET STATISTICS_LEVEL=ALL ; 执行SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',0,'BASIC LAST ALLSTATS ADVANCED')); |
是 |
是 |
无 |
有 |
有 |
有 |
无 |
无 |
|
DBMS_XPLAN.DISPLAY_CURSOR |
没有设置STATISTICS_LEVEL=ALL或没有使用/*+ GATHER_PLAN_STATISTICS*/的Hint： SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID')); --从内存得到执行计划 |
是 |
是 |
无 |
无 |
无 |
无 |
无 |
无 |
|
DBMS_XPLAN.DISPLAY_AWR |
DISPLAY_AWR函数显示存储在AWR历史数据的执行计划。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQLID')); |
是 |
是 |
无 |
无 |
无 |
无 |
无 |
无 |
|
DBMS_XPLAN.DISPLAY_SQLSET |
DISPLAY_SQLSET函数显示存储在一个SQL调优集中的语句的执行计划，SQL调优集查询DBA_SQLSET_STATEMENTS，查询执行计划的SQL语句为：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQLSET('&SQLSET','&SQLID',NULL,'BASIC ALLSTATS ADVANCED')); |
是 |
是 |
无 |
无 |
无 |
无 |
无 |
无 |
|
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE |
DISPLAY_SQL_PLAN_BASELINE函数显示存储在数据字典当中SQL执行计划基线的计划。执行计划基线所属SQL的句柄名称（SQL_HANDLE）可以通过视图DBA_SQL_PLAN_BASELINES查询，查询执行计划的SQL语句为：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => )); |
是 |
是 |
无 |
无 |
无 |
无 |
无 |
无 |
|
SQL_TRACE、事件10046、10053跟踪 |
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'; 执行SQL语句 ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; TKPROF格式化TRACE文件 |
是 |
是 |
有 |
有 |
有 |
无 |
有 |
有 |
|
awrsqrpt.sql |
@?/rdbms/admin/awrsqrpt.sql SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(V_DBID,V_INST_ID,V_MIN_SNAP_ID,V_MAX_SNAP_ID,V_SQLID)); |
是 |
是 |
有 |
有 |
有 |
无 |
有 |
有 |
|
SQL实时监控特性：DBMS_SQLTUNE.REPORT_SQL_MONITOR |
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('&SQLID') FROM DUAL ; SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'&SQLID',TYPE=>'ACTIVE',REPORT_LEVEL=>'ALL') AS REPORT FROM DUAL; |
是 |
是 |
有 |
有 |
有 |
有 |
有 |
有 |
|
其它工具 |
利用第三方工具，如PL/SQL DEV、TODO等开发工具，在PL/SQL DEV中选定SQL后，按F5即可查看执行计划 |
否 |
不确定 |
无 |
无 |
无 |
无 |
无 |
无 |
|
对于这几种获取执行计划的方法有如下结论：
① 若目标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程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧