Oracle DB HWM(high water mark)對查詢的效能影響

HWM高水位是指一個segment(Table/Index)中已使用與未曾使用的Block分界線,
也就是說在HWM之下的Block是已使用(包含曾使用),在HWM之上的Block是未曾使用。
當不斷地insert資料時,HWM也會跟著向上移動,但是Delete資料,HWM卻不會往下移動,
就像飲料一樣,在裝杯時水量不斷增加,但是開始喝飲料時,水量就下降,
但是最高的水痕依然留在杯上,沒有降低。
而Oracle在執行Table的full scan時並不是只讀取有資料的Block,
而是從HWM以下的Block都會讀取(無論是否有資料),所以會影響查詢的時間。
如果有大量空的已使用Block就表示該Segment有大量的碎片,浪費大量的空間。

例如我有一個暫存各種資料的暫存表,有一次新增了三百多萬筆的資料
SQL> select count(1) from tmp_ddd;

  COUNT(1)
----------
   3705682

SQL> delete tmp_ddd;

3705682 rows deleted.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='TMP_DDD';

SEGMENT_NA SEGMENT_TY     BLOCKS
---------- ---------- ----------
TMP_DDD    TABLE           42912

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM dba_tables  WHERE table_name='TMP_DDD';

TABLE_NAME      NUM_ROWS   BLOCKS     EMPTY_BLOCKS
--------------- ---------- ---------- ------------
TMP_DDD                  0      42636            8
由上可知雖然已經將資料刪除,但是BLOCKS還是有這麼多

查詢Table的大小
SQL> select segment_name, bytes/1024/1024 "表大小MB" from dba_segments where segment_name='TMP_DDD';

SEGMENT_NAME   表大小MB
-------------- ----------
TMP_DDD            335.25

查詢Table的實際大小
SQL> select table_name, AVG_ROW_LEN ,NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024 "表實際大小MB",LAST_ANALYZED from dba_tables where table_name='TMP_DDD';

SEGMENT_NAME   表實際大小MB
-------------- -------------
TMP_DDD               .00494

碎片公式:(1-表實際數據大小/表大小)
(1-0.00494/335.25) = 99.9%

對於查詢的影響
SQL> set timing on;
SQL> select * from tmp_ddd;

no rows selected

Elapsed: 00:00:05.24
沒有任何資料,查詢花費5秒鐘。

所以可以利用truncate Table,把HWM降低。
(也可以利用alter table table_name move、drop再重建或10g以後還可以用shrink)
SQL> truncate table cusadmin.tmp_ddd;

Table truncated.

SQL> select segment_name,segment_type,blocks FROM dba_segments  WHERE segment_name='TMP_DDD';

SEGMENT_NA SEGMENT_TY     BLOCKS
---------- ---------- ----------
TMP_DDD    TABLE              16

SQL> analyze table cusadmin.tmp_ddd compute statistics;

Table analyzed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM dba_tables  WHERE table_name='TMP_DDD';

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------
TMP_DDD             0          0           16

再次查詢,不到1秒鐘
SQL> select * from tmp_ddd;

no rows selected

Elapsed: 00:00:00.01

修改 Ubuntu 20.04 系統時區