关于删除表数据时速度慢的测试报告

    

最近删除的时候无聊掐了一下手表，发现删除数据远远没有查询的时候速度快。于是乎，便做了下实验，实验内容如下，水平有限，希望能起到抛砖引玉的作用让让这个问题尽可能的搞明白！希望各位前辈能明确、无情的指正小弟操作不合理和思路不正确的地方。

问题：
发现delete的速度不如select的速度快。

分析： 导致delete速度慢的原因有很多，比如：
1.写大量回滚段，在RAID5上，写回滚段速度相对更慢，因为需要写校验位。
2.外键影响
3.在线用户访问过多，可以查看v$session_wait
4.没有表分区

实验：

基础环境建设：

创建tom用户的表空间：
create tablespace tom datafile '/opt/ora10g/oradata/jssbook/jssbook/tom01.dbf'
size 50m
autoextend on 
next 32m maxsize 2048m 
extent management local; 

确定创建成功：
select name from v$datafile where name like '%tom%'

创建tom用户：
create user tom identified by "jackson"
default tablespace tom
profile default
account unlock;

给用户授权：
grant dba to tom

查看临时表空间：
select * from v$tempfile

创建时没有声明，则用默认表空间。

查看test表大小：
select segment_name, bytes 
from user_segments 
where segment_type = 'TABLE';

查看test数据量：
select count(*) from test

向test表中注入数据：（该语句执行了N遍，不停地刷，不停地刷，最后达到150万条数据之后进行下一步---delete）
insert /* +append */ into  tom.test  select * from all_objects

删除表中数据：
delete from test;


下面是监控语句，这些语句分别在刚注入150万条数据之后执行了一下，抓了下状态，然后又在执行过程中抓了一下。（只抓取相应变化比较大的字段）
1.查看回滚段信息（tom用户使用）
select * from V$ROLLSTAT
删除之前信息：

USN        EXTENTS        RSSIZE        HWMSIZE           
0        6        385024        385024           
1        3        1171456        1171456           
2        37        22077440        22077440           
3        36        22011904        22011904           
4        5        3268608        3268608           
5        42        28303360        28303360           
6        33        18866176        18866176           
7        7        5365760        14671872           
8        37        23060480        23060480           
9        4        2220032        2220032           
10        36        21028864        21028864         


删除之后信息：

USN        EXTENTS        RSSIZE        HWMSIZE           
0        6        385024        385024           
1        3        1171456        1171456           
2        3        1171456        22077440           
3        3        1171456        22011904           
4        3        1171456        3268608           
5        3        1171456        28303360           
6        3        1171456        18866176           
7        332        208461824        208461824           
8        3        1171456        23060480           
9        3        1171456        2220032           
10        3        1171456        21028864           
                                   
USN        NAME           
0        SYSTEM           
1        _SYSSMU1$           
2        _SYSSMU2$           
3        _SYSSMU3$           
4        _SYSSMU4$           
5        _SYSSMU5$           
6        _SYSSMU6$           
7        _SYSSMU7$           
8        _SYSSMU8$           
9        _SYSSMU9$           
10        _SYSSMU10$         


2.查看回滚段的统计信息：
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status

　　FROM v$rollname n, v$rollstat s

　　WHERE n.usn = s.usn;
删除之前信息：

        NAME        EXTENTS        RSSIZE        HWMSIZE        STATUS           
1        SYSTEM        6        385024        385024        ONLINE           
2        _SYSSMU1$        3        1171456        1171456        ONLINE           
3        _SYSSMU2$        37        22077440        22077440        ONLINE           
4        _SYSSMU3$        36        22011904        22011904        ONLINE           
5        _SYSSMU4$        5        3268608        3268608        ONLINE           
6        _SYSSMU5$        42        28303360        28303360        ONLINE           
7        _SYSSMU6$        33        18866176        18866176        ONLINE           
8        _SYSSMU7$        7        5365760        14671872        ONLINE           
9        _SYSSMU8$        37        23060480        23060480        ONLINE           
10        _SYSSMU9$        4        2220032        2220032        ONLINE           
11        _SYSSMU10$        36        21028864        21028864        ONLINE         

删除之后信息：

        NAME        EXTENTS        RSSIZE        HWMSIZE        STATUS           
1        SYSTEM        6        385024        385024        ONLINE           
2        _SYSSMU1$        3        1171456        1171456        ONLINE           
3        _SYSSMU2$        27        15523840        22077440        ONLINE           
4        _SYSSMU3$        26        13492224        22011904        ONLINE           
5        _SYSSMU4$        3        1171456        3268608        ONLINE           
6        _SYSSMU5$        32        17817600        28303360        ONLINE           
7        _SYSSMU6$        23        9363456        18866176        ONLINE           
8        _SYSSMU7$        194        125689856        125689856        ONLINE           
9        _SYSSMU8$        27        12574720        23060480        ONLINE           
10        _SYSSMU9$        3        1171456        2220032        ONLINE           
11        _SYSSMU10$        26        11526144        21028864        ONLINE         


3.查看回滚段用户征用情况：
SELECT s.username, u.name

　　FROM v$transaction t, v$rollstat r, v$rollname u, v$session s

　　WHERE s.taddr = t.addr

　　AND t.xidusn = r.usn

　　AND r.usn = u.usn

　　ORDER BY s.username;

只有一个用户使用回滚段：


1        TOM        _SYSSMU7$         
        TOM        _SYSSMU7$         

结论：
用delete删除确实产生了大量回滚段，是造成delete速度慢的因素之一。
希望能有兄弟辛苦一下做一下分区、外键的性能对照实验，嘻嘻嘻~
其他原因正在补充中ing...