获取sql的执行计划总结！

最新推荐文章于 2024-04-19 10:23:56 发布

原创最新推荐文章于 2024-04-19 10:23:56 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #oracle #table #buffer #cache

execution plan 同时被 2 个专栏收录

27 篇文章

订阅专栏

sql tuning

12 篇文章

订阅专栏

本文介绍了在 Oracle 数据库中获取 SQL 执行计划的多种方法，包括使用 autotrace 功能、dbms_xplan.display 包以及 dbms_xplan.display_cursor 包来查看和分析 SQL 的执行计划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.sql
SQL> grant plustrace to public;

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

使用autotrace功能：

SQL> conn scott/tiger
已连接。
SQL> set autotrace on
SQL> 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 /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 23 18:22:57 2012

Copyright (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$1

Column 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 t2

Plan 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 t2

Plan 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 ：多少次一致性读。