關於刪除表資料時速度慢的測試報告

最近刪除的時候無聊掐了一下手錶，發現刪除資料遠遠沒有查詢的時候速度快。於是乎，便做了下實驗，實驗內容如下，水平有限，希望能起到拋磚引玉的作用讓讓這個問題儘可能的搞明白！希望各位前輩能明確、無情的指正小弟操作不合理和思路不正確的地方。

問題：
發現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...

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20802110/viewspace-1063214/，如需轉載，請註明出處，否則將追究法律責任。

關於刪除表資料時速度慢的測試報告

相關文章