oracle如何产生sql 执行计划(二)

oracle如何产生sql 执行计划(二)

    今天是2013-09-23,之前写过一篇笔记是《oracle如何产生sql执行计划(一)》,那么除了这一种方法以外其实还有两种(据我所知)。

今天就看看另外另种:

一、explain plan sql

该语句可以对sql直接执行计划,执行的计划存储在表plan_table这张表中,但这太繁琐了,每个表的字段需要自己定义。

我习惯使用dbms_xplan这个软件包,

看一下这个包的介绍:

The DBMS_XPLAN package provides an easy way to display the output of theEXPLAIN PLAN command in several, predefined formats. You can also use theDBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views. Finally, it displays plans from a SQL plan baseline.

然后看一下这个包的函数有哪些:

  • DISPLAY - to format and display the contents of a plan table.

  • DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

  • DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

  • DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle

  • DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

  •  

    我常用的两个如下。

    1)displan :格式化然后显示执行计划

    eg:

    SQL> explain plan for     
      2  select * from amy_emp; 
    
    Explained.
    
    SQL> 
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 320686012
    
    -----------------------------------------------------------------------------
    | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |         |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| AMY_EMP |    14 |   532 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    8 rows selected.
    
    SQL> 

    2)、DISPLAY_CURSOR:这个函数显示了在加载到内存中的所有cursor执行计划,除此之外还有一些统计信息如(i/0,memory,timing):

    该函数定义:

    DBMS_XPLAN.DISPLAY_CURSOR(
       sql_id           IN  VARCHAR2  DEFAULT  NULL,
       cursor_child_no  IN  NUMBER    DEFAULT  0, 
       format           IN  VARCHAR2  DEFAULT  'TYPICAL');

    eg:

    参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm#CACFJGHG

    我可以在执行完一条语句之后直接使用该函数:

    QL> select * from amY_dept where deptno>20;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  8rpwwabhbasvn, child number 0
    -------------------------------------
    select * from amY_dept where deptno>20
    
    Plan hash value: 3336059312
    
    ------------------------------------------------------------------------------
    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |          |       |       |     4 (100)|          |
    |*  1 |  TABLE ACCESS FULL| AMY_DEPT |     3 |    60 |     4   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("DEPTNO">20)
    
    
    18 rows selected.
    
    SQL> 



     

     

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值