DBMS_XPLAN详细说明

执行计划的组成部分

正确的看执行计划

DBMS_XPLAN 这个包是一个很好查看执行计划，显示很多格式，来分析执行计划中存在的问题

format：控制详细执行计划输出的格式，包含以下内容：

  • BASIC：显示最少的信息-ID，operation name
  • TYPICAL :默认，在计划中显示最多相关信息（operation id,name，rows，bytes和cost），在适用时显示pruning 、parallel和predicate 信息
  • SERIAL：像typical 除了并行信息不显示，即使在并行中执行
  • ALL：最大用户级别，包含信息显示TYPICAL级别，在增加（PROJECTION、ALLAS和在分布式操作中REMOTE SQL）

format 关键字必须用逗号或者空格隔开

  • ROWS-相关，显示优化器评估的行数
  • BYTES-显示优化器评估的字节数量
  • COST-显示优化器评估的代价信息
  • PARATITION-显示优化器的裁集信息
  • PARALLEL-显示PX信息（分配方法和表队列的信息）
  • PREDICATE-显示谓词章节信息（predicate ）
  • PROJECTION-显示字段定义信息（projection）
  • ALLAS-显示查询块名称/对象别名（QUERY BLOCK NAME/OBJECT ALIAS）
  • REMOTE-显示分布式查询信息（如远程从分布式远程SQL）
  • NOTE-显示计划中NOTE信息
  • IOSTATS-当SQL语句被执行时，假如基本的计划统计信息被收集（使用gather_plan_statistics hint或设置statistics_level参数设置为ALL），用格式ALL来显示IO统计信息（或仅用LAST显示）对这个游标的执行
  • MEMSTATS-假如PGA内存管理被启用（这个是，pga_aggregate_target参数设置非为0），这个格式允许显示内存的统计信息（如，操作者执行模式，内存被多少使用，多少的字节数被写入磁盘等），这些统计信息应用到内存敏感的操作如hash-join，排序或一些位图操作。
  • ALLSTATS-‘IOSTATS MEMSTATS’的缩写
  • LAST-默认，计划统计信息被显示游标中的全部的执行计划，关键字LAST被指定仅查看语句的最后一次执行计划

以下两种已经过时，但是支持向后的兼容性：

  • RUNSTATS_TOT-跟IOSTATS类同，显示IO统计信息指定游标的全部统计信息
  • RUNSTATS_LAST跟 IOSTATS LAST ,显示运行统计信息的游标最后一次统计信息

格式化关键字可以前缀通过标记‘-’排除指定的信息，如：‘-PROJECTION’就排除了projection 信息

类别分类和显示

class level alias bytes cost note predicate parallel partition projection outlines peeked_binds remote rows
BASIC                        
TYPICAL   Y Y Y Y Y Y       Y Y
SERIAL   Y Y Y Y             Y
ALL Y Y Y Y Y Y Y Y Y   Y Y
ADVANCED Y Y Y Y Y Y Y Y Y Y Y Y

执行计划的组成部分

  • 目标SQL语句
  • 执行计划的主要部分：路径、查询块命名、OUTline data、Predicate Information 、Column Projection Information
  • 辅助信息：NOTE（11g的基数评估、sql profile、动态采样）

找到SQL_ID,调用DBMS_XPLAN包，格式化执行计划

SELECT
  SQL_ID,
  CHILD_NUMBER
FROM
  V$SQL
WHERE
  SQL_TEXT LIKE 'select * from GLL_OBJECT_0602  where object_id=7782%'
  AND SQL_TEXT NOT LIKE '%V$SQL%';
  

  SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75nc852bx00ub',0,'ADVANCED'));

SQL_ID  75nc852bx00ub, child number 0
-------------------------------------
select * from GLL_OBJECT_0602  where object_id=7782
 
Plan hash value: 2093672533
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| GLL_OBJECT_0602 |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECT      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / GLL_OBJECT_0602@SEL$1
   2 - SEL$1 / GLL_OBJECT_0602@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "GLL_OBJECT_0602"@"SEL$1" ("GLL_OBJECT_0602"."OBJECT_ID"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=7782)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "GLL_OBJECT_0602"."OWNER"[VARCHAR2,30], 
       "GLL_OBJECT_0602"."OBJECT_NAME"[VARCHAR2,128], 
       "GLL_OBJECT_0602"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22], 
       "GLL_OBJECT_0602"."DATA_OBJECT_ID"[NUMBER,22], 
       "GLL_OBJECT_0602"."OBJECT_TYPE"[VARCHAR2,19], "GLL_OBJECT_0602"."CREATED"[DATE,7], 
       "GLL_OBJECT_0602"."LAST_DDL_TIME"[DATE,7], "GLL_OBJECT_0602"."TIMESTAMP"[VARCHAR2,19], 
       "GLL_OBJECT_0602"."STATUS"[VARCHAR2,7], "GLL_OBJECT_0602"."TEMPORARY"[VARCHAR2,1], 
       "GLL_OBJECT_0602"."GENERATED"[VARCHAR2,1], "GLL_OBJECT_0602"."SECONDARY"[VARCHAR2,1], 
       "GLL_OBJECT_0602"."NAMESPACE"[NUMBER,22], "GLL_OBJECT_0602"."EDITION_NAME"[VARCHAR2,30]
   2 - "GLL_OBJECT_0602".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement (level=2)

输出格式中真实ROWS

获取SQL_ID

select /*+gather_plan_statistics*/ * from GLL_OBJECT_0602  where object_id=:id

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3ap5ukwk8gs9g',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));
SQL_ID  3ap5ukwk8gs9g, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ * from GLL_OBJECT_0602  where 
object_id=:id
 
Plan hash value: 3717822783
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |       |   293 (100)|      1 |00:00:00.01 |    1072 |
|*  1 |  TABLE ACCESS FULL| GLL_OBJECT_0602 |      1 |    872 |   176K|   293   (1)|      1 |00:00:00.01 |    1072 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / GLL_OBJECT_0602@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_ID"=TO_NUMBER(:ID))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "GLL_OBJECT_0602"."OWNER"[VARCHAR2,30], "GLL_OBJECT_0602"."OBJECT_NAME"[VARCHAR2,128], 
       "GLL_OBJECT_0602"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22], 
       "GLL_OBJECT_0602"."DATA_OBJECT_ID"[NUMBER,22], "GLL_OBJECT_0602"."OBJECT_TYPE"[VARCHAR2,19], 
       "GLL_OBJECT_0602"."CREATED"[DATE,7], "GLL_OBJECT_0602"."LAST_DDL_TIME"[DATE,7], 
       "GLL_OBJECT_0602"."TIMESTAMP"[VARCHAR2,19], "GLL_OBJECT_0602"."STATUS"[VARCHAR2,7], 
       "GLL_OBJECT_0602"."TEMPORARY"[VARCHAR2,1], "GLL_OBJECT_0602"."GENERATED"[VARCHAR2,1], 
       "GLL_OBJECT_0602"."SECONDARY"[VARCHAR2,1], "GLL_OBJECT_0602"."NAMESPACE"[NUMBER,22], 
       "GLL_OBJECT_0602"."EDITION_NAME"[VARCHAR2,30]

查询块的输出，可以把内联视图的信息，在主查询中去定义全表扫描还是索引访问

SQL_ID  896fbd4yjghj8, child number 0
-------------------------------------
select /*+ FULL(@strange dept) */ ENAME from emp e, (select /*+ 
QB_NAME(strange) */* from dept where deptno=20) d where 
e.deptno=d.deptno and d.loc= 'DALLAS'
 
Plan hash value: 4192419542
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS      |      |     1 |    31 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     5 |   100 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$DB579D14
   2 - SEL$DB579D14 / DEPT@STRANGE
   3 - SEL$DB579D14 / E@SEL$1
前缀 行源操作
CRI$ CREATE INDEX statement
DEL$ DELETE STATEMENT
INS$ INSERT statement
MISC$ miesc SQL 类似 lock table
MRG$ Merge Statement
SEL$ SELECT statement
SET$ Set operators
UPD$ Update statement

STARTS 实际执行行源的次数

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bv3a5xts1cuh2',0,'ALLSTATS +PEEKED_BINDS +COST -PREDICATE'));

SQL_ID  0v8h8ka3hr0bp, child number 0
-------------------------------------
select /*+gather_plan_statistics*/  ENAME from emp e, (select * from 
dept where deptno=20) d where e.deptno=d.deptno and d.loc= 'DALLAS'
 
Plan hash value: 568005898
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |     4 (100)|      5 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |         |      1 |      5 |     4   (0)|      5 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|   3 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS FULL          | EMP     |      1 |      5 |     3   (0)|      5 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)

remote的信息展示

PARALLEL信息展示

 SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1ms60dn34wyy0',0,'ADVANCED'));

SQL_ID  1ms60dn34wyy0, child number 0
-------------------------------------
select /*+PARALLEL(4)*/ ENAME from emp e, DEPT  d where 
e.deptno=d.deptno
 
Plan hash value: 2873591275
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     2 (100)|          |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   280 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   280 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| EMP      |    14 |   280 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F7859CDE
   4 - SEL$F7859CDE / E@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      SHARED(4)
      OUTLINE_LEAF(@"SEL$F7859CDE")
      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("E"."DEPTNO" IS NOT NULL)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "ENAME"[VARCHAR2,10]
   2 - (#keys=0) "ENAME"[VARCHAR2,10]
   3 - "ENAME"[VARCHAR2,10]
   4 - "ENAME"[VARCHAR2,10]
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 4 because of hint

note信息

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Degree of Parallelism is 4 because of hint


Note
-----
 - SQL profile “SQL_PROF_896fbd4yjghj8" used for this
statement

11g出现的基数评估
Note
-----
 - Cardinality feedback used

输出指标的全部说明

指标参数 说明
通用内容  
ID 执行计划中每个步骤的标识符，如果前缀*，在谓词部分有谓词信息
operation 行源的操作方式
name 对象名称：表、索引、视图等
CB0 解析时间信息  
rows 通过行源操作优化器评估的行返回的数量，来源操作对象的统计信息
Bytes 优化器评估的返回的数据量
tempspc 优化器评估临时空间的使用率
Cost（%CPU） 优化器行源操作的代价，CPU百分比代价给于（）中独立的行源操作
time 评估需要执行这个操作的时间量，单位HH:MI： SS
分区  
Pstart 第一个分区数目被访问
Pstop 最后一个分区数目被访问
并行处理  
TQ 表队列使用处理
IN-OUT 并行操作的关联
PQ Distrib 由服务器端使用分配并发送数据给客户端
真正运行统计信息  
Starts 多少次数数目操作被执行（核对更多嵌套循环操作）
A-ROWS 行返回的真实数目
A-TIMES 真实的时间量花费在执行这个操作上
I/O统计信息  
buffers 当每个步骤执行，逻辑读取操作执行
read 物理读取执行每个操作步骤
write 物理写入执行
内存使用统计  
 OMem 最佳执行计划评估需要的内存（字节） 
 1Mem 1次通过执行评估需要的内存（字节） 
 0/1/M  通过最佳/1次通过/多模式执行的次数
 Used-Mem 当最后一次执行这个操作使用的内存量（字节） 
 Used-Tmp 最后执行这个操作使用的临时空间的量（KB） 
Max-Tmp 这个操作最大的使用临时空间量(KB)

DBMS_XPLAN.DISPLAY_AWR输出awr中的历史执行计划，不在重复说明

限制说明

DISPLAY_AWR不能存储谓词信息

awr中没有存储全部的SQL语句，导致一些语句无法查看到历史的执行计划

DISPLAY_CURor在rac环境下，不能全部显示，只显示连接的节点，需要查看另外节点的话，连接到另一个节点

显示真实时间的统计信息需要SQL语句运行完成（需要长时间运行查询）

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/gull/p/5564276.html