作者：王剑波200813 | 来源：互联网 | 2014-07-13 17:52
关于删除表数据时速度慢的测试最近删除的时候无聊掐了一下手表，发现删除数据远远没有查询的时候速度快。于是乎，便做了下实验，实验内容如下，水平有限，希望能起到抛砖引玉的作用让让这个问题尽可能的搞明白！...SyntaxHighlighter.all();
关于删除表数据时速度慢的测试
最近删除的时候无聊掐了一下手表，发现删除数据远远没有查询的时候速度快。于是乎，便做了下实验，实验内容如下，水平有限，希望能起到抛砖引玉的作用让让这个问题尽可能的搞明白！希望各位前辈能明确、无情的指正小弟操作不合理和思路不正确的地方。
问题：
发现delete的速度不如select的速度快。
分析：
导致delete速度慢的原因有很多，比如：
1.写大量回滚段，在RAID5上，写回滚段速度相对更慢，因为需要写校验位。
2.外键影响
3.在线用户访问过多，可以查看v$session_wait
4.没有表分区 www.2cto.com
实验：
基础环境建设：
创建tom用户的表空间：
create tablespace tom datafile &＃39;/opt/ora10g/oradata/jssbook/jssbook/tom01.dbf&＃39;
size 50m
autoextend on
next 32m maxsize 2048m
extent management local;
确定创建成功：
select name from v$datafile where name like &＃39;%tom%&＃39;
创建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 = &＃39;TABLE&＃39;;
查看test数据量：
select count(*) from test
www.2cto.com
向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
www.2cto.com
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
删除之后信息： www.2cto.com
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
www.2cto.com
ORDER BY s.username;
只有一个用户使用回滚段：
1 TOM _SYSSMU7$
TOM _SYSSMU7$
结论：用delete删除确实产生了大量回滚段，是造成delete速度慢的因素之一。
作者 jackson198574
京公网安备 11010802041100号