获取sql的执行计划总结！

来源：互联网发布：淘宝哪里进货编辑：程序博客网时间：2024/05/26 02:51

1、使用autotrace获取执行计划、解释计划

要查看oracle sql 的执行计划有很多种方法：查看执行计划表、使用oracle第三方工具、使用sql*plus、利用sql trace跟踪文件、诊断事件10046等。这里介绍使用sql*plus的autotrace功能来查看sql的执行计划。

autotrace功能的启用：

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

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

使用autotrace功能：

SQL> conn scott/tiger已连接。SQL> set autotrace onSQL> select * from dept;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON执行计划----------------------------------------------------------Plan hash value: 3383998547--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     4 |   120 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          0  recursive calls--递归调用SQL的个数，在执行这个SQL的时候，有时候会生成很多额外的SQL语句，这个就成为递归调用。          0  db block gets--从buffer cache中读取的block的数量(通过update/delete/select for update读的次数)。          8  consistent gets--从buffer cache中读取的undo数据的block的数量(通过不带for update的select 读的次数)。          0  physical reads--物理读，执行SQL的过程中，从硬盘上读取的数据块个数。          0  redo size--重做数，执行SQL的过程中，产生的重做日志的大小。        641  bytes sent via SQL*Net to client--通过SQL*Net发送给客户端的字节数。        400  bytes received via SQL*Net from client--通过SQL*Net接受客户端的字节数。          2  SQL*Net roundtrips to/from client--网络往返次数。（在sql*plus 中可以受arraysize参数影响。）          0  sorts (memory)--在内存中发生的排序，通过参数sort_area_size控制。          0  sorts (disk)--不能在内存中发生的排序，需要硬盘来协助（使用临时表空间排序）。          4  rows processed--结果的记录数。

上面的方法看见的执行计划是最详细，最全面，最准确的。但是缺点就是必须真正的执行一次sql语句，如果实际中sql需要执行很长时间呐！autotrace也可以不执行sql，而获取解释计划。

2、使用dbms_xplan.display包获取解释计划

[oracle@linux ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 23 18:22:57 2012Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn u1/u1;已连接。SQL> explain plan for select * from t where a='a';已解释。SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    12 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T    |     1 |    12 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("A"='a')已选择13行。

通过explain plan for 获得sql语句的执行计划，最大的优点是不用直接运行sql语句，避免了由于返回结果时间过长过多带来的等待。使用下面的方法，貌似可以得到更多的信息：

SQL> explain plan for select * from t;已解释。SQL> select * from table(dbms_xplan.display(format=>'ALL'));    --在这里加一个ALL    PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      | 71050 |  6730K|   176   (1)| 00:00:03 ||   1 |  TABLE ACCESS FULL| T    | 71050 |  6730K|   176   (1)| 00:00:03 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - SEL$1 / T@SEL$1Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "T"."OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,30],       "T"."SUBOBJECT_NAME"[VARCHAR2,30], "T"."OBJECT_ID"[NUMBER,22],       "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],       "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],       "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------       "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],       "T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],       "T"."EDITION_NAME"[VARCHAR2,30]已选择25行。

3、使用dbms_xplan.display_cursor包抓取共享池中sql的执行计划

  -- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)  function display_cursor(sql_id           varchar2 default  null,                          cursor_child_no  integer  default  0,                          format           varchar2 default  'TYPICAL')  return dbms_xplan_type_table  pipelined;

sql_id和cursor_child_no的值来至于select sql_id,child_number from v$sql;这样就可以抓出共享池中执行过的sql语句的执行计划。

SQL> set serveroutput off;  --这里一定要关闭。SQL> select /*+ gather_plan_statistics */ * from t2; OBJECT_ID OBJECT_NAME---------- ------------------------------        20 ICOL$        46 I_USER1        28 CON$        15 UNDO$        29 C_COBJ#         3 I_OBJ#        25 PROXY_ROLE_DATA$        41 I_IND1        54 I_CDEF2        40 I_OBJ5已选择10行。SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  3xa294hhgupzt, child number 0-------------------------------------select /*+ gather_plan_statistics */ * from t2Plan hash value: 1513984157------------------------------------------------------------------------------------| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |       8 ||   1 |  TABLE ACCESS FULL| T2   |      1 |    160 |     10 |00:00:00.01 |       8 |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------已选择13行。

/*+ GATHER_PLAN_STATISTICS */ 在执行的时候抓取行数据源执行统计信息。行数据源的执行统计信息包括行数、一致性读取次数、物理读取次数、物理写入次数，以及每一个运算在一行数据上的运行时间。

SQL_ID和COUSOR_CHILD_NO参数使用空值表明了需要取上一个执行语句的执行计划。下面使用了一个脚本让这人过程变得简单一点：

[oracle@linux plan]$ cat xplan.sql SELECT xplan.*  FROM (select max(sql_id) keep(dense_rank last order by last_active_time) sql_id,               max(child_number) keep(dense_rank last order by last_active_time) child_number          from v$sql         where upper(sql_text) like '%&1%'           and upper(sql_text) not like               '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %') sqlinfo,       table(DBMS_XPLAN.DISPLAY_CURSOR(sqlinfo.sql_id,                                       sqlinfo.child_number,                                       'ALLSTATS LAST')) xplan/

SQL> set serveroutput off;SQL> select /* ZSH1 */ /*+ gather_plan_statistics */ * from t2; OBJECT_ID OBJECT_NAME---------- ------------------------------        20 ICOL$        46 I_USER1        28 CON$        15 UNDO$        29 C_COBJ#         3 I_OBJ#        25 PROXY_ROLE_DATA$        41 I_IND1        54 I_CDEF2        40 I_OBJ5已选择10行。SQL> @xplan.sql ZSH1原值    5:          where upper(sql_text) like '%&1%'新值    5:          where upper(sql_text) like '%ZSH1%'PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  gvu46pjq707rt, child number 0-------------------------------------select /* ZSH1 */ /*+ gather_plan_statistics */ * from t2Plan hash value: 1513984157------------------------------------------------------------------------------------| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |       8 ||   1 |  TABLE ACCESS FULL| T2   |      1 |    160 |     10 |00:00:00.01 |       8 |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------已选择13行。

这个功能是10G/11G里面才增强的功能。

Starts ：每一步骤执行次数。
E-Rows ：每一个运算估计能够返回多少行
A-Rows ：每一个运算实际能够返回多少行。
A-Time ：每一个运算实际执行时间。
Buffers ：多少次一致性读。