Oracle中使用DBMS_XPLAN处理执行计划详解


DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。

这5个函数分别对应不同的显示计划的方式,DBMS_XPLAN包不仅可以获取解释计划,它还可以用来输出存储在AWR,SQL调试集,缓存的SQL游标,以及SQL基线中的语句计划,实现如上的功能,通常会用到一下5个方法:

1.DISPLAY
2.DISPLAY_AWR
3.DISPLAY_CURSOR
4.DISPLAY_PLAN
5.DISPLAY_SQL_PLAN_BASELINE
6.DISPLAY_SQLSET

下面将重点讨论关于DBMS_XPLAN包在解释计划和执行计划上的应用。

来看一个经常使用的查看某条语句的解释计划示例:

E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Predicate Information (identified by operation id):

   3 - filter("E"."ENAME"='JONES')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

43 rows selected.

-- 去除执行计划上的字节数和成本统计信息
SQL> select empno, ename from emp e, dept d
  2  where e.deptno = d.deptno
  3  and e.ename = 'JONES' ;

     EMPNO ENAME
---------- ----------
      7566 JONES

SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  3mypf7d6npa97, child number 1
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'JONES'

Plan hash value: 3956160932

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       8 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))

19 rows selected.

-- 另一种选项,窥视绑定变量的值,非常方便!!
SQL> variable v_empno number
SQL> exec :v_empno := 7566 ;

PL/SQL procedure successfully completed.

SQL> select * from emp where empno = :v_empno ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9q17w9umt58m7, child number 0
-------------------------------------
select * from emp where empno = :v_empno

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :V_EMPNO (NUMBER): 7566

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("EMPNO"=:V_EMPNO)

24 rows selected.

-- 并行查询信息筛选
SQL> select /*+ parallel(d, 4) parallel (e, 4) */
  2  d.dname, avg(e.sal), max(e.sal)
  3  from dept d, emp e
  4  where d.deptno = e.deptno
  5  group by d.dname
  6  order by max(e.sal), avg(e.sal) desc;

DNAME          AVG(E.SAL) MAX(E.SAL)
-------------- ---------- ----------
SALES          1566.66667       2850
RESEARCH             2175       3000
ACCOUNTING     2916.66667       5000

SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc

Plan hash value: 3078011448

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |          |        |      |            |
|   1 |  PX COORDINATOR               |          |       |          |        |      |            |
|   2 |   PX SEND QC (ORDER)          | :TQ10004 |     4 | 00:00:01 |  Q1,04 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY              |          |     4 | 00:00:01 |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                |          |     4 | 00:00:01 |  Q1,04 | PCWP |            |
|   5 |      PX SEND RANGE            | :TQ10003 |     4 | 00:00:01 |  Q1,03 | P->P | RANGE      |
|   6 |       HASH GROUP BY           |          |     4 | 00:00:01 |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE             |          |    14 | 00:00:01 |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH          | :TQ10002 |    14 | 00:00:01 |  Q1,02 | P->P | HASH       |
|*  9 |          HASH JOIN BUFFERED   |          |    14 | 00:00:01 |  Q1,02 | PCWP |            |
|  10 |           PX RECEIVE          |          |     4 | 00:00:01 |  Q1,02 | PCWP |            |
|  11 |            PX SEND HASH       | :TQ10000 |     4 | 00:00:01 |  Q1,00 | P->P | HASH       |
|  12 |             PX BLOCK ITERATOR |          |     4 | 00:00:01 |  Q1,00 | PCWC |            |
|* 13 |              TABLE ACCESS FULL| DEPT     |     4 | 00:00:01 |  Q1,00 | PCWP |            |
|  14 |           PX RECEIVE          |          |    14 | 00:00:01 |  Q1,02 | PCWP |            |
|  15 |            PX SEND HASH       | :TQ10001 |    14 | 00:00:01 |  Q1,01 | P->P | HASH       |
|  16 |             PX BLOCK ITERATOR |          |    14 | 00:00:01 |  Q1,01 | PCWC |            |
|* 17 |              TABLE ACCESS FULL| EMP      |    14 | 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("D"."DEPTNO"="E"."DEPTNO")
  13 - access(:Z>=:Z AND :Z<=:Z)   17 - access(:Z>=:Z AND :Z<=:Z)

38 rows selected.




相关阅读:
CSS中height和width在IE和其他浏览器中的区别图文详解
Win8系统交换机如何设置?Win8系统设置交换机的方法
Android中简单调用图片、视频、音频、录音和拍照的方法
基于HTML模板和JSON数据的JavaScript交互(移动端)
jQuery实现的多屏图像图层切换效果实例
javascript根据像素点取位置示例
重装win7系统后开机提示inconsistent filesystem导致无法启动的故障分析及解决方法
AngularJS 使用 UI Router 实现表单向导
Backbone View 之间通信的三种方式
C#求n个数中最大值和最小值的方法
理解Java访问权限控制
Win7下手动安装apache2.2、php5.4笔记
js中this的用法实例分析
说明Java的传递与回调机制的代码示例分享
快速导航

Copyright © 2016 phpStudy |