oracle執行計劃與統計資訊的一些總結

chenfengwww發表於2011-01-19
Oracle

來自：http://hi.baidu.com/dba_jungle/blog/item/874751ab58a1ecbccb130cd2.html

1、SET AUTOTRACE ON EXPLAIN
（set autot on exp)
SQLPLUS的命令，在執行SQL語句的同時顯示執行計劃，設定EXP(LAIN)的目的是隻顯示執行計劃而不顯示統計資訊.。
2、SQL>explain plan for select ````````;
SQL>select * from table(dbms_xplan.display);

執行了set autotrace on explain語句之後，接下來的查詢、插入、更新、刪除語句就會顯示執行計劃，直到執行“set autotrace off;”語句。如果是設定了set autotrace on，除了會顯示執行計劃之外，還會顯示一些有用的統計資訊。

執行EXPLAIN PLAN FOR 可以只顯示執行計劃,然後執行如下查詢

SQL> select * from table(dbms_xplan.display);

如：

SQL> explain plan for select * from emp where deptno='20';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 150 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 150 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("DEPTNO"=20)

13 rows selected.

3、SQL>exec dbms_stats.delete_table_stats(USER,'表');(刪除表的統計資訊）

SQL>exec dbms_stats.gather_table_stats(USER,'表',METHOD_OPT=>'FOR ALL COLUMNS SIZE 100')（收集表的統計資訊）

4、AUTOTRACE的幾個常用選項　　

set autotrace off ---------------- 不生成autotrace 報告，這是預設模式
set autotrace on explain ------ autotrace只顯示最佳化器執行路徑報告
set autotrace on statistics -- 只顯示執行統計資訊
set autotrace on ----------------- 包含執行計劃和統計資訊
set autotrace traceonly ------ 同set autotrace on，但是不顯示查詢輸


(1). set autotrace on explain; --只顯示執行計劃
SQL> set autotrace on explain;
SQL>

<wbr>select count(*) from dba_objects;
<wbr>
<wbr>COUNT(*)
----------
<wbr> <wbr> <wbr> <wbr> 31820
<wbr>
Execution Plan
----------------------------------------------------------
<wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> SELECT STATEMENT Optimizer=CHOOSE
<wbr> <wbr> 1 <wbr> <wbr> <wbr> 0 <wbr> <wbr> SORT (AGGREGATE)
<wbr> <wbr> 2 <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr> VIEW OF 'DBA_OBJECTS'
<wbr> <wbr> 3 <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> UNION-ALL
<wbr> <wbr> 4 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> FILTER
<wbr> <wbr> 5 <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
<wbr> <wbr> 6 <wbr> <wbr> <wbr> 5 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr> <wbr> 7 <wbr> <wbr> <wbr> 6 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (FULL) OF 'USER$'
<wbr> <wbr> 8 <wbr> <wbr> <wbr> 6 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
<wbr> <wbr> 9 <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
<wbr>10 <wbr> <wbr> <wbr> 9 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
<wbr>11 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr>12 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (FULL) OF 'USER$'
<wbr>13 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
<wbr>
(2). set autotrace on statistics;--只顯示統計資訊
SQL> set autotrace on statistics;
SQL> select count(*) from dba_objects;
<wbr>
<wbr>COUNT(*)
----------
<wbr> <wbr> <wbr> <wbr> 31820
<wbr>
Statistics
----------------------------------------------------------
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>recursive calls
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>db block gets
<wbr> <wbr> <wbr> <wbr> <wbr> 25754 <wbr>consistent gets
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>physical reads
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>redo size
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 383 <wbr>bytes sent via SQL*Net to client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 503 <wbr>bytes received via SQL*Net from client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr>SQL*Net roundtrips to/from client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>sorts (memory)
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>sorts (disk)
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1 <wbr>rows processed
<wbr>
(3). set autotrace traceonly;--同set autotrace on 只是不顯示查詢輸出
SQL> set autotrace traceonly;
SQL> select count(*) from dba_objects;
<wbr>
Execution Plan
----------------------------------------------------------
<wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> SELECT STATEMENT Optimizer=CHOOSE
<wbr> <wbr> 1 <wbr> <wbr> <wbr> 0 <wbr> <wbr> SORT (AGGREGATE)
<wbr> <wbr> 2 <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr> VIEW OF 'DBA_OBJECTS'
<wbr> <wbr> 3 <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> UNION-ALL
<wbr> <wbr> 4 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> FILTER
<wbr> <wbr> 5 <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
<wbr> <wbr> 6 <wbr> <wbr> <wbr> 5 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr> <wbr> 7 <wbr> <wbr> <wbr> 6 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (FULL) OF 'USER$'
<wbr> <wbr>8 <wbr> <wbr> <wbr> 6 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
<wbr> <wbr> 9 <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
<wbr>10 <wbr> <wbr> <wbr> 9 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
<wbr>11 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr>12 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (FULL) OF 'USER$'
<wbr>13 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
<wbr>
Statistics
----------------------------------------------------------
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>recursive calls
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>db block gets
<wbr> <wbr> <wbr> <wbr> <wbr> 25754 <wbr>consistent gets
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>physical reads
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>redo size
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 383 <wbr>bytes sent via SQL*Net to client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 503 <wbr>bytes received via SQL*Net from client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2 <wbr>SQL*Net roundtrips to/from client
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>sorts (memory)
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 0 <wbr>sorts (disk)
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1 <wbr>rows processed
<wbr>
(4).set autotrace traceonly explain;--比較實用的選項，只顯示執行計劃，但是與set autotrace on explain;相比不會執行語句，對於僅僅檢視大表的Explain Plan非常管用。
SQL> set autotrace traceonly explain;
SQL> select * from dba_objects;
已用時間: <wbr>00: 00: 00.00
<wbr>
Execution Plan
----------------------------------------------------------
<wbr> <wbr> 0 <wbr> <wbr> <wbr> <wbr> <wbr> SELECT STATEMENT Optimizer=CHOOSE
<wbr> <wbr> 1 <wbr> <wbr> <wbr> 0 <wbr> <wbr> VIEW OF 'DBA_OBJECTS'
<wbr> <wbr> 2 <wbr> <wbr> <wbr> 1 <wbr> <wbr> <wbr> <wbr> UNION-ALL
<wbr> <wbr> 3 <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> FILTER
<wbr> <wbr> 4 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
<wbr> <wbr> 5 <wbr> <wbr> <wbr> 4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr> <wbr> 6 <wbr> <wbr> <wbr> 5 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>TABLE ACCESS (FULL) OF 'USER$'
<wbr> <wbr> 7 <wbr> <wbr> <wbr> 5 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
<wbr> <wbr> 8 <wbr> <wbr> <wbr> 3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
<wbr> <wbr> 9 <wbr> <wbr> <wbr> 8 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
<wbr>10 <wbr> <wbr> <wbr> 2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'
<wbr>11 <wbr> <wbr> 10 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> NESTED LOOPS
<wbr>12 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> TABLE ACCESS (FULL) OF 'USER$'
<wbr>13 <wbr> <wbr> 11 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

5、analyze

analyze table hr.employees compute(estimate) statistics;(compute收集每一行資料的統計資訊，比較耗時；estimate收集一部分資料行的統計資訊）

select t.owner,t.table_name,t.tablespace_name,t.blocks,t.empty_blocks,t.avg_space
from dba_tables t
where t.owner='HR';

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22036495/viewspace-1044873/，如需轉載，請註明出處，否則將追究法律責任。

相關文章