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>