oracle的高水位線HWM
當我們建立一個表的時候,在還沒有為這張表插入資料時,oracle預設會分配8個塊給新建表。如下我們新建一張表t1:
SYS@orcl 25-SEP-14>create table t1 as select * from dba_objects where 1=2;
Table created.
以下是處理高水位線的方法:
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 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
說明此時高水位線已經降低了。
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掉,高水位線還是會保持原來的位置,因為這樣,所以即使我們將表中的大部分資料刪除了,在進行查詢操作的時候還是會進行全段掃描,全表掃描通常要讀出直到高水位線標記的所有的屬於該表的資料塊,這樣會大大降低查詢效能。另外當我們往表中插入記錄的時候,使用的是高水位線以上的資料塊,此時高水位線又會增長。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
以下是處理高水位線的方法:
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 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.
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
重新進行統計分析,檢視錶資訊:
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE 高水位線(HWM)Oracle
- oracle的高水位線(HWM)Oracle
- Oracle 高水位(HWM)Oracle
- Oracle表段中的高水位線HWMOracle
- Oracle高水位線(HWM)及效能優化Oracle優化
- Oracle 高水位(HWM)標記Oracle
- Oracle案例10——HWM(高水位線)效能優化Oracle優化
- delete與高水位線HWM回收delete
- 深入瞭解oracle的高水位(HWM)Oracle
- Oracle表段中高水位線HWMOracle
- oracle 高水位線及如何有效的降低高水位線Oracle
- oracle11g表的高水位線hwm與dbms_space系列一Oracle
- Oracle段高水位(HWM, high water mark)問題Oracle
- 各個Oracle 版本下如何調整高水位(HWM)Oracle
- oracle 回收高水位線Oracle
- Oracle 降低高水位線Oracle
- oracle回收高水位線Oracle
- 降低Oracle高水位線的方法Oracle
- Oracle的高水位線介紹Oracle
- oracle高水位線處理Oracle
- Oracle之降低高水位線Oracle
- oracle 高水位線詳解Oracle
- Oracle 高水位線的一點研究Oracle
- 一、oracle 高水位線詳解Oracle
- 對Oracle高水位線的研究實踐Oracle
- oracle表碎片以及整理(高水位線)Oracle
- Oracle高水位Oracle
- 【實驗】關於HWM(高水位)的學習與測試
- ORACLE資料庫降低高水位線方法Oracle資料庫
- Oracle delete 高水位線處理問題Oracledelete
- oracle回收高水位Oracle
- oracle高水位問題Oracle
- oracle 高水位分析處理Oracle
- 關於高水位線和deletedelete
- ORACLE的簡單處理高水位Oracle
- ORACLE高水位表的查詢方法Oracle
- 高水位線、行遷移行連結
- Oracle 找出需要回收高水位的表Oracle