Test slow table data deletion speed

Test on slow table data deletion: recently, I was bored with my watch and found that data deletion is far from faster than query. As a result, I made an experiment. The experiment content is as follows, and the level is limited. I hope it will serve as an example to help you understand this problem as much as possible! I hope that our predecessors can clearly and relentlessly correct the unreasonable operations and incorrect ideas of our younger siblings. Problem: It is found that the speed of delete is not as fast as that of select. Analysis: there are many reasons for the slow delete speed, such as: 1. Write a large number of rollback segments. on RAID5, the write rollback segment speed is relatively slow because the write check bit is required. 2. foreign key impact 3. the number of online users is too large. You can view v $ session_wait4. no table partition www.2cto.com experiment: Basic Environment Construction: Create a table space for the tom User: create tablespace tom datafile '/opt/ora10g/oradata/jssbook/tom01.dbf' size 50 mautoextend on next 32 m maxsize 2048 m extent management local; Confirm creation successful: select name from v $ datafile where name like '% tom %' create a tom user: create user tom identified by "jackson" default tablespace tomprofile defaultaccount unlock; Authorize the user: grant db A to tom: If the temporary tablespace is not declared when select * from v $ tempfile is created, the default tablespace is used. View test TABLE size: select segment_name, bytes from user_segments where segment_type = 'table'; view test data volume: select count (*) from test www.2cto.com injects data into the test table: (this statement has been executed N times, constantly refreshing, and continuously refreshing. After the statement reaches 1.5 million data records, proceed to the next step --- delete) insert/* + append */into tom. test select * from all_objects delete table data: delete from test; the following are monitoring statements. These statements are executed after the first 1.5 million pieces of data are injected, And the status is captured, then, I caught it during the execution process. (Only capture fields with relatively large changes) 1. view rollback segment information (used by the tom user) select * from V $ ROLLSTAT before deletion: usn extents rssize hwmsize 0 6 385024 385024 1 3 1171456 1171456 2 37 22077440 22077440 3 36 22011904 22011904 4 5 3268608 5 42 3268608 28303360 6 33 28303360 18866176 7 18866176 5365760 8 37 14671872 23060480 9 4 2220032 2220032 10 36 21028864 21028864 information after deletion: usn extents rssize hwmsize 0 6 385024 385024 1 3 1171456 117145 6 2 3 1171456 22077440 3 1171456 22011904 4 3 1171456 5 3 3268608 1171456 6 3 28303360 1171456 7 18866176 332 208461824 8 3 208461824 9 3 1171456 23060480 10 3 1171456 2220032 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. view rollback segment statistics: SELECT n. name, s. extents, s. rssize, s. o Ptsize, s. hwmsize, s. xacts, s. status FROM v $ rollname n, v $ rollstat s WHERE n. usn = s. usn; information before deletion: name extents rssize hwmsize status 1 SYSTEM 6 385024 ONLINE 2 _ SYSSMU1 $3 385024 ONLINE 3 _ SYSSMU2 $37 1171456 1171456 ONLINE 4 _ SYSSMU3 $36 22077440 22077440 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 information after ONLINE deletion: www.2cto.com name extents rssize hwmsize status 1 SYSTEM 6 385024 385024 ONLINE 2 _ SYSSMU1 $3 1171456 ONLINE 3 _ SYSSMU2 $27 1171456 15523840 ONLINE 4 _ SYSSMU3 $26 22077440 13492224 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 ONLINE 11 _ SYSSMU10 $26 11526144 21028864 ONLINE 3. view User requisition of rollback segments: 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; only one user uses the rollback segment: 1 TOM _ SYSSMU7 $ TOM _ SYSSMU7 $ conclusion: deleting with delete does produce a large number of rollback segments, it is one of the factors that slow the delete speed. Author: jackson198574