立即登录 马上注册
首页 › 开发帮助文档 › 数据库 › Oracle中使用DBMS_XPLAN处理执行计划详解

赞

评论

收藏

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

数据库 数据库 发布于：2022-01-27 18:20 | 阅读数：482 | 评论：0

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包在解释计划和执行计划上的应用。
来看一个经常使用的查看某条语句的解释计划示例：
SQL> explain plan for select * from scott.emp;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |    |  14 |   532 |   3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |  14 |   532 |   3   (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
-- 对应autotrace实现
SQL> set autotrace traceonly explain

上例中使用了dbms_xplan.display方法来显示PLAN_TABLE中保存的解释计划，如果想要显示执行计划，就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法了，DMBS_XPLAN.DISPLAY_CURSOR调用签名：
DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id    IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER  DEFAULT  NULL,
   format    IN  VARCHAR2  DEFAULT  'TYPICAL');
sql_id表示存储在cursor cache中的SQL语句的id，child_number用于指示缓存sql语句计划的子id，format参数用于控制包含在输出中的信息类型，官档的参数如下：
1.BASIC: 显示最少的信息，只包括操作类型，ID名称和选项。
2.TYPICAL: 默认值，显示相关信息以及某些附加的显示选项，如分区和并发使用等。
3.SERIAL: 与TYPICAL类型相似，区别是它不包括并发的信息，即使是并行执行的计划。
4.ALL: 显示最多的信息，包含了TYPICAL的全部以及更多的附加信息,如别名和远程调用等。
除了以上的基本的四种输出格式外，format还有一些附加的选项可用于定制化输出行为，使用中可以通过逗号和空格分隔来声明多个关键字，同时可以使用”+”和”-”符号来包含或排除相应的显示元素，这些附加的选项在官档中也有记载：
1.ROWS – 显示被优化器估算的记录的行号
2.BYTES – 显示优化器估算的字节数
3.COST – 显示优化器计算的成本信息
4.PARTITION – 显示分区的分割信息
5.PARALLEL – 显示并行执行信息
6.PREDICATE – 显示谓语
7.PROJECTION – 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
8.ALIAS – 显示查询块名称已经对象别名
9.REMOTE – 显示分布式查询信息
10.NOTE – 显示注释
11.IOSTATS – 显示游标执行的IO统计信息
12.MEMSTATS – 为内存密集运算如散列联结，排序，或一些类型的位图运算显示内存管理统计信息
13.ALLSTATS – 与'IOSTATS MEMSTATS'等价
14.LAST – 显示最后执行的执行计划统计信息，默认显示为ALL类型，并且可以累积。
以上的参数同样适用于解释计划的display方法。
示例部分：
一、使用display_cursor方法查看最近一条语句的执行计划
SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp;
  COUNT(*)
----------
    14
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  f9qyz8s3c2c02, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------
| Id  | Operation    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |    |    1 |    |    1 |00:00:00.01 |     1 |
|   1 |  SORT AGGREGATE  |    |    1 |    1 |    1 |00:00:00.01 |     1 |
|   2 |   INDEX FULL SCAN| PK_EMP |    1 |   14 |   14 |00:00:00.01 |     1 |
-------------------------------------------------------------------------------------
14 rows selected.
使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')时，将sql_id和child_number设置成null，表示获取上一条执行语句的执行计划；注意上面的例子中一定要指定gather_plan_statistics提示或者手动设置数据库STATISTICS_LEVEL参数为ALL来使得其抓取行数据源的执行统计信息，这些信息包括行数，一直读取次数，物理读次数，物理写次数以及运算在一行数据上耗费的运行时间，如果没有指定该提示，就不会有A-Rows,A-Time,Buffers这三列信息。
二、获取某条指定语句的执行计划
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);
  COUNT(*)
----------
     9
-- 通过v$sql视图查询到sql语句的SQL_ID和CHILD_NUMBER
SQL> select sql_id,child_number,sql_text from v$sql
  2  where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';
SQL_ID    CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
5qxmkvh40yw0p      0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
bqjrnskvpv51n      0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
               cs */ count(*)%'
-- 获取对应的执行计划
SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5qxmkvh40yw0p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.employees where
department_id in (90, 100)
Plan hash value: 4167091351
--------------------------------------------------------------------------------------------------
| Id  | Operation      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    1 |    |    1 |00:00:00.01 |     2 |
|   1 |  SORT AGGREGATE  |           |    1 |    1 |    1 |00:00:00.01 |     2 |
|   2 |   INLIST ITERATOR  |           |    1 |    |    9 |00:00:00.01 |     2 |
|*  3 |  INDEX RANGE SCAN| EMP_DEPARTMENT_IX |    2 |    9 |    9 |00:00:00.01 |     2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100))
21 rows selected.
三、通过format参数定制执行计划输出信息
-- 使用ALL来显示解释计划的全部信息
SQL> explain plan for
  2  select * from emp e, dept d
  3  where e.deptno = d.deptno
  4  and e.ename = 'JONES' ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   1 |  58 |   4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS        |     |     |     |      |      |
|   2 |   NESTED LOOPS         |     |   1 |  58 |   4   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS FULL     | EMP   |   1 |  38 |   3   (0)| 00:00:01 |
|*  4 |  INDEX UNIQUE SCAN     | PK_DEPT |   1 |     |   0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT  |   1 |  20 |   1   (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / 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.

关注下面的标签，发现更多相似文章
免责声明：

1. 本站所有资源来自网络搜集或用户上传，仅作为参考不担保其准确性！

2. 本站内容仅供学习和交流使用，版权归原作者所有！© 查看更多

3. 如有内容侵害到您，请联系我们尽快删除，邮箱：kf@codeae.com

分享到： QQ好友和群QQ好友和群 QQ空间QQ空间

举报

返回列表
相关帖子
avatar
3253 经验
3251 文档
OPNE在线字典
  • 手机版| 免责声明|
2021-2024  版权所有：CodeAE代码之家   
粤ICP备2023096576号-2