海量資料表刪除方案

 

宣告：本文思路參考了hrb_qiuyb老師的文章《如何有效刪除一張大表》http://space.itpub.net/9134/viewspace-166488，特此感謝。

 

 

當我們的系統進入海量資料時代後，很多過去看起來輕鬆簡單的事情就變得比較複雜。此時，就需要我們採取一些獨特的技術和技巧，來避免因此帶來的一些問題。

 

 

如果一個資料表要進行刪除，而資料表對應的資料量很大，對應空間多，此時要進行資料表drop需要面對一些困難。

 

首先是系統記憶體資源和CPU資源的使用峰值。集中進行drop操作，系統會進行資料表結構的回收，對應資料分割槽和資料塊的回收。當資料表很大的時候，這個過程自然很長，對CPU來說消耗時間和數量都很大。同時，進行drop table的過程中，會將資料錶轉移到臨時段進行處理，這個過程對臨時段空間的消耗也是巨大的。

 

第二個是一個重要問題，就是對整體前端應用的影響。如果貿然進行drop操作，給業務系統造成影響，進而帶來影響。如果借用業務系統視窗期進行刪除操作，又不能保證視窗期夠用。

 

 

分步分階段大表刪除

 

 

總的指導原則是先刪除資料，不影響資料一致性的要求，之後分階段分步驟的進行空間回收。避免一次性drop對系統造成過大的壓力。

 

步驟：

 

1、首先使用帶reuse storage子句的truncate table，將資料刪除。reuse storage子句的作用是單純降低資料段data segment的高水位線，對分配的空間不進行回收。這樣truncate操作不涉及到空間回收，速度是可以接受的。

2、分若干次數，使用deallocate unused keep XXX的方法，將分配的空間回收。因為keep後面可以加入維持空間數量，所以可以分若干個視窗期進行回收。

 

 

實驗演示

 

首先準備實驗環境。

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0    Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

Executed in 0.051 seconds

 

--準備20萬的資料，遠談不上海量，例項而已。

SQL> select count(*) from m;

 

  COUNT(*)

----------

    201600

 

Executed in 0.221 seconds

 

 

此時，我們分析作為一個data segment，資料表M的相關引數資訊。

 

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2816   23068672         37

 

Executed in 0.201 seconds

 

 

透過dba_segments檢視可以知道，資料表M共分配在37個分割槽上，共包括2816個資料塊。空間佔有為23M左右。

 

刪除實驗，首先使用truncate table。

 

SQL> truncate table m reuse storage;

 

Table truncated

 

Executed in 0.52 seconds

 

 

reuse storage子句下，空間是不進行回收的，所以相對速度較快。下面是一個對比同型別規模資料表實驗。

 

 

SQL> select count(*) from md;

 

  COUNT(*)

----------

    201604

 

Executed in 0.451 seconds

 

SQL> truncate table md;

 

Table truncated

 

Executed in 0.901 seconds

 

 

相同的資料結構和資料量，使用的時間要超過reuse storage的方式。當然，帶有reuse storage的語句下空間也是不回收的。

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

Executed in 0.43 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2816   23068672         37

 

Executed in 0.131 seconds

 

 

發現，雖然資料被刪除了，但是空間沒有回收。在dba_segments上，依然顯示37個資料區的空間分配。

 

分階段進行空間回收，讓回收工作在受控範圍下進行。

 

 

--第一次空間回收

SQL> alter table m deallocate unused keep 20M; --控制在20M

 

Table altered

 

Executed in 0.11 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2568   21037056         36

 

Executed in 0.12 seconds

 

 

 

可見，將空間近似降低到21M左右。此後，可以在不同的視窗期中，根據自身情況不斷進行回收空間工作。

 

SQL> alter table m deallocate unused keep 15M;

 

Table altered

 

Executed in 0.06 seconds

 

SQL> alter table m deallocate unused keep 10M;

 

Table altered

 

Executed in 0.01 seconds

 

SQL> alter table m deallocate unused keep 3M;

 

Table altered

 

Executed in 0.01 seconds

 

SQL> alter table m deallocate unused keep 100k;

 

Table altered

 

Executed in 0.01 seconds

 

SQL>

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353         16     131072          2

 

Executed in 0.03 seconds

 

 

最後，要求回收到1k實驗。

 

SQL> alter table m deallocate unused keep 1k;

 

Table altered

 

Executed in 0 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353          8      65536          1

 

Executed in 0.03 seconds

 

 

雖然沒有按照我們的希望回收到1k，但也是已經到一個分割槽八個資料塊的程度。最後就好處理了，直接drop table即可。

 

SQL> drop table m;

 

Table dropped

 

Executed in 0.06 seconds

 

 

實驗告訴我們幾個結論：

 

1、在寫一句SQL的時候，一定要注意運算元據集合、物件的範圍。對於可能存在的海量資料訪問，一定要實現有準備，事後有監控。這樣才能保證系統可用性以及上線成功率；

2、海量資料處理的一個方法就是分割，根據業務的限制和要求，在時間上進行分割。處理原則是避開業務系統繁忙時間段，儘量將高負載工作進行劃分實現；

3、drop資料表的方法。一般來說，進行drop資料表是不能中間終止的。drop資料表時，oracle首先把資料段轉化為臨時段物件，之後開始不斷的進行空間回收。即使這個過程中間停止（強制終止），再次啟動的時候smon進行回覆也會進行回收刪除操作。所以，對資料表進行直接drop的時候，切記三思。