您當前位置：首頁 > 互聯網 > Delete刪除表數據時對性能的影響分析

Delete刪除表數據時對性能的影響分析

來源：程序員人生發布時間：2014-10-03 08:00:01 閱讀次數：4412次

上一篇博客中提到，當我們用delete刪除數據時，因為無法降低高水位，對表做全表掃描時會帶來數據庫性能問題，而truncate可以大大改善這一狀況，下面來看測試

SQL> conn zlm/zlm

Connected.

--創建表之前先記錄下剩余表空間大小

SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

ZLM 6 128 51380224 6272 6

--創建一個100W行的大表

SQL> create table test1(int number);

Table created.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ------------ ----------- ------------------

TEST1 0 0 0 0 27-SEP-14

注意，創建完表結構而未插入數據時，表的高水位是0，并沒有為該表分配任何數據塊

SQL> declare

2 i number := 0;

3 begin

4 for i in 1 .. 1000000 loop

5 insert into test1 values (i);

6 end loop;

7 commit;

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test1;

COUNT(*)

----------

1000000

--查看高水位狀況

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

----------- ------------ ---------- ---------- ----------

6 130 13631488 1664 28

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'TEST%';

BLOCK_ID EXTENT_ID BYTES BLOCKS

---------- ---------- ---------- ----------

128 0 65536 8

136 1 65536 8

144 2 65536 8

152 3 65536 8

160 4 65536 8

168 5 65536 8

176 6 65536 8

184 7 65536 8

192 8 65536 8

200 9 65536 8

208 10 65536 8

216 11 65536 8

224 12 65536 8

232 13 65536 8

240 14 65536 8

248 15 65536 8

256 16 1048576 128

384 17 1048576 128

512 18 1048576 128

640 19 1048576 128

768 20 1048576 128

896 21 1048576 128

1024 22 1048576 128

1152 23 1048576 128

1280 24 1048576 128

1408 25 1048576 128

1536 26 1048576 128

1664 27 1048576 128

SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

ZLM 6 1792 37748736 4608 6

SQL> set autot trace

SQL> select count(*) from test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1599 consistent gets

0 physical reads

0 redo size

526 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> delete from test1;

1000000 rows deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 2642947686

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

814 recursive calls

1038983 db block gets

1953 consistent gets

8 physical reads

245334988 redo size

847 bytes sent via SQL*Net to client

769 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1000000 rows processed

用delete刪除100W行數據的大表TEST1時，產生了大量的redo（2億多），另外還有很多一致性讀（1953），讀取了100多W個數據塊，814次遞歸調用，可以看到，用delete刪除表記錄，對數據庫的性能消耗是很大的，尤其是當delete大量行的時候

SQL> set autot off

SQL> select count(*) from test1;

COUNT(*)

----------

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

----------- ------------ ---------- ---------- ----------

6 130 13631488 1664 28

--用delete刪除數據后查看執行計劃

SQL> set autot trace

SQL> select count(*) from test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

1599 consistent gets

0 physical reads

0 redo size

525 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

此時雖然表中已經沒有一條記錄了，但是由于TEST1表并沒有索引，走的是全表掃描，全表掃描就是要從hearder_block的值一致掃描到blocks得值，即從block 130一致到block 1664，可以看到，即使去訪問一個空表，也要消耗1599次邏輯讀，這就是高水位沒有下降的緣故，之前說過，delete操作并不會降低表的高水位，帶來的副作用就是訪問該表時帶來的性能下降（產生大量邏輯讀）

--truncate表后再次查看統計信息

SQL> truncate table test1;

Table truncated.

SQL> set autot trace

SQL> select count(*) from test1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3896847026

--------------------------------------------------------------------

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |