Oracle truncate 和drop 的区别测试

longgangbai

浏览: 7332406 次
性别:
来自: 上海

最近访客更多访客>>

sdcharles

paladin1988

ljq867

yuxiangtong

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle数据库实验室

  设置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好,

分享到：

Oracle性能调优学习笔记---调优的角色,层 ... | Oracle使用实例证明实体化视图和视图的区别

2011-09-15 21:47
浏览 1686
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle truncate 和drop 的区别测试

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Oracle truncate 和drop 的区别测试

评论

发表评论

相关推荐

Oracle使用实例证明实体化视图和视图的区别

Oracle中证明spfile和pfile的启动顺序

ORACLE在windows上使用orakill结束oracle会话的线程

Oracle【COLUMN】设置表字段默认值仅对未来生效

Oracle无法使用命令 /usr/X11R6/bin/xdpyinfo 自动检查显示器颜色

Oracle Listener中常用命令

简单描述Oracle临时表的存储特点

Oracle实现定时expdp备份

用户的＄home/.dmrc已被忽略，这将无法保存 默认会话和语言

undo表空间学习总结

Oracle11g中SCN与TimeStamp的相互转换

ORA-01578: ORACLE 数据块损坏的解决方案

Oracle v$parameter表知多少

Oracle 初始化参数的管理

Oracle查询数据库的各种文件

Oracle 性能调优学习笔记(二)- ----Oracle SQL TRACE和TKPROF使用

Why VKTM background process in Oracle 11g(转)

没事看看Oracle的AlertSID.log做一下分析

Oracle Job使用和管理

Oracle 11g新特性之Create pfile from memory

最近访客更多访客>>

用户的＄home/.dmrc已被忽略，这将无法保存默认会话和语言