oracle的高水位線HWM

jane_pop發表於2014-09-25
當我們建立一個表的時候,在還沒有為這張表插入資料時,oracle預設會分配8個塊給新建表。如下我們新建一張表t1:
SYS@orcl 25-SEP-14>create table t1 as select * from dba_objects where 1=2;

Table created.
然後再查詢oracle為這張表分配了多大的空間:
SYS@orcl 25-SEP-14>select segment_name,segment_type,blocks from user_segments where segment_name='T1';

SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS
--------------------------------------------------------------------------------- ------------------ ----------
T1                                                                                TABLE                       8
隨著業務的增長我們會往這張表裡插入大量的資料,高水位線就是一個段內的標記,用來說明還有多少沒使用的塊可以分配給這個segment。通常情況下,高水位線的增長幅度是一次5個資料塊。原則上高水位線只能增大不能縮小,即使將表中的資料全部delete掉,高水位線還是會保持原來的位置,因為這樣,所以即使我們將表中的大部分資料刪除了,在進行查詢操作的時候還是會進行全段掃描,全表掃描通常要讀出直到高水位線標記的所有的屬於該表的資料塊,這樣會大大降低查詢效能。另外當我們往表中插入記錄的時候,使用的是高水位線以上的資料塊,此時高水位線又會增長。
以下是處理高水位線的方法:
1.將表進行重建,使用語句alter table table_name move tablespace tablespace_name;
注意,使用了這個語句之後,原表上的索引也會無效,因此執行完以上語句之後要對錶中的索引進行重建。
2.執行alter table table_name shrink space;
這是oracle10g引入的新效能,注意,在執行這條語句之前,先要允許行移動,alter table table_name enable row movement;
move是透過移動資料來降低hwm,需要更多的磁碟空間,而shrink是透過delete和insert,會產生較多的undo和redo。
move的效率相對於shrink來說要高一些,但是會導致索引失效。
3.重建表
複製要保留的資料到臨時表t_tmp,drop原表,然後rename臨時表t_tmp為原表,注意重建過程需要備份該表的觸發器和儲存過程等語法。
4.邏輯匯入匯出
5.alter table table_name deallocate unused;
deallocate unused會hwm上面未使用的空間,但是並不會釋放hwm下面的自由空間,也不會移動hwm的位置。
6.truncate table

下面我們以第一種方式move為例看看如何處理高水位線:
剛剛我們建立了一張表結構和dba_objects一樣的表t1,現在做如下相關查詢:


SYS@orcl 25-SEP-14>select segment_name,segment_type,blocks from user_segments where segment_name='T1';

SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS
--------------------------------------------------------------------------------- ------------------ ----------
T1                                                                                TABLE                       8

往表中插入足夠多的資料:
SYS@orcl 25-SEP-14>insert /*+ APPEND NOLOGGING */ into t1 select * from dba_objects;

72602 rows created.

SYS@orcl 25-SEP-14>commit;

Commit complete.

SYS@orcl 25-SEP-14>select max(object_id) from t1;

MAX(OBJECT_ID)
--------------
         75306
SYS@orcl 25-SEP-14>insert /*+ APPEND NOLOGGING */ into t1 select 
  2  owner,object_name,subobject_name,object_id+75306,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,secondary,
  3  namespace,edition_name from t1;

72602 rows created.

SYS@orcl 25-SEP-14>commit;

Commit complete.

建立索引:
SYS@orcl 25-SEP-14>create unique index idx_objectid on t1(object_id) tablespace users;

Index created.

SYS@orcl 25-SEP-14>create index idx_index02 on t1(owner,object_name) tablespace users;

Index created.
檢視索引資訊:
SYS@orcl 25-SEP-14>select index_name,status from user_indexes where table_name='T1';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_INDEX02                    VALID
IDX_OBJECTID                   VALID

再次檢視錶的資訊:
SYS@orcl 25-SEP-14>select segment_name,segment_type,blocks from user_segments where segment_name='T1';


SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS
--------------------------------------------------------------------------------- ------------------ ----------
T1                                                                                TABLE                    2176

SYS@orcl 25-SEP-14>select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1
可以看到這裡的記錄為空,這是因為沒有做統計分析:
SYS@orcl 25-SEP-14>exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

SYS@orcl 25-SEP-14>select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                 145204       2068            0
這裡看到empty_blocks的資訊仍為空,這是因為這個欄位只有透過analyze收集統計資訊之後才會有資料:
SYS@orcl 25-SEP-14>analyze table t1 compute statistics;

Table analyzed.

SYS@orcl 25-SEP-14>select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';


TABLE_NAME                       NUM_ROWS     BLOCKS     EMPTY_BLOCKS
------------------------------          ----------          ----------     ------------
T1                                          145204               2068             107

SYS@orcl 25-SEP-14>select   2068 +  107 from dual;

2068+107
----------
      2175
(這裡和前面的2176相比少了一個塊,那一個塊是用來儲存segment資訊的)

下面我們delete表中的資料,留下兩行:
SYS@orcl 25-SEP-14>delete from t1 where rownum<=145202;

145202 rows deleted.

SYS@orcl 25-SEP-14>select count(*) from t1;

COUNT(*)
----------
         2
再次檢視錶資訊:
SYS@orcl 25-SEP-14>select segment_name,segment_type,blocks from user_segments where segment_name='T1';


SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS
--------------------------------------------------------------------------------- ------------------ ----------
T1                                                                                TABLE                    2176


SYS@orcl 25-SEP-14>select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                 145204       2068          107

可以看出高水位線並沒有發生任何變化。下面使用move來處理高水位線:
SYS@orcl 25-SEP-14>alter table t1 move tablespace hh;

Table altered.

檢視索引狀態已變為unusable,重建索引:
SYS@orcl 25-SEP-14>select index_name,status from user_indexes where table_name='T1';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_INDEX02                    UNUSABLE
IDX_OBJECTID                   UNUSABLE

SYS@orcl 25-SEP-14>alter index IDX_INDEX02 rebuild;

Index altered.

SYS@orcl 25-SEP-14>alter index IDX_OBJECTID rebuild;

Index altered.

SYS@orcl 25-SEP-14>select index_name,status from user_indexes where table_name='T1';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_INDEX02                    VALID
IDX_OBJECTID                   VALID

重新進行統計分析,檢視錶資訊:
SYS@orcl 25-SEP-14> analyze table t1 compute statistics;

Table analyzed.
SYS@orcl 25-SEP-14>select segment_name,segment_type,blocks from user_segments where segment_name='T1';


SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS
--------------------------------------------------------------------------------- ------------------ ----------
T1                                                                                TABLE                       8


SYS@orcl 25-SEP-14>select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                      2          4            4

說明此時高水位線已經降低了。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29800581/viewspace-1280891/,如需轉載,請註明出處,否則將追究法律責任。

相關文章