首页 / 面试 / 【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含29094字，纯文字阅读大概需要42分钟。

内容图文

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

?题目 部分

在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即可查看执行计划：

 

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

此外，还可以通过写脚本从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程序员面试笔试宝典》，作者：李华荣。



【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

---------------优质麦课------------

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

 详细内容可以添加麦老师微信或QQ私聊。


【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片


About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片DBA宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片

【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？ - 文章图片喜欢就点击“好看”吧



内容总结

以上是互联网集市为您收集整理的【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？全部内容，希望文章能够帮你解决【DB笔试面试597】在Oracle中，获取执行计划有哪几种方法？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/858954.html

来源：【匿名】