Oracle段高水位(HWM, high water mark)問題

Oracle對錶做全表掃描的時候，會掃描完HWM以下的資料塊。如果某個表delete(delete操作不會降低高水位)了大量資料，那麼這時對錶做全表掃描就會做很多無用功，掃描了一大堆資料塊，最後發現塊裡面居然沒有資料。

通常，在對錶做了大批量delete操作之後，就應該馬上降低表的高水位，可以使用shrink 命令或者alter table table_name move降低表的高水位。在降低表的高水位之後，表上面的索引會失效，因為表的rowid更改了，這個時候需要rebuild索引。

如何求出段的高水位？其實很簡單，首先對錶收集統計資訊，然後查詢DBA_TABLES的blocks，以及empty_blocks欄位，blocks表示已經用了多少個blocks,empty_blocks表示從來沒有使用過的blocks。那麼blocks就表示段的高水位。

可以使用下面的語句檢視錶到底用了多少個blocks

select count( distinct dbms_rowid.rowid_block_number(rowid)) from table_name;

然後再對比dba_tables表中的blocks列，如果求出的blocks數與dba_tables相差在10左右，那麼表示這個表不需要shrink,用上面的指令碼求出的blocks數沒計算段頭，點陣圖管理塊。如果相差很大，那麼表示這個表需要shrink了，不過這樣做比較麻煩，不是嗎？

其實還可以監控表的DML操作，根據監控的結果，我們就可以判斷哪些表需要降低高水位。在Oracle10g中DBA_TAB_MODIFICATIONS這個檢視記錄了自上次收集統計資訊以來表的DML操作資訊。當我們再次對錶收集統計資訊，該檢視的記錄就會被清空。

對於非分割槽表，可以使用下面指令碼初步檢測哪些表需要降低高水位

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

col table format a35

select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,

round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"

from dba_tables a left join all_tab_modifications b

on a.owner=b.table_owner and a.table_name=b.table_name inner join dba_segments c on a.owner=c.owner and a.table_name=c.segment_name

where a.last_analyzed is not null and a.partitioned='NO' and b.deletes>100 and a.owner not like ‘%SYS%' and c.bytes/1024/1024>100

and (a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0)))/c.bytes<0.5 order by b.deletes desc;

上面的指令碼查詢收集過統計資訊的非分割槽表，並且delete超過100，段大小超過100M，num_rows*avg_row_len/段大小小於0.5的表。這裡解釋一下為什麼要用num_rows*avg_row_len，因為只看insert,delete是不夠的。比如先對錶insert,再delete，這個時候需要shrink表，但是如果先delete,再insert就不需要shrink表了，所以我引入了num_rows*avg_row_len比上段大小作為參考依據。

注意，使用上面的指令碼查詢出來total_rows可能是負數，那表明你對錶收集統計資訊有問題。

你可以適當修改上面的指令碼,比如設定deletes>0，段大小超過200M,比值小於0.3等等。利用上面指令碼查詢出可疑的表之後，可以最開始講的方法檢查是否要shrink表，你也可以用Segment Advisor來檢查是否需要shrink這個表。Segment Advisor使用方法：

SQL> variable task_id number;

SQL> begin

2 declare

3 object_id number;

4 name varchar2(100);

5 task_desc varchar2(100);

6 begin

7 name := 'test';

8 task_desc := 'Segment Advisor TEST';

9 dbms_advisor.create_task(advisor_name => 'Segment Advisor',

10 task_id => :task_id,

11 task_name => name,

12 task_desc => task_desc

13 );

14 dbms_advisor.create_object(task_name => name,

15 object_type => 'TABLE',

16 attr1 => 'ROBINSON',

17 attr2 => 'TEST',

18 attr3 => NULL,

19 attr4 => NULL,

20 attr5 => NULL,

21 object_id =>object_id

22 );

23 dbms_advisor.set_task_parameter(task_name => name,

24 parameter => 'recommend_all',

25 value => 'TRUE'

26 );

27 dbms_advisor.execute_task(task_name => name);

28 end;

29 end;

30 /

PL/SQL procedure successfully completed

task_id

---------

560

SQL> col task_name format a8

SQL> col segname format a8

SQL> col partition format a8

SQL> col type format a8

SQL> col message format a100

SQL> select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

2 from dba_advisor_findings af, dba_advisor_objects ao

3 where ao.task_id = af.task_id

4 and ao.object_id = af.object_id

5 and ao.owner = 'ROBINSON';

TASK_NAM SEGNAME PARTITIO TYPE MESSAGE

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

test TEST TABLE Enable row movement of the table ROBINSON.TEST and perform. shrink, estimated savings is 28451785 bytes.

下面是一個生產環境資料庫，可以看到有很多表都需要shrink,我就不貼出具體的處理步驟了。

SQL> select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,

2 round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"

3 from dba_tables a left join all_tab_modifications b

4 on a.owner=b.table_owner and a.table_name=b.table_name inner join dba_segments c on a.owner=c.owner and a.table_name=c.segment_name

5 where a.last_analyzed is not null and a.partitioned='NO' and b.deletes>100 and c.bytes/1024/1024>100 and a.owner not like '%SYS%'

6 and (a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0)))/c.bytes<0.5 order by b.deletes desc;

Table NUM_ROWS AVG_ROW_LEN INSERTS DELETES TOTAL_ROWS Calculate_Sizle(Mb) Segment_Size(Mb)

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

IDWSU14.OMCR4_SHIP_HIST_SRC_V 39370237 77 805667850 884712377 -39674290 -2913.3991 1088

IDWSU12.SH08_STJP7T_7001_VIEW 39761320 133 229568455 268620133 709642 90.0101 6164

IDWSU14.SH30_DP_SSFV_PROD_REPL 4871971 11 231242749 230333428 5781292 60.6482 132

C_2_AA

IDWSU14.SH30_DP_SSFV_PROD_REPL 4868627 11 231242749 230333428 5777948 60.6131 130.125

C_2_NEA

IDWSU14.SH30_DP_SSFV_PROD_REPL 4877610 11 231242749 230333428 5786931 60.7073 130.125

C_2_GC

IDWSU11.YMCR4_SHIP_HIST_SRC_V 22753157 76 203507302 226332317 -71858 -5.2082 688

IDWSU13.SH30_DP_SSFV_PROD_REPL 5638000 11 204891145 204449473 6079672 63.7783 136

C_2_WE

IDWSU13.SH30_V_CUST_SUBCTRY_67 4232552 35 165554103 167511636 2275019 75.937 452

7

IDWSU14.OMCR7_SHIP_HIST_SRC_V 40399302 77 123348208 163744322 3188 0.2341 1136

IDWSU14.SH30_V_CUST_SUBCTRY_67 4232198 35 156844906 156528506 4548598 151.8258 432

7

ADWU.GDF_SHPMT_EXTRACT_MV 0 0 142410224 142410224 0 0 13884

IDWSU10.SYMPH_CHECK_RDS_LOG 24632789 84 90571770 111067817 4136742 331.3888 2143.5

IDWSU13.OMCR8_SHIP_HIST_SRC_V 9606113 72 86326358 95986530 -54059 -3.7119 316

IDWSU11.SH28_GKC_DP3606_PR9005 11369578 19 47034509 53631501 4772586 86.4784 356

_1

IDWSU11.MCR_MV_SRCE_PLANT_BW 1384608 31 47708177 47676037 1416748 41.8846 316

ADWU_OPTIMA_AP10.OPT_FUND_DIM_ 43513659 220 34516 43643704 -95529 -20.0428 9580

ERR

IDWSU11.SH30_DP_SSFV_PROD_REPL 6148402 11 37392566 37279353 6261615 65.687 140

C_1

ADWGU_IVC_RSTMT.IVC_EFRTN_RULE 17280240 67 19963487 35209550 2034177 129.9761 2797.375

_PLC

IDWSU11.SH28_GKC_DP3602_PR9005 4741685 19 23071830 26059892 1753623 31.7753 192

_1

IDWSU12.SH08_STJP2D_MVIEW 4664224 33 20742310 25330983 75551 2.3777 224

Table NUM_ROWS AVG_ROW_LEN INSERTS DELETES TOTAL_ROWS Calculate_Sizle(Mb) Segment_Size(Mb)

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

IDWSU11.SH30_V_CUST_SUBCTRY_67 4508160 35 20378604 22611745 2275019 75.937 460

7

ADWGU_IVC_RSTMT.IVC_RSTMT_RULE 17218047 60 4784752 20724823 1277976 73.1264 1080

_AS_PLC

IDWSU12.SH30_V_CUST_SUBCTRY_67 4232552 35 10624250 12719507 2137295 71.3399 300

7

IDWSU11.SH30_DP_SSFV_PROD_REPL 6056749 11 12122875 12110928 6068696 63.6632 140

C_1_LA

IDWSU12.SH30_V_PROD_BOM_DENORM 1987829 54 6009188 7987214 9803 0.5048 204

_FAC

IDWSU29.R3_EQSCN_ATTR_RPT_FACT 2298703 143 1436116 3148585 586234 79.9479 353

ADWGU_TFF2.TF_DPSHP_PULL_SFADS 1734816 88 1742583 1743434 1733965 145.5201 6120

ADWGU_TFF2.TF_DPSHP_UNOFL_PULL 1738361 88 1721664 1740119 1719906 144.3403 4440

_SFADS

IDWSU29.R3_EQSCN_ATTR_FACT 610243 165 1155068 1475080 290231 45.6697 107

IDWSU13.DSHB_MCR_GLB3_WE_MC_FC 141849 57 1426032 1427202 140679 7.6472 166.375

T

IDWSU14.SH30_PRCSS_EXCTN_LOG 107340 133 1602797 1295252 414885 52.6235 106.25

ADWU_GPOS.GPOS_DD_WMUS_WHSE_TF 3109445 115 2995416 894588 5210273 571.4239 11537.375

ADS

ADWU_GPOS.GPOS_CD_ERR 31250000 229 1305866 816661 31739205 6931.57 30428

IDWSU14.DSHB_MCR_GLB3_AS_MC_FC 69719 58 535419 539333 65805 3.6399 133.5

T

IDWSU14.DSHB_MCR_GLB3_AS_MC500 49550 58 397994 397727 49817 2.7555 224

9_FCT

ADWU.GEO_705_EFSR_DAY_FDIM 793 276 1526 1622 697 0.1835 496

ADWU.EFSR_PRMTN_TYPE_LKP 198 15 452 582 68 0.001 396

ADWU.EFSR_PRMTN_TYPE_SLKP 198 15 452 582 68 0.001 396

ADWU.CUST_GRP_EFSR_DAY_FDIM 221 20 442 442 221 0.0042 496

ADWU.CUST_656_EFSR_DAY_FDIM 142 644 296 286 152 0.0934 524

IDWSU14.MCR_PLANT_DERIV 1909908 51 42088 193 1951803 94.9306 4069.875

41 rows selected

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

Oracle段高水位(HWM, high water mark)問題

相關文章