Oracle 中使用tkprof来查看比较delete,truncate,drop

先来从整体上看这三者：delete是属于dml，truncate和drop是ddl；在删除操作上，delete只是会删除数据，而且是要生成日志文件的，并且可以通过rollback回滚数据；但truncate和drop则会删除所在的表的结构与空间，而且不可回滚；下面从Oracle的输出日志文档来看看这些操作（以下操作均在sqlplus中进行）。

1.数据准备，创建一个表

create table t_part(object_id int,object_name varchar2(1000))

partition by range(object_id)(

          partition p1 values less than(10000),

          partition p2 values less than(20000),

          partition p3 values less than(30000),

          partition p4 values less than(40000),

          partition pm values less than(maxvalue)

);

insert into t_part select object_id,object_name from sys.dba_objects;

查看总体数据量： select count(*) from t_part;​   72561

查看分区的数据量：select count(*) from t_part partition(p1);  9708

                              ​select count(*) from t_part partition(p2); 9978；

​                              select count(*) from t_part partition(p1); 10000；

2.开始删除并比较，这里先进行追踪显示：

    2.1修改会话追踪  ：  alter session set sql_trace=true;​

    2.2​分别进行删除操作：

      delete from t_part where object_id<9708;

     alter table t_part drop partition p2;

     alter  table  t_part truncate partition p3;

   2.3关闭会话追踪。

因为打开了会话追踪，以上的操作都会生成一个输出文档在默认的路径，在sqlplus中输入  show  parameter user_dump_dest;​​


显示出路径

 2.4 退出sqlplus切换到该目录下，在这里使用tkprof工具将次文件整理并重定向到d盘中

​    切换到该目录下找到该文件

找到输出文件

    然后在cmd中切换到该目录下输入：tkprof   追踪文件名   重定向的文件路径  

处理追踪文件

​​然后找到该文件打开，会发现里面有很多东西，

处理过的文件

中间的那几个参数说明要注意；

   COUNT：这个语句被parse、execute、fetch的次数

   CPU:cpu被占用的时间

  ELAPSED： 这个语句所有消耗在parse、execute、fetch的总的时间

  ​DISK： 从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存              中读取的数据而不是从磁盘上读取的数据

  ​QUERY： 在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。 一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。

  ​ CURRENT： 在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。 

  ​ ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。​

接下来找到我们操作的那几句sql

    delete语句：


delete时系统的各项消耗

drop 语句：

drop时系统的各项消耗填写图片摘要（选填）

truncate 语句：

truncate时系统的消耗

对比图中的数据可以看出，drop与truncate不去操作某一行，就不用进行全表扫描（当然这里没有加入索引），所以操作的过程就更会简单尤其是在 ​QUERY和CURRENT的处理上消耗明显要小，在数据越大的情况下这种差别越明显。一般来讲在速度上drop>truncate>delete.

3.再来删除数据之后表所占的空间

新建表：create table t as select object_id,object_name from sys.dba_objects;

查出表空间的大小：select count(*) from user_extents wher segment_name='T'（这里的表名要大写，因为Oracle会在没有引号的情况下将所的语句转为大写，但是有引号就会保持引号的内容，在创建表的时候，存在数据库里面的表名是T）;​

delete t;​

同样再查询一次发现表空间没有变，而使用drop和truncate就会明显看出缩小了。

实际上delete之后还可以用下面的语句：alter table t move;这个的作用就相当于磁盘碎片整理，会对表的空间重新进行分配，也会大大减少空表所占的空间​