Explain For理论执行计划相关

 

DBMS_XPLAN完整结构

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

ZHONG@ zhongpdb   SQL > desc  dbms_xplan
FUNCTION  BUILD_PLAN_XML   RETURNS  XMLTYPE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
  TABLE_NAME                     VARCHAR2                 IN      DEFAULT
 STATEMENT_ID                   VARCHAR2                 IN      DEFAULT
 PLAN_ID                         NUMBER                   IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT
 FILTER_PREDS                   VARCHAR2                 IN      DEFAULT
 PLAN_TAG                       VARCHAR2                 IN      DEFAULT
 REPORT_REF                     VARCHAR2                 IN      DEFAULT
FUNCTION  DIFF_PLAN   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_TEXT                        CLOB                     IN
 OUTLINE                         CLOB                     IN
 USER_NAME                      VARCHAR2                 IN      DEFAULT
FUNCTION  DIFF_PLAN_AWR   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_ID                         VARCHAR2                 IN
 PLAN_HASH_VALUE1                NUMBER                   IN
 PLAN_HASH_VALUE2                NUMBER                   IN
FUNCTION  DIFF_PLAN_CURSOR   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_ID                         VARCHAR2                 IN
 CURSOR_CHILD_NUM1               NUMBER                   IN
 CURSOR_CHILD_NUM2               NUMBER                   IN
FUNCTION  DIFF_PLAN_OUTLINE   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_TEXT                        CLOB                     IN
 OUTLINE1                        CLOB                     IN
 OUTLINE2                        CLOB                     IN
 USER_NAME                      VARCHAR2                 IN      DEFAULT
FUNCTION  DIFF_PLAN_SQL_BASELINE   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 BASELINE_PLAN_NAME1            VARCHAR2                 IN
 BASELINE_PLAN_NAME2            VARCHAR2                 IN
FUNCTION  DISPLAY   RETURNS  DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
  TABLE_NAME                     VARCHAR2                 IN      DEFAULT
 STATEMENT_ID                   VARCHAR2                 IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT
 FILTER_PREDS                   VARCHAR2                 IN      DEFAULT
FUNCTION  DISPLAY_AWR   RETURNS  DBMS_XPLAN_TYPE_TABLE

DISPLAY_AWR 函数显示存储在AWR 历史数据的执行计划。

提示：要正常调用DISPLAY_AWR 参数，必须对以下视图有权限：DBA_HIST_SQL_ PLAN 和DBA_HIST_SQLTEXT 的SELECT。
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_ID                         VARCHAR2                 IN

 提示：该ID 可以从DBA_HIST_SQL_PLAN.
 PLAN_HASH_VALUE                 NUMBER( 38)               IN      DEFAULT

 提示：通过该值，可以显示SQL 语句的特定执行计划。如果该参数未指定或为 NULL，则会显示语句的所有执行计划；
 DB_ID                           NUMBER( 38)               IN      DEFAULT

 提示：我们可以将其他数据库的AWR 数据导入本地数据库进行分析。
 FORMAT                         VARCHAR2                 IN      DEFAULT

1.根据模糊搜索方式获得历史SQL_TEXT对应的SQL_ID
select sql_id, to_char(substr(sql_text,0,4000)) from dba_hist_sqltext where upper(sql_text) like 'SELECT COUNT(*) FROM %';

SQL_ID
-   -   -   -   -   -   -   -   -   -   -   -   -
TO_CHAR(SUBSTR(SQL_TEXT,   0  ,   4000  ))
-   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   - -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -  
007grn40hdw0v
select   COUNT  (   *  )   from  t1_skew   where  id   =  :num

2.通过DISPLAY_AWR获得历史执行计划
select * from table(dbms_xplan.display_awr(' 007grn40hdw0v'));

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -SQL_ID   007grn40hdw0v
- - - - - - - - - - - - - - - - - - - -
select   COUNT( *)   from  t1_skew   where  id   =  :num
 
Plan hash   value:   2900991624
 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |   Operation             |   Name      |   Rows  | Bytes | Cost (%CPU)|   Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |   SELECT   STATEMENT      |           |       |       |     29  ( 100)|          |
|    1  |  SORT   AGGREGATE       |           |      1  |      3  |            |          |
|    2  |    INDEX  FAST   FULL  SCAN| IDX_T1_ID |   25158  |   75474  |     29   ( 0)| 00:00: 01  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
 
14   rows  selected.

FUNCTION  DISPLAY_CURSOR   RETURNS  DBMS_XPLAN_TYPE_TABLE

函数可以显示内存中一个或者多个游标的执行计划。

用户必须对视图V$SQLV、$SQL_PLAN 和V$SQL_PLAN_STATISTICS_ALL 的SELECT有权限，才能正常调用DISPLAY_CURSOR 函数。

参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_ID                         VARCHAR2                 IN      DEFAULT
 CURSOR_CHILD_NO                 NUMBER( 38)               IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT

FORMAT ：格式化控制字符串。DISPLAY 函数的格式化控制字符串的所有选项都适用于DISPLAY_CURSOR 函数。由于运行语句还可以通过提示GATHER_PLAN_STATISTICS 或设置系统参数STATISTICS_LEVEL 为“ALL”收集语句运行的性能统计数据，因此在细化选项中还有额外的选项，以选择是否输出这些数据。

? IOSTATS：是否输出计划的输入输出（IO）统计数据；

? MEMSTATS ：在启用了PGA 自动管理（参数pga_aggregate_target 的值大于0） 的情况下，是否输出计划的输入内存统计数据（操作的内存使用量、内存读次数 等）；

? ALLSTATS：包含了IOSTATS 和MEMSTATS 的全部内容；

? LAST ：以上三个选项输出的统计数据都是实际产生的数据，而非估算数据，它们 是该游标所有执行所产生的数据的总和。你可以增加LAST 选项以限定仅显示最 后一次运行的统计数据。

此外，还有一些未公布的选项可用于该函数的输出控制。首先是预定义格式：

? 'ADVANCED' ：高级格式。高级格式除了会输出完全格式中的所有内容外，还会视 情况输出绑定变量窥视信息和计划概要（Outline）信息；

? OUTLINE：是否以提示（HINT）的方式显示计划概要；

? PEEKED_BINDS：是否显示绑定变量窥视信息；

? BUFFSTATS ：是否显示内存读次数（包括一致性读和当前读次数），该信息为 IOSTATS 的一部分；

? PLAN_HASH：是否显示计划的哈希值，该选项同样适用于DISPLAY 函数。

 

直接查询

SELECT plan_table_output FROM

table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

间接查询

SELECT plan_table_output

FROM v$sql s, TABLE(dbms_xplan.display_cursor(s.sql_id,s.child_number,

' ADVANCED')) t

WHERE s.sql_text like 'select * from%';

FUNCTION  DISPLAY_PLAN   RETURNS   CLOB
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
  TABLE_NAME                     VARCHAR2                 IN      DEFAULT
 STATEMENT_ID                   VARCHAR2                 IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT
 FILTER_PREDS                   VARCHAR2                 IN      DEFAULT
  TYPE                           VARCHAR2                 IN      DEFAULT
FUNCTION  DISPLAY_SQLSET   RETURNS  DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQLSET_NAME                    VARCHAR2                 IN
 SQL_ID                         VARCHAR2                 IN
 PLAN_HASH_VALUE                 NUMBER( 38)               IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT
 SQLSET_OWNER                   VARCHAR2                 IN      DEFAULT
FUNCTION  DISPLAY_SQL_PLAN_BASELINE   RETURNS  DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 SQL_HANDLE                     VARCHAR2                 IN      DEFAULT
 PLAN_NAME                      VARCHAR2                 IN      DEFAULT
 FORMAT                         VARCHAR2                 IN      DEFAULT
FUNCTION  FORMAT_NUMBER   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 NUM                             NUMBER                   IN
FUNCTION  FORMAT_NUMBER2   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 NUM                             NUMBER                   IN
FUNCTION  FORMAT_SIZE   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 NUM                             NUMBER                   IN
FUNCTION  FORMAT_SIZE2   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 NUM                             NUMBER                   IN
FUNCTION  FORMAT_TIME_S   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 NUM                             NUMBER                   IN
FUNCTION  GET_PLANDIFF_REPORT_XML   RETURNS  XMLTYPE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 REPORT_REF                     VARCHAR2                 IN      DEFAULT
 TID                             NUMBER                   IN
  METHOD                         VARCHAR2                 IN
FUNCTION  PREPARE_PLAN_XML_QUERY   RETURNS  VARCHAR2
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 PLAN_QUERY                     VARCHAR2                 IN
FUNCTION  PREPARE_RECORDS   RETURNS  DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 PLAN_CUR                        REF   CURSOR               IN
 I_FORMAT_FLAGS                 BINARY_INTEGER           IN
FUNCTION  VALIDATE_FORMAT   RETURNS   BOOLEAN
参数名称                       类型                    输入 /输出默认值?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - -   - - - - - -   - - - - - - - -
 HASPLANSTATS                    BOOLEAN                  IN
 FORMAT                         VARCHAR2                 IN
 FORMAT_FLAGS                   BINARY_INTEGER           OUT

 

DBMS_XPLAN额外信息

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

- 查询块和对象别名
在使用DBMS_XPLAN 显示执行计划时，选择'ADVANCED' 预定义格式作为参数或者 加入'ALIAS' 控制字符串，可以在输出中看到以下内容:

Query Block   Name   /   Object   Alias  (identified   by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1   -  SEL$ 1
2   -  SEL$ 1   /  T@SEL$ 1
3   -  SEL$ 1   /   O@SEL$1

其中，数字为对应的操作ID，SEL$1 为查询块（Query Block）的名字，T@SEL$1 和 O@SEL$1 为查询块中对象的别名（Alias）。

语句在被提交到Oracle 后，解析器（Parser）会对SQL 语句的语法、语义进行分析，并 将查询中的视图展开、划分为小的查询块（Query Block）。这些查询块被传输给优化器后， 其查询转换器（Query Transformer）会对它们进行进一步地查询转换，使优化器能生成效率 更高的执行计划。

 

- 计划概要数据

在使用DBMS_XPLAN 显示执行计划时，选择'ADVANCED' 预定义格式作为参数或者 加入'OUTLINE' 控制字符串，可以在输出中看到以下内容：

Outline   Data
- - - - - - - - - - - - -
/ * +
BEGIN_OUTLINE_DATA
FULL(@ "SEL$1"   "T_USERS"@ "SEL$1")
OUTLINE_LEAF(@ "SEL$1")
OPT_PARAM( 'optimizer_index_cost_adj'   60)
OPTIMIZER_FEATURES_ENABLE( '10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
* /

这是由一组提示（HINT）组成的数据，即执行计划的概要（Outline）数据。计划概要可 以确保语句解析出一个特定的执行计划，换句话说，它能确保语句执行计划的稳定性。在9i 当中，引入了一个特性：存储概要（Stored Outline），作为一个保持语句执行计划稳定的手段， 它可以在不中止相关程序、不修改源代码的情况下，影响优化器解析执行计划时的行为。

 

要注意的是，执行计划中的概要数据是在优化器选择了最终的执行计划后，根据该计划 产生的，用于重现该执行计划的必要的概要数据。

提示：SQL“提示”（HINT）是内嵌在SQL 语句中，由/*+ Hint_Words */ 构成的一段注释。它不会改变语句的逻辑结果，但可以强制优化器在选择执行计划的过程中，使用特定值作为优化参数，或者选择某些特定的操作作为执行计划的一部分。

 

在11g 中，执行计划管理器（SQL Plan Management）的引入，能使SQL 语句获得更加 稳定的性能，官方不再推荐使用存储概要。

DBMS_XPLAN 中显示的概要数据对于我们来说具有相当重要的作用：

1）它可以在不对语句做OPTIMIZER_TRACE 的情况下，让我们了解优化器生成该执行 计划的基本环境；

2）利用概要数据，我们可以在其他环境中重现一条语句的执行计划，以帮助我们做问 题分析（Troubleshooting）和语句调优。

 

提示：概要数据是由一组SQL 提示构成，在11g 中，Oracle 提供了一个视图V$SQL_ HINT，可用于查询各个版本可用的SQL 提示。其中，字段VERSION_OUTLINE 表示SQL 提示是否可用于计划概要，并且是从哪个版本开始可以被用于计划概要。

 

-  绑定变量信息

对于使用绑定变量， 并且在解析计划时启用了绑定变量窥视特性的语句， 在使 用DBMS_XPLAN 显示执行计划时， 选择'ADVANCED' 预定义格式作为参数或者加 入'PEEKED_BINDS' 控制字符串，可以在输出中看到以下内容：

Peeked Binds (identified   by   position):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1   -  :A (VARCHAR2( 30), CSID = 871):   'S'

其中，数字1 为关联的操作ID，:A 为绑定变量名（括号中为变量数据类型，对于字符 类型，还有其字符集的ID 号），最后为解析计划时，该变量所窥视到的数值。

注意：9i和10g中，绑定变量如果存在严重的分布不均，系统将会出现执行计划错误的情况。11g中，可以通过adaptive Cursor Sharing自动适应共享游标方式以解决这一问题：它会比较绑定变量不同数值的执行计划的效率，相应的选择最优的执行计划。

- 分布式查询语句信息

分布式查询中，会涉及对远程数据库上对象的查询。该部分信息则是将执行计划中涉及

远程对象查询的语句显示出来，语句是与执行计划中的操作相关联的。示例如下：

Remote   SQL  Information (identified   by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
3   -   SELECT   "USERNAME", "USER_ID", "PASSWORD", "ACCOUNT_STATUS", "LOCK_DATE", "EXPIRY_DATE
", "DEFAULT_TABLESPACE", "TEMPORARY_TABLESPACE", "CREATED", "PROFILE", "INITIAL_RSRC_CONSUME
R_GROUP", "EXTERNAL_NAME"   FROM   "T_USERS"   "T_USERS"  (accessing   'ORA10201'  )

- 注释

注释（Note）部分显示了在输出执行计划时所探测到的问题以及相关建议。例如，以下

注释内容告诉我们，该执行计划使用了RBO 作为优化器，建议我们使用CBO：

Note
- - - - -
-   rule  based optimizer used (consider   using  cbo)

Explain Plan事前调查

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--创建执行计划使用的表

@?\rdbms\admin\utlxplan

 

Explain Plan重要的脚本

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

A、utlxplan.sql(Utilility explain PLAN table) 创建表plan_table，用于存储执行计划  
B、utlxplp.sql(Utilility explain PLAN Parallel) 显示计划表内容，包括并行查询计划的信息细节  
C、utlxpls.sql(Utilitity explain PLAN Serial) 显示普通、串行计划的计划表内容

执行Explain Plan语句

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

explain  PLAN [   SET  STATEMENT_ID [ =]   <  string literal   >  ] [   INTO   <   table_name   >  ]   FOR   <  sql_statement   >

其中：
STATEMENT_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。
TABLE_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。
SQL_STATEMENT：是真正的SQL语句。

-  常见的简便模式
explain plan for select name from v$datafile;

- 高级模式

explain plan set statement_id='ZHONG' for select name from v$datafile;

 

注1：每次存储计划时，需要将计划表中原有的内容删除

delete from plan_table

 

DBMS_XPLAN模拟执行计划计算

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
FROM PLAN_TABLE  a
WHERE STATEMENT_ID='ERIC'
ORDER BY Id;

- 常用无参模式

select * from table(dbms_xplan.display);

- 高级模式

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'ZHONG', 'ALL') );

注1：高级模式可以查看Column Projection Information可以方便地看到每一步的操作与变量类型

ZHONG@ zhongpdb   SQL > SELECT  plan_table_output   FROM    TABLE(DBMS_XPLAN.DISPLAY( 'PLAN_TABLE',   'ZHONG',   'ALL') );

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash   value:   2966233522

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |   Operation          |   Name  |   Rows  | Bytes | Cost (%CPU)|   Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |   SELECT   STATEMENT   |      |      1  |   2015  |   19382   ( 1)| 00: 03: 53  |
|    1  |  SORT   AGGREGATE    |      |      1  |   2015  |            |          |
| *   2  |    TABLE   ACCESS   FULL| T    |   77093  |    148M|   19382   ( 1)| 00: 03: 53  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Query Block   Name   /   Object   Alias  (identified   by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    1   -  SEL$ 1
    2   -  SEL$ 1   /  T1@SEL$ 1

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

    2   -  filter( "X" = 1)

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

    1   -  (#keys = 0)   COUNT( "DATA")[ 22]
    2   -   "DATA"[VARCHAR2, 4000]

Note
- - - - -
    -   dynamic  sampling used   for  this   statement  ( level = 2)

-- 查询SQL HASH的执行计划

select * from table(dbms_xplan.display_cursor(' 6h2j39hfk0tx1',0,'advanced'));

 

显示执行结果：

@?\rdbms\admin\utlxpls.sql

 

DBMS_XPLAN查看实际执行计划

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
9i 中 DBMS_XPLAN只是提供了“理论”上的的SQL执行计划(通过Explain Plan). 不过，9i提供了几个动态视图来供查询实际的SQL(cursor)执行计划信息，比如v$SQL_PLAN, v$SQL_PLAN_STATISTICS).  虽然如此，DBMS_XPLAN却没有能够提供返回容易阅读的格式化好的实际执行计划信息。

在10g中这个问题得到了解决， DBMS_XPLAN中增加的Function, DISPLAY_CURSOR, 用来返回格式化好(就像DISPLAY一样）的SQL实际执行的信息。DISPLAY_CURSOR主要是依赖于Oracle的几个动态视图， 包括v$SQL, v$SQL_PLAN, v$SQL_PLAN_STATISTICS_ALL等。

注1：注意调用DISPLAY_CURSOR的时候，我们没有传入任何参数，这样的话这个函数会返回当前Session中最后一个执行的SQL(cursor) 的执行计划信息。由于sqlplus默认会调用DBMS_OUTPUT.GET_LINES，影响到DISPLAY_CURSOR返回的结果，因此我们首先 set serveroutput off。

 

# 查询上一条SQL语句的实际执行计划

ZHONG@ zhongpdb   SQL > select   count( data)   from  t t1   where  x   =   1;

COUNT( DATA)
- - - - - - - - - - -
       71005

已用时间:  00: 00:   10. 32
ZHONG@ zhongpdb   SQL > SELECT  plan_table_output   FROM    TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID   1vfjpvrcfbw8t, child   number   1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
select   count( data)   from  t t1   where  x   =   1

Plan hash   value:   2966233522

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |   Operation          |   Name  |   Rows  | Bytes | Cost (%CPU)|   Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |   SELECT   STATEMENT   |      |       |       |   19382  ( 100)|          |
|    1  |  SORT   AGGREGATE    |      |      1  |   2015  |            |          |
| *   2  |    TABLE   ACCESS   FULL| T    |   77093  |    148M|   19382   ( 1)| 00: 03: 53  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

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

    2   -  filter( "X" = 1)

Note
- - - - -
    -   dynamic  sampling used   for  this   statement  ( level = 2)

 

# 动态视图v$sql_plan用于查看过去的执行计

-  授权给相应用户
grant   select   on  v_$sql_plan   to  zhong;
grant   select   on  v_$ sql   to  zhong;
-  创建动态PLAN_TABLE保存执行计划
CREATE   OR  REPLACE   VIEW  dynamic_plan_table
AS 
SELECT 
    RAWTOHEX(address) ||   '_'  || child_number   AS  statement_id
    ,SYSDATE   timestamp
    , operation
    , options
    ,object_node
    ,object_owner
    ,object_name
    , 0  object_instance
    ,optimizer
    ,search_columns
    ,id
    ,parent_id
    , position
    ,cost
    , cardinality
    ,bytes
    ,other_tag
    ,partition_start
    ,partition_stop
    ,partition_id
    ,other
    ,distribution
    ,cpu_cost
    ,io_cost
    ,temp_space
    ,access_predicates
    ,filter_predicates
FROM  v$sql_plan;
- 执行查询脚本  
select  plan_table_output 
from   table(dbms_xplan.display( 'dynamic_plan_table', 
              ( select  rawtohex(address)|| '_'||child_number x 
                  from  v$ sql 
                 where  sql_text = 'xxx'), 
                 'serial')))

 

# 查询已经在Share Pool的SQL语句的实际执行计划

DISPLAY_CURSOR可以用来查询某个具体的SQL CURSOR的实际执行信息， 不过它需要传入一个参数： SQL_ID. (有时候还需要CHILD_CURSOR_NO参数）

 

SQL_ID是Oracle10g新增加的，在很多动态视图里面都存在，用来标示一条SQL语句。

 

需要注意的是，SQL_ID只是跟在shared pool里面的SQL语句的字符串(TEXT)是一一对应的，但并不是于SQL语句对应的Cursor是一一对应的。因为一条SQL语句(TEXT)可能会对应于多个child cursors(不同的执行计划). 正是因为这个原因，DISPLAY_CURSOR还接受另外一个参数—child cursor no (默认值为0， 因为大多数情况下每条SQL语句对应一个cursor)

 

另外， 一个Cusor(SQL_ID,  CHILD_CURSOR)的执行计划也可能随着时间的迁移而变化，这个会在动态视图v$SQL_PLAN中以新的一条PLAN_HASH_VALUE来体现。

 

为了通过DISPLAY_CURSOR得到具体某个SQL（cursor）实际的执行计划，显然我们需要得到该SQL的SQL_ID和CHILD_NUMBER). 可以通过查询动态视图v$SQL来得到SQL_ID。

 

col sql_text format a80

SELECT sql_id, child_number , sql_text FROM v$sql WHERE  LOWER(sql_text) LIKE 'select count(data)%';

SQL_ID        CHILD_NUMBER SQL_TEXT
- - - - - - - - - - - - -   - - - - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
c602jxkbz5r0r             0   select   count( data)   from  t t1   where  x   =   2
1vfjpvrcfbw8t             1   select   count( data)   from  t t1   where  x   =   1
c602jxkbz5r0r             1   select   count( data)   from  t t1   where  x   =   2
1vfjpvrcfbw8t             0   select   count( data)   from  t t1   where  x   =   1

 

SELECT plan_table_output FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1vfjpvrcfbw8t', 0, 'ALL'));

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID   1vfjpvrcfbw8t, child   number   0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
select   count( data)   from  t t1   where  x   =   1

Plan hash   value:   2966233522

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |   Operation          |   Name  |   Rows  | Bytes | Cost (%CPU)|   Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |   SELECT   STATEMENT   |      |       |       |   19382  ( 100)|          |
|    1  |  SORT   AGGREGATE    |      |      1  |   2015  |            |          |
| *   2  |    TABLE   ACCESS   FULL| T    |   77093  |    148M|   19382   ( 1)| 00: 03: 53  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Query Block   Name   /   Object   Alias  (identified   by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    1   -  SEL$ 1
    2   -  SEL$ 1   /  T1@SEL$ 1

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

    2   -  filter( "X" = 1)

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

    1   -  (#keys = 0)   COUNT( "DATA")[ 22]
    2   -   "DATA"[VARCHAR2, 4000]

Note
- - - - -
    -   dynamic  sampling used   for  this   statement  ( level = 2)


已选择 35行。

 

DBMS_XPLAN查询运行时runtime statistics

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

CREATE   TABLE  t1   AS   SELECT   *   FROM  all_objects;

CREATE   TABLE  t2   AS   SELECT   *   FROM  all_objects;

CREATE   TABLE  t3   AS   SELECT   *   FROM  all_objects   WHERE  ROWNUM   < =   100;

CREATE   INDEX  it3   ON  t3 (object_id);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS( user, 'T1');
     DBMS_STATS.GATHER_TABLE_STATS( user, 'T2');
     DBMS_STATS.GATHER_TABLE_STATS( user, 'T3', cascade = > TRUE);
END;
/

ALTER   SESSION   SET  STATISTICS_LEVEL   =   ALL;

SELECT   / * +  ORDERED   * /
         t1.object_type
  ,       COUNT( *)   AS  object_count
   FROM   t1
  ,      t2
  ,      t3
   WHERE  t1.object_id   =  t2.object_id
   AND    t2.object_id   =  t3.object_id
   GROUP   BY  t1.object_type;

OBJECT_TYPE         OBJECT_COUNT
- - - - - - - - - - - - - - - - - - -   - - - - - - - - - - - -
EDITION                         1
SEQUENCE                        1
TABLE                          37
INDEX                          56
CLUSTER                         5

SELECT  plan_table_output   FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'RUNSTATS_LAST'));

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL_ID   7b49gb1jczckb, child   number   0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT   / * +  ORDERED   * /          t1.object_type   ,       COUNT( *)   AS
object_count    FROM   t1   ,      t2   ,      t3    WHERE  t1.object_id
=  t2.object_id    AND    t2.object_id   =  t3.object_id    GROUP   BY
t1.object_type

Plan hash   value:   2827993557

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |   Operation            |   Name  | Starts | E - Rows  | A - Rows  |   A - Time   | Buffers |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |   SELECT   STATEMENT     |      |       1  |        |       5  |00:00:00. 21  |     2035  |
|    1  |  HASH   GROUP   BY       |      |       1  |      39  |       5  |00:00:00. 21  |     2035  |
| *   2  |   HASH   JOIN          |      |       1  |     100  |     100  |00:00:00. 21  |     2035  |
|    3  |     INDEX   FULL  SCAN   | IT3  |       1  |     100  |     100  |00:00:00. 01  |        1  |
| *   4  |    HASH   JOIN         |      |       1  |   71005  |   71005  |00:00:00. 18  |     2034  |
|    5  |      TABLE   ACCESS   FULL| T1   |       1  |   71005  |   71005  |00:00:00. 03  |     1017  |
|    6  |      TABLE   ACCESS   FULL| T2   |       1  |   71006  |   71006  |00:00:00. 02  |     1017  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified   by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2   -   access( "T2". "OBJECT_ID" = "T3". "OBJECT_ID")
    4   -   access( "T1". "OBJECT_ID" = "T2". "OBJECT_ID")

已选择 27行。

Oracle provides six high-level statistics as follows:

    - E-Rows:  estimated rowcounts flowing through each plan step;

    - A-Rows:  the actual rowcounts flowing through each plan step;

    - Buffers:  reads from the buffer cache (LIO);

    - Reads:  physical reads (PIO);

    - Writes:  physical writes (e.g. to temp); and

    - A-Time:  elapsed time of each plan step.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12974804/viewspace-1062268/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12974804/viewspace-1062268/