ITPub博客

首页 > 数据库 > Oracle > Explain For理论执行计划相关

Explain For理论执行计划相关

原创 Oracle 作者:eric_zhyd 时间:2013-12-04 23:47:32 0 删除 编辑
 
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-03-13

  • 博文量
    21
  • 访问量
    519485