Oracle truncate 和drop 的区别测试

  设置autotrace功能
1.创建plan_table表
	sqlplus /nolog
	conn sys /as sysdba
	@?/rdbms/admin/utlxplan.sql;
	create public synonym plan_table for plan_table;
	grant all on plan_table to public;
2.创建plustrace角色和权限
  @?/sqlplus/admin/plustrce.sql;
  DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public，这样所有用户都将拥有plustrace角色的权限. 
  grant plustrace to public ;
  已可用autotrace功能

3.测试:
    SQL> connect center/Dongyun123
	SQL> set autotrace on;
	设置时间提示
	SQL> set time on; 
	设置统计执行时间
	SQL 18:23:53> set timing on;
4.帮助:
	关于autotrace几个常用选项的说明:
	SET AUTOTRACE OFF ------------ 不生成AUTOTRACE 报告，这是缺省模式
	SET AUTOTRACE ON EXPLAIN ----- AUTOTRACE只显示优化器执行路径报告 
	SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
	SET AUTOTRACE ON ------------- 包含执行计划和统计信息 
	SET AUTOTRACE TRACEONLY ------- 同set autotrace on，但是不显示查询输出 
 

sys@TICKET> set auo
SP2-0158: 未知的 SET 选项 "auo"
sys@TICKET> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
sys@TICKET> set autotrace on stat;
sys@TICKET> create table test as select * from dba_objects
  2  union all select * from dba_objects
  3  union all select * from dba_objects
  4  union all select * from dba_objects
  5  union all select * from dba_objects
  6  union all select * from dba_objects
  7  union all select * from dba_objects
  8  union all select * from dba_objects
  9  union all select * from dba_objects
 10  union all select * from dba_objects
 11  union all select * from dba_objects
 12  union all select * from dba_objects
 13  union all select * from dba_objects
 14  union all select * from dba_objects
 15  union all select * from dba_objects
 16  union all select * from dba_objects;

表已创建。

sys@TICKET> 
sys@TICKET> commit;

提交完成。

sys@TICKET> commit;

提交完成。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
        88


统计信息
----------------------------------------------------------
         22  recursive calls
          0  db block gets
         96  consistent gets
          1  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> truncate table test;

表被截断。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
         1


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> drop table test;

表已删除。
sys@TICKET> create table test as select * from dba_objects
  2  union all select * from dba_objects
  3  union all select * from dba_objects
  4  union all select * from dba_objects
  5  union all select * from dba_objects
  6  union all select * from dba_objects
  7  union all select * from dba_objects
  8  union all select * from dba_objects
  9  union all select * from dba_objects
 10  union all select * from dba_objects
 11  union all select * from dba_objects
 12  union all select * from dba_objects
 13  union all select * from dba_objects
 14  union all select * from dba_objects
 15  union all select * from dba_objects
 16  union all select * from dba_objects;

表已创建。

sys@TICKET> 
sys@TICKET> commit;

提交完成。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
        88


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> truncate table test drop storage;

表被截断。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
         1


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> drop table test;

表已删除。

sys@TICKET> create table test as select * from dba_objects
  2  union all select * from dba_objects
  3  union all select * from dba_objects
  4  union all select * from dba_objects
  5  union all select * from dba_objects
  6  union all select * from dba_objects
  7  union all select * from dba_objects
  8  union all select * from dba_objects
  9  union all select * from dba_objects
 10  union all select * from dba_objects
 11  union all select * from dba_objects
 12  union all select * from dba_objects
 13  union all select * from dba_objects
 14  union all select * from dba_objects
 15  union all select * from dba_objects
 16  union all select * from dba_objects;

表已创建。

sys@TICKET> commit;

提交完成。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
        88


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> truncate table test reuse storage;

表被截断。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
        88


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> select * from test;

未选定行


统计信息
----------------------------------------------------------
        323  recursive calls
          1  db block gets
         40  consistent gets
          1  physical reads
         96  redo size
       1124  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

sys@TICKET> drop table test;

表已删除。

sys@TICKET> create table test as select * from dba_objects
  2  union all select * from dba_objects
  3  union all select * from dba_objects
  4  union all select * from dba_objects
  5  union all select * from dba_objects
  6  union all select * from dba_objects
  7  union all select * from dba_objects
  8  union all select * from dba_objects
  9  union all select * from dba_objects
 10  union all select * from dba_objects
 11  union all select * from dba_objects
 12  union all select * from dba_objects
 13  union all select * from dba_objects
 14  union all select * from dba_objects
 15  union all select * from dba_objects
 16  union all select * from dba_objects;

表已创建。

sys@TICKET> 
sys@TICKET> commit;

提交完成。

sys@TICKET> select extents from user_segments where segment_name='TEST';

   EXTENTS
----------
        88


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys@TICKET> drop table test;

表已删除。

sys@TICKET> select extents from user_segments where segment_name='TEST';

未选定行


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
        284  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

sys@TICKET> spool off;

由以上可以看出得出一下信息:
1.如果从性能的角度来看，truncate  reuse   storage 主要是针对大量extent 的字典管理表空间.
2.DROP TABLE的时候，Extents要被回收。而truncate table test reuse storage没有Extents要被回收.
3.truncate table test drop storage 的时候，Extents要被回收,HWM会变化 。
4.truncate table test功能和truncate table test drop storage差不多.
5.truncate table reuse storage在性能上比truncate table  drop storage好,