Explain For理论执行计划相关

转自 http://otn.itpub.net/12974804/viewspace-1062268/

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.

posted @ 2014-02-03 02:45 princessd8251 阅读(250) 评论(0) 编辑收藏举报

刷新页面返回顶部