生成執行計劃的方法

生成執行計劃的方法：

######################################################################
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