Delete刪除表資料時對效能的影響分析

Delete刪除表資料時對效能的影響分析
上一篇文章中提到（），當我們用delete刪除資料時，因為無法降低高水位，對錶做全表掃描時會帶來資料庫效能問題，而truncate可以大大改善這一狀況，下面來看測試 SQL> conn zlm/zlmConnected. --建立表之前先記錄下剩餘資料表空間大小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 traceSQL> select count(*) from test1;  Execution Plan----------------------------------------------------------Plan hash value: 3896847026 --------------------------------------------------------------------| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |--------------------------------------------------------------------|   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 --------------------------------------------------------------------| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |--------------------------------------------------------------------|   0 | DELETE STATEMENT   |       |     1 |     2   (0)| 00:00:01 ||   1 |  DELETE            | TEST1 |       |            |          ||   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 offSQL> select count(*) from test1;   COUNT(*)----------         0 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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁