使用 DBMS_XPLAN 查看执行计划
<span style="color:#000000;">SQL 优化的第一步，是查看 SQL 的执行计划。今天我们来说说查看执行计划的常用的一种方式 ：使用DBMS_XPLAN </span><br />
<br />
<span style="color:#000000;">如果PLan_table 不存在。可以通过下面脚本创建：</span><br />
<span style="color:#000000;">conn sys/password as sysdba</span><br />
<span style="color:#000000;">@$ORACLE_HOME/rdbms/admin/utlxplan.sql</span><br />
<span style="color:#000000;">CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;</span><br />
<span style="color:#000000;">GRANT ALL ON sys.plan_table TO public;</span><br />
<br />
<span style="color:#000000;">1. DISPLAY</span><span class="Apple-converted-space"><span style="color:#000000;"> 函数 </span><br />
<span style="color:#000000;">SQL> CONN scott/tiger</span><br />
<span style="color:#000000;">Connected.</span><br />
<span style="color:#000000;">SQL> </span><br />
<span style="color:#000000;">SQL> EXPLAIN PLAN FOR</span><br />
<span style="color:#000000;"> 2 SELECT *</span><br />
<span style="color:#000000;"> 3 FROM emp e, dept d</span><br />
<span style="color:#000000;"> 4 WHERE e.deptno = d.deptno</span><br />
<span style="color:#000000;"> 5 AND e.ename = 'SMITH';</span><br />
<br />
<span style="color:#000000;">Explained.</span><br />
<br />
<span style="color:#000000;">SQL> SET LINESIZE 130</span><br />
<span style="color:#000000;">SQL> SET PAGESIZE 20</span><br />
<span style="color:#000000;">SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">Plan hash value: 3625962092</span><br />
<br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">| 1 | NESTED LOOPS | | | | | |</span><br />
<span style="color:#000000;">| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<br />
<span style="color:#000000;">Predicate Information (identified by operation id):</span><br />
<span style="color:#000000;">---------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 3 - filter("E"."ENAME"='SMITH')</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;"> 4 - access("E"."DEPTNO"="D"."DEPTNO")</span><br />
<br />
<span style="color:#000000;">18 rows selected.</span><br />
<br />
<span style="color:#000000;">DBMS_XPLAN.DISPLAY 函数可以接受3个可选参数.</span><br />
<span style="color:#000000;">table_name 表名，默认是 'PLAN_TABLE'。</span><br />
<span style="color:#000000;">statement_id 要显示的ID 默认为空 NULL,表示最近在PLAN_TABLE的执行计划。</span><br />
<span style="color:#000000;">format 控制要输出的格式, 默认为 'TYPICAL'. 其他的值有 'BASIC', 'ALL', 'SERIAL'. 'ADVANCED' 。</span><span style="color:#000000;font-family:"font-style:normal;font-variant:normal;letter-spacing:normal;line-height:22.8571px;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;display:inline ! important;float:none;"><span class="Apple-converted-space"><br />
<br />
<span style="color:#000000;">SQL> EXPLAIN PLAN SET STATEMENT_ID='LS' FOR</span><br />
<span style="color:#000000;"> 2 SELECT *</span><br />
<span style="color:#000000;"> 3 FROM emp e, dept d</span><br />
<span style="color:#000000;"> 4 WHERE e.deptno = d.deptno</span><br />
<span style="color:#000000;"> 5 AND e.ename = 'SMITH';</span><br />
<br />
<span style="color:#000000;">Explained.</span><br />
<br />
<span style="color:#000000;">SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','LS','BASIC'));</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">Plan hash value: 3625962092</span><br />
<br />
<span style="color:#000000;">------------------------------------------------</span><br />
<span style="color:#000000;">| Id | Operation | Name |</span><br />
<span style="color:#000000;">------------------------------------------------</span><br />
<span style="color:#000000;">| 0 | SELECT STATEMENT | |</span><br />
<span style="color:#000000;">| 1 | NESTED LOOPS | |</span><br />
<span style="color:#000000;">| 2 | NESTED LOOPS | |</span><br />
<span style="color:#000000;">| 3 | TABLE ACCESS FULL | EMP |</span><br />
<span style="color:#000000;">| 4 | INDEX UNIQUE SCAN | PK_DEPT |</span><br />
<span style="color:#000000;">| 5 | TABLE ACCESS BY INDEX ROWID| DEPT |</span><br />
<span style="color:#000000;">------------------------------------------------</span><br />
<br />
<span style="color:#000000;">12 rows selected.</span><br />
<br />
<span style="color:#000000;">SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','LS','ADVANCED'));</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">Plan hash value: 3625962092</span><br />
<br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">| 1 | NESTED LOOPS | | | | | |</span><br />
<span style="color:#000000;">| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<br />
<span style="color:#000000;">Query Block Name / Object Alias (identified by operation id):</span><br />
<span style="color:#000000;">-------------------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 1 - SEL$1</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;"> 3 - SEL$1 / E@SEL$1</span><br />
<span style="color:#000000;"> 4 - SEL$1 / D@SEL$1</span><br />
<span style="color:#000000;"> 5 - SEL$1 / D@SEL$1</span><br />
<br />
<span style="color:#000000;">Outline Data</span><br />
<span style="color:#000000;">-------------</span><br />
<br />
<span style="color:#000000;"> /*+</span><br />
<span style="color:#000000;"> BEGIN_OUTLINE_DATA</span><br />
<span style="color:#000000;"> NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> USE_NL(@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))</span><br />
<span style="color:#000000;"> FULL(@"SEL$1" "E"@"SEL$1")</span><br />
<span style="color:#000000;"> OUTLINE_LEAF(@"SEL$1")</span><br />
<span style="color:#000000;"> ALL_ROWS</span><br />
<span style="color:#000000;"> DB_VERSION('11.2.0.1')</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;"> OPTIMIZER_FEATURES_ENABLE('11.2.0.1')</span><br />
<span style="color:#000000;"> IGNORE_OPTIM_EMBEDDED_HINTS</span><br />
<span style="color:#000000;"> END_OUTLINE_DATA</span><br />
<span style="color:#000000;"> */</span><br />
<br />
<span style="color:#000000;">Predicate Information (identified by operation id):</span><br />
<span style="color:#000000;">---------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 3 - filter("E"."ENAME"='SMITH')</span><br />
<span style="color:#000000;"> 4 - access("E"."DEPTNO"="D"."DEPTNO")</span><br />
<br />
<span style="color:#000000;">Column Projection Information (identified by operation id):</span><br />
<span style="color:#000000;">-----------------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],</span><br />
<span style="color:#000000;"> "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],</span><br />
<span style="color:#000000;"> "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;"> "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]</span><br />
<span style="color:#000000;"> 2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],</span><br />
<span style="color:#000000;"> "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],</span><br />
<span style="color:#000000;"> "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],</span><br />
<span style="color:#000000;"> "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],</span><br />
<span style="color:#000000;"> "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],</span><br />
<span style="color:#000000;"> "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]</span><br />
<br />
<span style="color:#000000;">61 rows selected.</span><br />
</span></span><br />
<span style="color:#000000;">ORACLE 10G 之后，oracle 引入了 DISPLAY_CURSOR 这个函数。</span><span style="color:#000000;" class="Apple-converted-space">DISPLAY_CURSOR</span><span style="color:#000000;">函数并不是从</span><span style="color:#000000;" class="Apple-converted-space">PLAN_TABLE</span><span style="color:#000000;"> 返回记录。而是从游标缓存获取数据。</span><br />
<span style="color:#000000;"></span><code><span style="color:#000000;">这些数据来自于</span></code><code style="box-sizing:border-box;font-family:Menlo, Monaco, Consolas, 'Courier New', monospace;font-size:14px;padding:2px 4px;color:#000000;border-top-left-radius:4px;border-top-right-radius:4px;border-bottom-right-radius:4px;border-bottom-left-radius:4px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;orphans:auto;text-align:start;text-indent:0px;text-transform:none;white-space:normal;widows:auto;word-spacing:0px;-webkit-text-stroke-width:0px;background-color:#FFFFFF;"><span style="color:#000000;">V$SQL_PLAN_STATISTICS_ALL</span></code><span style="color:#000000;font-family:"font-style:normal;font-variant:normal;letter-spacing:normal;line-height:22.8571px;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;display:inline ! important;float:none;"><span style="color:#000000;">,</span><span style="color:#000000;" class="Apple-converted-space"> </span></span><code style="box-sizing:border-box;font-family:Menlo, Monaco, Consolas, 'Courier New', monospace;font-size:14px;padding:2px 4px;color:#000000;border-top-left-radius:4px;border-top-right-radius:4px;border-bottom-right-radius:4px;border-bottom-left-radius:4px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;orphans:auto;text-align:start;text-indent:0px;text-transform:none;white-space:normal;widows:auto;word-spacing:0px;-webkit-text-stroke-width:0px;background-color:#FFFFFF;"><span style="color:#000000;">V$SQL</span></code><span style="color:#000000;font-family:"font-style:normal;font-variant:normal;letter-spacing:normal;line-height:22.8571px;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;display:inline ! important;float:none;"><span class="Apple-converted-space"> <code><span style="color:#000000;">和</span></code></span></span><code style="box-sizing:border-box;font-family:Menlo, Monaco, Consolas, 'Courier New', monospace;font-size:14px;padding:2px 4px;color:#000000;border-top-left-radius:4px;border-top-right-radius:4px;border-bottom-right-radius:4px;border-bottom-left-radius:4px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;orphans:auto;text-align:start;text-indent:0px;text-transform:none;white-space:normal;widows:auto;word-spacing:0px;-webkit-text-stroke-width:0px;background-color:#FFFFFF;"><span style="color:#000000;">V$SQL_PLAN。</span><br />
<span style="color:#000000;"></span><span style="color:#000000;">所以用</span><span style="color:#000000;" class="Apple-converted-space">DISPLAY_CURSOR</span><span style="color:#000000;"> 看到的执行计划是实际执行过的执行计划，是真实的。</span><br />
<span style="color:#000000;"></span></code><br />
<span style="color:#000000;">SQL> set linesize 300</span><br />
<span style="color:#000000;">SQL> set pagesize 300</span><br />
<span style="color:#000000;">SQL> SELECT * FROM emp e, dept d</span><br />
<span style="color:#000000;"> 2 WHERE e.deptno = d.deptno</span><br />
<span style="color:#000000;"> 3 AND e.ename = 'SMITH';</span><br />
<br />
<br />
<span style="color:#000000;">SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));</span><br />
<br />
<span style="color:#000000;">PLAN_TABLE_OUTPUT</span><br />
<span style="color:#000000;">------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">SQL_ID az3q3dcu2tzzx, child number 0</span><br />
<span style="color:#000000;">-------------------------------------</span><br />
<span style="color:#000000;">SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename</span><br />
<span style="color:#000000;"> = 'SMITH'</span><br />
<br />
<span style="color:#000000;">Plan hash value: 3625962092</span><br />
<br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<span style="color:#000000;">| 0 | SELECT STATEMENT | | | | 4 (100)| |</span><br />
<span style="color:#000000;">| 1 | NESTED LOOPS | | | | | |</span><br />
<span style="color:#000000;">| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |</span><br />
<span style="color:#000000;">| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |</span><br />
<span style="color:#000000;">----------------------------------------------------------------------------------------</span><br />
<br />
<span style="color:#000000;">Query Block Name / Object Alias (identified by operation id):</span><br />
<span style="color:#000000;">-------------------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 1 - SEL$1</span><br />
<span style="color:#000000;"> 3 - SEL$1 / E@SEL$1</span><br />
<span style="color:#000000;"> 4 - SEL$1 / D@SEL$1</span><br />
<span style="color:#000000;"> 5 - SEL$1 / D@SEL$1</span><br />
<br />
<span style="color:#000000;">Outline Data</span><br />
<span style="color:#000000;">-------------</span><br />
<br />
<span style="color:#000000;"> /*+</span><br />
<span style="color:#000000;"> BEGIN_OUTLINE_DATA</span><br />
<span style="color:#000000;"> IGNORE_OPTIM_EMBEDDED_HINTS</span><br />
<span style="color:#000000;"> OPTIMIZER_FEATURES_ENABLE('11.2.0.1')</span><br />
<span style="color:#000000;"> DB_VERSION('11.2.0.1')</span><br />
<span style="color:#000000;"> ALL_ROWS</span><br />
<span style="color:#000000;"> OUTLINE_LEAF(@"SEL$1")</span><br />
<span style="color:#000000;"> FULL(@"SEL$1" "E"@"SEL$1")</span><br />
<span style="color:#000000;"> INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))</span><br />
<span style="color:#000000;"> LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> USE_NL(@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")</span><br />
<span style="color:#000000;"> END_OUTLINE_DATA</span><br />
<span style="color:#000000;"> */</span><br />
<br />
<span style="color:#000000;">Predicate Information (identified by operation id):</span><br />
<span style="color:#000000;">---------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 3 - filter("E"."ENAME"='SMITH')</span><br />
<span style="color:#000000;"> 4 - access("E"."DEPTNO"="D"."DEPTNO")</span><br />
<br />
<span style="color:#000000;">Column Projection Information (identified by operation id):</span><br />
<span style="color:#000000;">-----------------------------------------------------------</span><br />
<br />
<span style="color:#000000;"> 1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],</span><br />
<span style="color:#000000;"> "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],</span><br />
<span style="color:#000000;"> "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],</span><br />
<span style="color:#000000;"> "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]</span><br />
<span style="color:#000000;"> 2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],</span><br />
<span style="color:#000000;"> "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],</span><br />
<span style="color:#000000;"> "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],</span><br />
<span style="color:#000000;"> "D"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],</span><br />
<span style="color:#000000;"> "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],</span><br />
<span style="color:#000000;"> "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]</span><br />
<span style="color:#000000;"> 5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]</span><br />
<br />
<br />
<span style="color:#000000;">67 rows selected.</span><br />
<br />
<span style="color:#000000;">还有几个函数包大家自行实验</span><span style="color:#000000;">：</span><br />
<span style="color:#000000;"> DISPLAY_AWR</span><br />
<span style="color:#000000;"> DISPLAY_SQLSET</span><br />
<span style="color:#000000;"> DISPLAY_SQL_PLAN_BASELINE</span><br />
<span style="color:#000000;"> DISPLAY_PLAN </span><br />
<br />
<span style="color:#000000;"></span><br />
<span style="color:#000000;"></span><br />
</span>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25444422/viewspace-2095130/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 表连接
下一篇：
oracle增加分区的方法
请登录后发表评论
登录
全部评论
