sqlplus的命令，在执行sql语句的同时显示执行计划，设置exp(lain)的目的是只显示执行计划而不显示统计信息.。2、sqlgt;explain
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(*)
----------
31820
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 loops
12 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(*)
----------
31820
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
(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 loops
12 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.00
execution 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 loops
12 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 t
where t.owner='hr';