oracle 高水位線及如何有效的降低高水位線

dbLjy2015發表於2017-01-24
Oracle
一、什麼是水線(High Water Mark)?

    所有的oracle段(segments，在此，為了理解方便，建議把segment作為表的一個同義詞) 都有一個在段內容納資料的上限，我們把這個上限稱為"high water mark"或HWM。這個HWM是一個標記，用來說明已經有多少沒有使用的資料塊分配給這個segment。HWM通常增長的幅度為一次5個資料塊，原則上HWM只會增大，不會縮小，即使將表中的資料全部刪除，HWM還是為原值，由於這個特點，使HWM很象一個水庫的歷史最高水位，這也就是HWM的原始含義，當然不能說一個水庫沒水了，就說該水庫的歷史最高水位為0。但是如果我們在表上使用了truncate命令，則該表的HWM會被重新置為0。


二、HWM資料庫的操作有如下影響：

a) 全表掃描通常要讀出直到HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料。
b) 即使HWM以下有空閒的資料庫塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料塊，此時HWM會自動增大。


三、查詢哪些表需要降低高水位線
想要確定哪些表需要降低高水位線，要基於有正確的統計資訊的，統計資訊的錯誤也將導致結果的錯誤。

  1. SYS@prod > SELECT NUM_ROWS,
  2.   2 AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED,
  3.   3 BLOCKS * 8 / 1024 TRUE,
  4.   4 (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB,
  5.   5 TABLE_NAME
  6.   6 FROM dba_tables
  7.   7 WHERE tablespace_name = 'VASTTBS'
  8.   8 AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100
  9.   9 AND rownum < 11
  10.  10 order by RECOVER_MB desc;
NUM_ROWS：表中現有的行數
NEED：行的平均長度*行數/1024/1024/0.9=表需要的空間，單位為MB，其中0.9為1-pctfree得到的。一般情況下建立表pctfree預設都是10%
TRUE：佔用空間
RECOVER_MB：可恢復的空間
where條件中，1定位表空間；2篩選出能壓縮至少100M的表，也可根據需求增加減少；3顯示前11行。

四、如何降低高水位線
降低高水位線的方式一共有7種，在這裡我們一一列舉出來。
a)    truncate 表
b)    開啟對錶的shrink
c)    CTAS
d)    表移動
e)    exp/imp
f)    expdp/impdp
g)    線上重定義


接下來將用實驗的方式將各方法的操作以及效果列舉出來。

oracle資料庫版本如下
  1. SYS@prod > select * from v$version;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. PL/SQL Release 11.2.0.3.0 - Production
  6. CORE 11.2.0.3.0 Production
  7. TNS for Linux: Version 11.2.0.3.0 - Production
  8. NLSRTL Version 11.2.0.3.0 - Production

實驗環境模擬
  1. --建立一張表
  2. VAST@prod > create table ote as select * from dba_objects;
  3. Table created.
  4. --建立索引
  5. VAST@prod > create index ind_ote_id on ote(object_id);
  6. Index created.
  7. --建立同義詞
  8. VAST@prod > create or replace public synonym ote for vast.ote;
  9. Synonym created.
  10. --收集統計資訊
  11. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
  12. Table analyzed.
  13. --段的資訊
  14. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  15. BYTES/1048576 BLOCKS EXTENTS
  16. ------------- ---------- ----------
  17.             9 1152 24
  18. --刪除表中資料
  19. VAST@prod > delete from ote;
  20. 75355 rows deleted.
  21. VAST@prod > commit;
  22. Commit complete.
  23. --段的資訊
  24. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  25. BYTES/1048576 BLOCKS EXTENTS
  26. ------------- ---------- ----------
  27.             9 1152 24
  28. --分析表塊數，空閒塊數，行數
  29. VAST@prod > SELECT blocks, empty_blocks, num_rows
  30.   2 FROM user_tables
  31.   3 WHERE table_name = 'OTE';
  32.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  33. ---------- ------------ ----------
  34.       1099           53      75355
  35. --BLOCKS + EMPTY_BLOCKS (1099+53=1152)DBA_SEGMENTS.BLOCKS 表示分配給這個表的所有的資料庫塊的數目。
  36. --USER_TABLES.BLOCKS表示已經使用過的資料庫塊的數目。USER_TABLES.EMPTY_BLOCKS則表示剩餘塊的數目。
  37. --插入資料
  38. VAST@prod > insert into ote select * from dba_objects where object_id<20000;
  39. 19677 rows created.
  40. VAST@prod > commit;
  41. Commit complete.
  42. --段的資訊
  43. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  44. BYTES/1048576 BLOCKS     EXTENTS
  45. ------------- ---------- ----------
  46.             9       1152         24
  47. --可見即便插入了資料，但是分給OTE段的塊數目沒有改變，這是因為之前刪除了一部分資料，oracle在插入的時候，找到了這些可以被覆蓋的塊，
  48. --將這些資料插入到這些塊中，故沒有分配新的塊給OTE段
  49. --分析表塊數，空閒塊數，行數
  50. VAST@prod > SELECT blocks, empty_blocks, num_rows
  51.   2 FROM user_tables
  52.   3 WHERE table_name = 'OTE';
  53.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  54. ---------- ------------ ----------
  55.       1099           53      75355
  56. --使用hint
  57. --使用append將會把新插入的資料插到高水位線之後，省去了查詢可覆蓋塊的時間，這種方式也可提高插入的速度，但是會提升高水位線的高度。
  58. VAST@prod > insert /*+append*/ into ote select * from dba_objects where object_id<20000;
  59. 19677 rows created.
  60. VAST@prod > commit;
  61. Commit complete.
  62. --段的資訊
  63. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  64. BYTES/1048576 BLOCKS     EXTENTS
  65. ------------- ---------- ----------
  66.            11       1408         26
  67. --可見這次沒有查詢可覆蓋的塊，而是直接將資料插在了高水位線之後的空閒塊中。        
  68. --收集統計資訊
  69. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
  70. Table analyzed.
  71. --分析表塊數，空閒塊數，行數
  72. VAST@prod > SELECT blocks, empty_blocks, num_rows
  73.   2 FROM user_tables
  74.   3 WHERE table_name = 'OTE';
  75.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  76. ---------- ------------ ----------
  77.       1370           38      39354
  78. --對錶的資訊檢視時，建議先進行統計資訊的收集，不然會得到錯誤的資訊。

1、truncate 表
  1. --截斷表
  2. VAST@prod > truncate table ote;
  3. Table truncated.
  4. --收集統計資訊
  5. VAST@prod > analyze table ote compute statistics; --estimate/compute
  6. Table analyzed.
  7. --段的資訊
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  9. BYTES/1048576 BLOCKS     EXTENTS
  10. ------------- ---------- ----------
  11.         .0625          8          1
  12. --此時可見OTE的段只剩下8個塊了，因為表還是存在的，段頭還是記錄了一些基本資訊的。        
  13. --分析表塊數，空閒塊數，行數
  14. VAST@prod > SELECT blocks, empty_blocks, num_rows
  15.   2 FROM user_tables
  16.   3 WHERE table_name = 'OTE';
  17.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  18. ---------- ------------ ----------
  19.          0            8          0
  20. --檢查索引以及同義詞是否可用
  21. VAST@prod > col name for a15;
  22. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  23.   2 union all
  24.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

  26. TYPE    NAME            STATUS
  27. ------- --------------- --------
  28. INDEX   IND_OTE_ID      VALID
  29. SYNONYM IND_OTE_ID      VALID        
  30. --可見此種方式執行過後，原索引以及同義詞還是可用的。
優點：速度快
缺點：表中資料全無
建議：當需要delete全表的時候，確定表中資料無用，可以使用這種方式進行清理。切記，謹慎。


接下來的六種方式，均採用清理表中部分資料，與開始對全表進行delete略有不同。


2、開啟表的shrink
  1. --開啟行遷移
  2. VAST@prod > alter table ote enable row movement;
  3. Table altered.
  4. --檢視錶的狀態
  5. VAST@prod > select table_name,
  6.   2 tablespace_name,
  7.   3 ROW_MOVEMENT,
  8.   4 read_only,
  9.   5 segment_created
  10.   6 from user_tables
  11.   7 where table_name = 'OTE';
  12. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
  13. ------------------------------ ------------------------------ -------- --- ---
  14. OTE                            VASTTBS                        ENABLED  NO  YES
  15. --開啟shrink
  16. VAST@prod > alter table ote shrink space;
  17. Table altered.
  18. --關閉行遷移
  19. VAST@prod > alter table ote disable row movement;
  20. Table altered.
  21. --檢視錶的狀態
  22. VAST@prod > select table_name,
  23.   2 tablespace_name,
  24.   3 ROW_MOVEMENT,
  25.   4 read_only,
  26.   5 segment_created
  27.   6 from user_tables
  28.   7 where table_name = 'OTE';
  29. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
  30. ------------------------------ ------------------------------ -------- --- ---
  31. OTE                            VASTTBS                        DISABLED NO YES
  32. --收集統計資訊
  33. VAST@prod > analyze table ote compute statistics;
  34. Table analyzed.
  35. --段的資訊
  36. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  37. BYTES/1048576 BLOCKS     EXTENTS
  38. ------------- ---------- ----------
  39.         3.375        432         19
  40. --分析表塊數，空閒塊數，行數
  41. VAST@prod > SELECT blocks, empty_blocks, num_rows
  42.   2 FROM user_tables
  43.   3 WHERE table_name = 'OTE';

  45.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  46. ---------- ------------ ----------
  47.        413           19      29678
  48. --檢查索引以及同義詞是否可用
  49. VAST@prod > col name for a15;
  50. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  51.   2 union all
  52.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

  54. TYPE    NAME            STATUS
  55. ------- --------------- --------
  56. INDEX   IND_OTE_ID      VALID
  57. SYNONYM IND_OTE_ID      VALID
優點：對於表中已清除部分資料依然可用，
缺點：暫不詳

3、CTAS
  1. --建立一張新表
  2. VAST@prod > create table ote1 as select * from ote;
  3. Table created.
  4. --收集統計資訊
  5. VAST@prod > analyze table ote1 compute statistics;
  6. Table analyzed.
  7. --段的資訊
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE1';
  9. BYTES/1048576 BLOCKS EXTENTS
  10. ------------- ---------- ----------
  11.             4        512         19
  12. --分析表塊數，空閒塊數，行數
  13. VAST@prod > SELECT blocks, empty_blocks, num_rows
  14.   2 FROM user_tables
  15.   3 WHERE table_name = 'OTE1';
  16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  17. ---------- ------------ ----------
  18.        429           83      29678
  19. --檢查索引以及同義詞是否可用
  1. 原索引以及同義詞依然可用，但是在同義詞以及索引是指向原OTE表。
  2. 建議：對於這種情況，可以在業務可以停止的時候進行，在ote1表新建對應索引，對應同義詞等。然後rename OTE表，再將OTE1 rename為OTE。又或者將CTAS後將原表truncate，再將資料匯入。

4、使用table move的方式
  1. --使用table move的方式
  2. VAST@prod > alter table ote move tablespace vasttbs;
  3. Table altered.
  4. --收集統計資訊
  5. VAST@prod > analyze table ote compute statistics;
  6. Table analyzed.
  7. --段的資訊
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  9. BYTES/1048576 BLOCKS     EXTENTS
  10. ------------- ---------- ----------
  11.             4        512         19
  12. --分析表塊數，空閒塊數，行數
  13. VAST@prod > SELECT blocks, empty_blocks, num_rows
  14.   2 FROM user_tables
  15.   3 WHERE table_name = 'OTE';
  16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  17. ---------- ------------ ----------
  18.        429           83      29678
  19. --檢查索引以及同義詞是否可用
  20. VAST@prod > col name for a15;
  21. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  22.   2 union all
  23.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  24. TYPE    NAME            STATUS
  25. ------- --------------- --------
  26. INDEX   IND_OTE_ID      UNUSABLE
  27. SYNONYM IND_OTE_ID      VALID
  28. 發現這時,索引已經失效，對索引進行線上重建。
  29. --線上重建索引
  30. VAST@prod > alter index ind_ote_id rebuild online;
  31. Index altered.
  32. --檢查索引以及同義詞是否可用
  33. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  34.   2 union all
  35.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  36. TYPE    NAME            STATUS
  37. ------- --------------- --------
  38. INDEX   IND_OTE_ID      VALID
  39. SYNONYM IND_OTE_ID      VALID
優點：降低了高水位線
缺點：因為表的移動，需要將索引進行重建

5、exp/imp
  1. --exp/imp降低高水位線
  2. [oracle@vedeu ~]$ exp vast/vast file=/home/oracle/ote.dmp tables=ote log=ote.log
  3. Export: Release 11.2.0.3.0 - Production on Mon Jan 23 11:54:49 2017
  4. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  5. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. Export done in US7ASCII character set and AL16UTF16 NCHAR character set
  8. server uses AL32UTF8 character set (possible charset conversion)
  9. About to export specified tables via Conventional Path ...
  10. . . exporting table OTE 29678 rows exported
  11. EXP-00091: Exporting questionable statistics.
  12. EXP-00091: Exporting questionable statistics.
  13. Export terminated successfully with warnings.
  14. --刪除ote表
  15. VAST@prod > drop table ote purge;
  16. Table dropped.
  17. --匯入表
  18. [oracle@vedeu ~]$ imp vast/vast file=/home/oracle/ote.dmp full=y
  19. Import: Release 11.2.0.3.0 - Production on Mon Jan 23 11:56:23 2017
  20. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  21. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  22. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  24. Export file created by EXPORT:V11.02.00 via conventional path
  25. import done in US7ASCII character set and AL16UTF16 NCHAR character set
  26. import server uses AL32UTF8 character set (possible charset conversion)
  27. . importing VAST's objects into VAST
  28. . importing VAST's objects into VAST
  29. . . importing table "OTE" 29678 rows imported
  30. Import terminated successfully without warnings.
  31. --收集統計資訊
  32. VAST@prod > analyze table ote compute statistics;
  33. Table analyzed.
  34. --段的資訊
  35. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  36. BYTES/1048576 BLOCKS     EXTENTS
  37. ------------- ---------- ----------
  38.            4         512         19
  39. --分析表塊數，空閒塊數，行數
  40. VAST@prod > SELECT blocks, empty_blocks, num_rows
  41. 2 FROM user_tables
  42. 3 WHERE table_name = 'OTE';
  43. BLOCKS EMPTY_BLOCKS NUM_ROWS
  44. ---------- ------------ ----------
  45.        496           16      29678
  46. --檢查索引以及同義詞是否可用
  47. VAST@prod > col name for a15;
  48. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  49. 2 union all
  50. 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  52. TYPE    NAME            STATUS
  53. ------- --------------- --------
  54. INDEX   IND_OTE_ID      VALID
  55. SYNONYM IND_OTE_ID      VALID

6、expdp/impdp
    expdp/impdp這種方式也可以降低高水位線，方法類似exp/imp就不在做介紹。
7、線上重定義
  1. --線上重定義
  2. 線上重定義同時也是我比較喜歡的一種方式，因為它可以不停業務，就完成降低高水位線。
  3. --設定主鍵
  4. VAST@prod > alter table ote add constraint pk_ote_id primary key (object_id);
  5. Table altered.
  6. --檢查是否可以線上重定義
  7. VAST@prod > exec dbms_redefinition.can_redef_table( 'VAST','OTE',dbms_redefinition.cons_use_pk);
  8. PL/SQL procedure successfully completed.
  9. --定義中間表
  10. VAST@prod > create table ote_interim as select * from ote where 1=0;
  11. Table created
  12. --開始線上重定義
  13. VAST@prod > exec dbms_redefinition.start_redef_table('VAST','OTE','OTE_INTERIM',
  14.                                                    col_mapping => 'OWNER OWNER,
  15.                                                                    OBJECT_NAME OBJECT_NAME,
  16.                                                                    SUBOBJECT_NAME SUBOBJECT_NAME,
  17.                                                                    OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,
  18.                                                                    OBJECT_TYPE OBJECT_TYPE,
  19.                                                                    CREATED CREATED,
  20.                                                                    LAST_DDL_TIME LAST_DDL_TIME,
  21.                                                                    TIMESTAMP TIMESTAMP,
  22.                                                                    STATUS STATUS,
  23.                                                                    TEMPORARY TEMPORARY,
  24.                                                                    GENERATED GENERATED,
  25.                                                                    SECONDARY SECONDARY,
  26.                                                                    NAMESPACE NAMESPACE,
  27.                                                                    EDITION_NAME EDITION_NAME',
  28.                                                     options_flag => dbms_redefinition.cons_use_pk);
  29. PL/SQL procedure successfully completed.
  30. --處理索引以及約束物件
  31. VAST@prod > declare
  32.   2 error_count number;
  33.   3 begin
  34.   4 error_count := 0;
  35.   5 dbms_redefinition.copy_table_dependents(uname => 'VAST',orig_table => 'OTE',
  36.   6 int_table => 'OTE_INTERIM',
  37.   7 copy_indexes => dbms_redefinition.cons_orig_params,
  38.   8 num_errors => error_count);
  39.   9 dbms_output.put_line(to_char(error_count));
  40.  10 end;
  41.  11 /
  42. PL/SQL procedure successfully completed.
  43. --結束線上重定義
  44. VAST@prod > exec dbms_redefinition.finish_redef_table('VAST','OTE','OTE_INTERIM');
  45. PL/SQL procedure successfully completed.
  46. --收集統計資訊
  47. VAST@prod > analyze table ote compute statistics;
  48. Table analyzed.
  49. --段的資訊
  50. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  51. BYTES/1048576 BLOCKS     EXTENTS
  52. ------------- ---------- ----------
  53.             4        512         19
  54. --分析表塊數，空閒塊數，行數
  55. VAST@prod > SELECT blocks, empty_blocks, num_rows
  56.   2 FROM user_tables
  57.   3 WHERE table_name = 'OTE';
  58.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  59. ---------- ------------ ----------
  60.        429           83      29678
  61. --檢查索引以及同義詞是否可用
  62. col name for a15;
  63. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  64.   2 union all
  65.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  66. TYPE    NAME            STATUS
  67. ------- --------------- --------
  68. INDEX   IND_OTE_ID      VALID
  69. SYNONYM IND_OTE_ID      VALID
優點：降低高水位線的同時，它是這七種辦法中唯一不需要停業務的方式。
建議：生產中大多數時候是沒辦法停止的，即便有停機時間，還是有其他好多操作要進行，線上重定義提供了很大的幫助。然而線上重定義的作用還遠遠不止於此。


-------END-------

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

相關文章