潛水珽的異想世界

High water mark 對資料庫空間的影響

High water mark的原理

    High Water Mark (HWM)指的是當我們建立一個segment的時候，在segment內的一個指標，界定了segment 內曾經配置過的block水位。

The high water mark is the boundary between used and unused space in a segment ~ (From Oracle Concept)。

    簡單如圖所示，剛建立segment時尚未insert資料，HWM通常在segment header附近，當你不斷地insert資料使用了更多的block之後，HWM自然會跟著移動。但是日後你delete資料後，雖然實際儲存資料的block已經減少，但是HWM仍然不會改變，這就像水庫的水位曾經滿到某個刻度而留下一道水痕，雖然水位下降了但是仍然清晰可見那道高水位。

     HWM之下如果存在太多空的block，最大的缺點就是當發生Full Table Scan (FTS)的時候，會造成過多無謂的IO，因為FTS會讀取segment內的block直到HWM為止，如此也會造成buffer pool的使用效率不佳。

刪除大量資料後調整high water mark

1.      建立一個測試table名為Z_TABLE

   CREATE TABLE Z_TEST

(

  A  VARCHAR2(4000 BYTE),

  B  VARCHAR2(4000 BYTE),

  C  VARCHAR2(4000 BYTE)

)

TABLESPACE TCHLOL_DATA

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            MINEXTENTS       1

            MAXEXTENTS       2147483645

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

2.      塞20000筆的資料

透過Quest Data factory工具來幫我們塞測試資料

select count(*) from Z_TEST;

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

40000

3.      check目前資料大小

select substr(segment_name,1,20) segment, bytes / 1024 "Size

[KB]" from user_segments

where segment_name in ('Z_TEST');

              segment       Size [KB]

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

              Z_TEST           81920

4.      刪除所有資料

delete from Z_TEST;

select count(*) from Z_TEST;

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

0

5.      計算目前的資料大小

select substr(segment_name,1,20) segment, bytes / 1024 "Size

[KB]" from user_segments

where segment_name in ('Z_TEST');

              segment       Size [KB]

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

              Z_TEST           81920

6.      調降high water mark

Alter table Z_TEST move;

7.      計算目前的資料大小

select substr(segment_name,1,20) segment, bytes / 1024 "Size

[KB]" from user_segments

where segment_name in ('Z_TEST');

              segment       Size [KB]

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

              Z_TEST               64        ß回復到最初init的大小

降低high water mark的方式

1.      EXP/IMP

2.      alter table XXX move

3.      alter table XXX shrink space  (only for ORACLE 10g )

alter table XXX shrink space使用Row lock,而alter table XXX move則使用table lock。