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

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

問題：
發現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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章