生成执行计划的方法

生成执行计划的方法：

######################################################################
1 Explain Plan
######################################################################

不会真正执行查询语句．

1.1 建立plan_table

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

1.2 Create the explain plan
analyze table xxx compute statistics;
Explain plan for select last_name from employees

1.3 View Excute Plan

col operation format a20;
col options format a20;
col object_name format a20;
col cost format 99999999;
select *
from (
SELECT LPAD('　',(LEVEL-1))||operation operation, options, object_name, CARDINALITY "return rows", cost,CPU_COST,IO_COST,TEMP_SPACE
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id
order by timestamp desc )
where rownum < 6;

######################################################################
2 Autotrace
######################################################################

Autotrace会真正执行语句．不太适合于有大数据量返回的查询．

2.1 Create the Plan_table table
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

2.2 Create the Plustrace role by executing plustrce.sql.
SQL> @$ORACLE_HOME/rdbms/admin/plustrce

2.3 Set autotrace  on| traceonly|on explain| traceonly statistics

SET AUTOTRACE ON: Produces the result set and the explain plan, and lists statistics.
(真正执行查询，显示查询结果集，显示执行计划，显示统计数据)

SET AUTOTRACE  TRACEONLY: Displays the explain plan and the statistics; you will not see the result set, although the statement is executed.
(真正执行查询,但不显示查询结果集，显示执行计划，显示统计数据)

SET AUTOTRACE  ON EXPLAIN: Displays the result set and the explain-plan results, without the statistics.
(真正执行查询,显示查询结果集，显示执行计划，不显示统计数据)

SET AUTOTRACE  ON STATISTICS: Display the statistics only.
(真正执行查询,显示查询结果集，不显示执行计划，只显示统计数据)

SET AUTOTRACE OFF
关闭。

However, Autotrace does parse and execute the statement, whereas explain-plan only parses the statement.

AUTOTEACE都会真正执行查询，这对于大表的情况是不适用的。
而explain plan只是解释语句不做真正的查询动作。

2.4 Execute Select Statement
select * from test;

可以更改Autotrace的默认 Level,以显示更详尽的信息?
如何更改, 还是依赖于Plan_table 的结构。

 

######################################################################
3 能过第三方工具来查看,如PL/SQL Developer
######################################################################

######################################################################
4
Desc plan_table
######################################################################

SQL> desc plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)

CARDINALITY:The number of rows returned by the current operation (estimated by the CBO)    
 
COST:The cost of the current operation estimated by the cost-based optimizer (CBO)至于单位是什么,不知道?没有具体的单位

######################################################################
5
Explain plan for
######################################################################

set linesize 100
Explain plan for select * from emp;
select * from table(dbms_xplan.display);

5 问题
SQL> analyze table test compute statistics;
analyze table test compute statistics
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Analyze table 时做的什么工作,其工作原理是什么?
会导致临时表空间消耗很大? 是的，做排序
collect exact or estimated statistcis about physical storage characteristics and data distribution in these schema objecs

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-669509/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-669509/