[日期：2011-08-05]
来源：Linux社区 作者：wangshengfeng1986211[字体： ]2010-07-01 15:03
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>select count(*) from dba_objects;
COUNT(*)---------- 31820Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 VIEW OF 'DBA_OBJECTS' 3 2 UNION-ALL 4 3 FILTER 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 6 5 NESTED LOOPS 7 6 TABLE ACCESS (FULL) OF 'USER$' 8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)11 3 NESTED LOOPS12 11 TABLE ACCESS (FULL) OF 'USER$'13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)(2). set autotrace on statistics;--只显示统计信息SQL> set autotrace on statistics;SQL> select count(*) from dba_objects;COUNT(*)---------- 31820Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 25754 consistent gets 0 physical reads 0 redo size 383 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed(3). set autotrace traceonly;--同set autotrace on 只是不显示查询输出SQL> set autotrace traceonly;SQL> select count(*) from dba_objects;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 VIEW OF 'DBA_OBJECTS' 3 2 UNION-ALL 4 3 FILTER 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 6 5 NESTED LOOPS 7 6 TABLE ACCESS (FULL) OF 'USER$' 8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)11 3 NESTED LOOPS12 11 TABLE ACCESS (FULL) OF 'USER$'13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 25754 consistent gets 0 physical reads 0 redo size 383 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed(4).set autotrace traceonly explain;--比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。SQL> set autotrace traceonly explain;SQL> select * from dba_objects;已用时间: 00: 00: 00.00Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW OF 'DBA_OBJECTS' 2 1 UNION-ALL 3 2 FILTER 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 5 4 NESTED LOOPS 6 5 TABLE ACCESS (FULL) OF 'USER$' 7 5 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 8 3 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' 9 8 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)10 2 TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'11 10 NESTED LOOPS12 11 TABLE ACCESS (FULL) OF 'USER$'13 11 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 twhere t.owner='HR';