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语句运行完成（需要长时间运行查询）
