你的位置:首页 > 数据库

[数据库]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 levelaliasbytescostnotepredicateparallelpartitionprojectionoutlinespeeked_bindsremoterows
BASIC            
TYPICAL YYYYYY   YY
SERIAL YYYY      Y
ALLYYYYYYYYY YY
ADVANCEDYYYYYYYYYYYY

 


执行计划的组成部分

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

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

SELECT SQL_ID, CHILD_NUMBERFROM V$SQLWHERE 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 hintNote----- - SQL profile “SQL_PROF_896fbd4yjghj8" used for thisstatement11g出现的基数评估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物理写入执行

 


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

 


限制说明

DISPLAY_AWR不能存储谓词信息

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

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

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