现在的位置: 首页 > 综合 > 正文

（1）生成(抓取)和显示(解释)执行计划

2013年11月28日 ⁄ 综合 ⁄ 共 12171字 ⁄ 字号 小 中 大 ⁄ 评论关闭
文章目录

1、dbms_xplan.display:用于显示Explain Plan 命令解释的计划

DISPLAY 函数用于显示存储在PLAN_TABLE 中的执行计划，或与PLAN_TABLE 拥有相同结构的表中的执行计划。此外，如果从视图v$sql_plan_statistics_all 可以获得该执行计划的相关统计数据，DISPLAY 也可以格式化输出这些数据。

function display(table_name   varchar2      default 'PLAN_TABLE',
                 statement_id varchar2      default null,
                 format       varchar2      default 'TYPICAL',
                 filter_preds varchar2      default null)                

table_name：存储查询计划的表名（不区分大小写），默认值为PLAN_TABLE。
statement_id：SQL 语句ID。在PLAN_TABLE 中，每条语句的执行计划都会有一个唯一的ID 来标识。这个ID 可以在执行Explain Plan 命令时，通过Set Statement_id 子句来指定。如果输入为NULL，则会获取最近一条被解释的语句。
format：输出格式。在DISPLAY 函数中，有以下预定义的格式（模板）可供选择：
                     'BASIC' ：基本格式。输出的内容最少。
                     'TYPICAL' ：典型格式。这种格式是默认格式。
                     'SERIAL' ：串行执行格式。这种格式和典型格式的输出内容基本一致，不同之处在于，对并行查询，它不会输出相关的并行内容。
                     'ALL' ：完全格式。输出的内容相对完整。
filter_preds：该参数接收合法的谓词过滤条件（可以是谓词逻辑表达式，也可以包含子查询），以过滤从查询计划表中读取的内容。例如，可以输入“COST > 10”以限制输出所有估算代价大于10 的操作。

示例代码：

SQL> explain plan for select * from emp where emp_id=:A;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3169874980

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0021331 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("EMP_ID"=TO_NUMBER(:A))

已选择14行。

2、dbms_xplan.display_cursor:显示内存中的执行计划

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

  function display_cursor(sql_id           varchar2 default  null,
                          cursor_child_no  integer  default  0,
                          format           varchar2 default  'TYPICAL')

sql_id ： 所显示执行计划的SQL 语句的ID。该ID 可以从V$SQL.SQL_ID、V$SESSION.SQL_ID 或者V$SESSION.PREV_SQL_ID 获得。如果没有指定SQL_ID（指定NULL），则默认会显示当前会话中最后一条执行的SQL 语句。
cursor_child_no ：语句的子游标序号。我们知道，受到执行环境的影响，一条SQL 语句可能会产生多个版本的子游标，每个子游标都会与一个执行计划相映射（多个子游标也可能映射同一个执行计划）。通过CURSOR_CHILD_NO 可以限制仅显示某一个子游标的执行计划。如果不指定该参数，则会显示该语句的所有子游标的执行计划。v$sql.cursor_child_no。
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 函数。
示例代码：

SQL> var A number;
SQL> exec :A:=1;

PL/SQL 过程已成功完成。

SQL> select * from emp where emp_id=:A;

    EMP_ID    DEPT_ID
---------- ----------
         1         10

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7bm4cwrgu7cad, child number 0
-------------------------------------
select * from emp where emp_id=:A

Plan hash value: 3169874980

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    26 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0021331 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("EMP_ID"=:A)


已选择19行。

3、dbms_xplan.display_awr:显示 AWR 历史数据中的计划

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

  function display_awr(sql_id          varchar2,
                       plan_hash_value integer  default null,
                       db_id           integer  default null,
                       format          varchar2 default 'TYPICAL')

SQL_ID ：所显示执行计划的SQL 语句的ID。该ID 可以从DBA_HIST_SQL_PLAN.SQL_ID 或DBA_HIST_SQLTEXT.SQL_ID 获得，该参数必须指定非空值，没有默认值；
PLAN_HASH_VALUE ：执行计划的哈希值。我们之前提到，每个执行计划都有一个哈希值。通过该值，可以显示SQL 语句的特定执行计划。如果该参数未指定或为NULL，则会显示语句的所有执行计划；
DB_ID：指定显示哪个数据库的执行计划，默认为本地数据库ID；
FORMAT：格式化控制字符串。与DISPLAY 的相同选项类似。

示例代码：

SQL> select sql_id,to_char(substr(sql_text,0,4000)) from dba_hist_sqltext where upper(sql_text) like 'SELECT * FROM T1%';

SQL_ID        TO_CHAR(SUBSTR(SQL_TEXT,0,4000))
------------- --------------------------------------------------------------------------------
2z5kqzmsngdpd select * from t1
66dmz76abwg9b select * from t1 where id between 'A00000000000060000' and 'A00000000000070000'
0kzcspy7b947q select * from t1 order by v2

SQL> select * from table(dbms_xplan.display_awr('66dmz76abwg9b'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 66dmz76abwg9b
--------------------
select * from t1 where id between 'A00000000000060000' and
'A00000000000070000'

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1190 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| T1   |    17 |   374 |  1190   (3)| 00:00:15 |
--------------------------------------------------------------------------


已选择14行。

4、dbms_xplan.display_sqlset:显示 SQL 优化集中语句的计划

DISPLAY_SQLSET 函数显示存储在一个SQL 调优集中的语句的执行计划。

  function display_sqlset(sqlset_name     varchar2,
                          sql_id          varchar2,
                          plan_hash_value integer  default null,
                          format          varchar2 default 'TYPICAL',
                          sqlset_owner    varchar2 default null)

SQLSET_NAME ：SQL 集的名称。每个SQL 集都有一个单独的名称（可以是创建时用户指定的，也可以是系统自动生成的），我们需要指定从哪个SQL 集中读取和显示语句的执行计划，该参数没有默认值，必须指定；
SQL_ID ： 所显示执行计划的SQL 语句的ID。该ID 可以从USER/DBA/ALL_SQLSET_PLANS.SQL_ID 获得，该参数必须指定非空值，没有默认值；
PLAN_HASH_VALUE ：执行计划的哈希值。如果未指定或为NULL，则会显示语句的所有执行计划；
FORMAT：格式化控制字符串。与DISPLAY 的FORMAT 选项相同；
SQLSET_OWNER：SQL 集的所有者，默认为当前用户名。
示例代码： 

SQL> declare
  2    ss_name varchar2(30);
  3  begin
  4    ss_name := dbms_sqltune.create_sqlset();
  5    dbms_sqltune.capture_cursor_cache_sqlset(ss_name, 300, 100);
  6    dbms_output.put_line(ss_name);
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select sqlset_name,sql_id,to_char(sql_text) from DBA_SQLSET_STATEMENTS where upper(sql_text) like 'SELECT * FROM T%';

SQLSET_NAME                    SQL_ID        TO_CHAR(SQL_TEXT)
------------------------------ ------------- --------------------------------------------------------------------------------
STS_1                          3wrm8zvanqxpn select * from table(dbms_xplan.display_sqlset('STS_2','d76shb2rbmmsn'))

SQL> select * from table(dbms_xplan.display_sqlset('STS_1','3wrm8zvanqxpn'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: STS_1
SQL Tuning Set Owner: U1
SQL_ID: 3wrm8zvanqxpn
SQL Text: select * from table(dbms_xplan.display_sqlset('STS_2','d76shb2rbmmsn'))
--------------------------------------------------------------------------------

Plan hash value: 3285807062

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |       |       |    19 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_SQLSET |  8168 | 16336 |    19   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------


已选择15行。

5、dbms_xplan.display_sql_plan_baseline:显示执行计划基线

DISPLAY_SQL_PLAN_BASELINE 函数显示存储在数据字典当中SQL 执行计划基线的计划。

  function display_sql_plan_baseline(sql_handle    varchar2 default null,
                                     plan_name     varchar2 default null,
                                     format        varchar2 default 'TYPICAL')

SQL_HANDLE：执行计划基线所属SQL 的句柄名称，它由Oracle 在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines 查询，默认为NULL；
PLAN_NAME ：执行计划基线中某个执行计划的名称，它由Oracle 在创建或载入执行计划到基线当中时自动生成，可以通过视图dba_sql_plan_baselines 查询，默认为NULL；
FORMAT ：格式化控制字符串。
示例代码： 

SQL>select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle =>'SYS_SQL_99cc41808e350a83'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_99cc41808e350a83
SQL text: select count(last_ddl_time) from t_objects where object_id > :"SYS_B_0"
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9mm21h273a2n319ac7bf0 Plan id: 430734320
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1202886560
---------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |              |    1 |    13 |       2 (0)| 00:00:01 |
|  1 |  SORT AGGREGATE              |              |    1 |    13 |            |          |
|  2 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS    | 3606 | 46878 |       2 (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | T_OBJECTS_PK |  649 |       |       1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">TO_NUMBER(:SYS_B_0))
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9mm21h273a2n384ce3094 Plan id: 2228105364
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 230482234
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |           |    1 |    13 |     296 (3)| 00:00:03 |
|  1 |  SORT AGGREGATE    |           |    1 |    13 |            |          |
|* 2 |   TABLE ACCESS FULL| T_OBJECTS | 3606 | 46878 |     296 (3)| 00:00:03 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">TO_NUMBER(:SYS_B_0))

6、autotrace

autotrace功能的启用：

[sql]
view plaincopyprint?
  1. SQL> @?/rdbms/admin/utlxplan.sql                                       --创建一个plan_table表，10g中已经有一个plan_table$表了。  
  2. SQL> create public synonym plan_table for plan_table;      --10g中已经创建了plan_table$表的一个名为plan_table的同义词了。  
  3. SQL> grant all on plan_table to public;  
  4. SQL> @?/sqlplus/admin/plustrce.sql  
  5. SQL> grant plustrace to public;  

执行完以上操作，所有的数据库用户都拥有了使用autotrace的功能的权限。在10g以上版本中已经默认做了前面两步，可以不用执行了。

示例代码：

SQL> set autotrace traceonly;
SQL> SELECT * FROM EMP WHERE EMP_ID=1;


执行计划
----------------------------------------------------------
Plan hash value: 3169874980

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0021331 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("EMP_ID"=1)


统计信息
----------------------------------------------------------
        403  recursive calls
          0  db block gets
         76  consistent gets
          0  physical reads
          0  redo size
        393  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

7、总结：

可以看见autotrace不但可以看见执行计划，还可以看见统计信息。由于执行计划和执行的性能数据都是进行SQL 调优时的重要参考信息，因此AUTOTRACE 是进行SQL 语句性能调优的一个非常实用的辅助方法。但是缺点是autotrace会实实在在的运行SQL语句。

细心点，从上面就可以看出Explain Plan for 中可以解释包含绑定变量的SQL语句，autotrace却不能。所以Explain Plan for 不会实实在在的运行SQL语句。适合于查看需要运行很久SQL的执行计划。

如果sql已经运行过，那么可以从内存中、或者AWR历史数据中抓取出它的执行计划，这样看见的执行计划最准确。

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞