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就表示段的高水位。可以使用下面的语句查看表到底用了多少个blocksselect 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 a35select 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_namewhere 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>100and （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 completedtask_id---------560SQL> col task_name format a8SQL> col segname format a8SQL> col partition format a8SQL> col type format a8SQL> col message format a100SQL> 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 1088IDWSU12.SH08_STJP7T_7001_VIEW 39761320 133 229568455 268620133 709642 90.0101 6164IDWSU14.SH30_DP_SSFV_PROD_REPL 4871971 11 231242749 230333428 5781292 60.6482 132C_2_AA IDWSU14.SH30_DP_SSFV_PROD_REPL 4868627 11 231242749 230333428 5777948 60.6131 130.125C_2_NEA IDWSU14.SH30_DP_SSFV_PROD_REPL 4877610 11 231242749 230333428 5786931 60.7073 130.125C_2_GC IDWSU11.YMCR4_SHIP_HIST_SRC_V 22753157 76 203507302 226332317 -71858 -5.2082 688IDWSU13.SH30_DP_SSFV_PROD_REPL 5638000 11 204891145 204449473 6079672 63.7783 136C_2_WE IDWSU13.SH30_V_CUST_SUBCTRY_67 4232552 35 165554103 167511636 2275019 75.937 4527 IDWSU14.OMCR7_SHIP_HIST_SRC_V 40399302 77 123348208 163744322 3188 0.2341 1136IDWSU14.SH30_V_CUST_SUBCTRY_67 4232198 35 156844906 156528506 4548598 151.8258 4327 ADWU.GDF_SHPMT_EXTRACT_MV 0 0 142410224 142410224 0 0 13884IDWSU10.SYMPH_CHECK_RDS_LOG 24632789 84 90571770 111067817 4136742 331.3888 2143.5IDWSU13.OMCR8_SHIP_HIST_SRC_V 9606113 72 86326358 95986530 -54059 -3.7119 316IDWSU11.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 316ADWU_OPTIMA_AP10.OPT_FUND_DIM_ 43513659 220 34516 43643704 -95529 -20.0428 9580ERR IDWSU11.SH30_DP_SSFV_PROD_REPL 6148402 11 37392566 37279353 6261615 65.687 140C_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 4607 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 3007 IDWSU11.SH30_DP_SSFV_PROD_REPL 6056749 11 12122875 12110928 6068696 63.6632 140C_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 353ADWGU_TFF2.TF_DPSHP_PULL_SFADS 1734816 88 1742583 1743434 1733965 145.5201 6120ADWGU_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 107IDWSU13.DSHB_MCR_GLB3_WE_MC_FC 141849 57 1426032 1427202 140679 7.6472 166.375T IDWSU14.SH30_PRCSS_EXCTN_LOG 107340 133 1602797 1295252 414885 52.6235 106.25ADWU_GPOS.GPOS_DD_WMUS_WHSE_TF 3109445 115 2995416 894588 5210273 571.4239 11537.375ADS ADWU_GPOS.GPOS_CD_ERR 31250000 229 1305866 816661 31739205 6931.57 30428IDWSU14.DSHB_MCR_GLB3_AS_MC_FC 69719 58 535419 539333 65805 3.6399 133.5T IDWSU14.DSHB_MCR_GLB3_AS_MC500 49550 58 397994 397727 49817 2.7555 2249_FCT ADWU.GEO_705_EFSR_DAY_FDIM 793 276 1526 1622 697 0.1835 496ADWU.EFSR_PRMTN_TYPE_LKP 198 15 452 582 68 0.001 396ADWU.EFSR_PRMTN_TYPE_SLKP 198 15 452 582 68 0.001 396ADWU.CUST_GRP_EFSR_DAY_FDIM 221 20 442 442 221 0.0042 496ADWU.CUST_656_EFSR_DAY_FDIM 142 644 296 286 152 0.0934 524IDWSU14.MCR_PLANT_DERIV 1909908 51 42088 193 1951803 94.9306 4069.875 41 rows selectedOracle 10g中 system 回滚段的作用Oracle对字符转义的处理相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）