免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1985 | 回复: 0

如何诊断高水位争用(enq: HW - contention)for lob [复制链接]

论坛徽章:
0
发表于 2011-12-22 08:53 |显示全部楼层
上文讲到了高水位推进的情况,当并发会话同时进行insert时,极易引起高水位争用enq: HW - contention,那么发生此类争用时,该如何诊断呢? 
查看v$session_wait,应该会有如下等待事件: 

SQL>select event,p1,p2,p3 from v$session_wait; 
EVENT                     P1         P2      P3 
---------------------- --------   ------- ---------- 
enq: HW - contention 1213661190  4 17005691 

通过P3进行DBMS_UTILITY转换可以获知发生争用的文件和block 
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#, 
  2  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK# 
  3  from dual; 

     FILE#     BLOCK# 
---------- ---------- 
         4     228475 
         
进而通过file#和block#定位对象 

SQL> select owner, segment_type, segment_name 
  2  from dba_extents 
  3  where file_id = 4 
  4  and 228475 between block_id and block_id + blocks - 1; 


OWNER SEGMENT_TYPE SEGMENT_NAME 
--------------- --------------- ------------------------------ 
SCOTT LOBSEGMENT EMP_DATA_LOB 

我们知道enqueue锁的p2,p3值与v$lock的id1,id2值相同,同样通过id2,也可以知道发生争用的文件和block 
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#, 
  2  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK# 
  3  from v$lock 
  4  where type = 'HW'; 
      FILE#     BLOCK# 
---------- ---------- 
         4     228475 

通过p1值可以知道锁类型和模式 

SQL> select chr(bitand(1213661190,-16777216)/16777215)|| 
  2  chr(bitand(1213661190,16711680)/65535) "Lock",to_char( bitand(1213661190, 65535) ) "Mode" from dual; 
Lock       Mode 
---------- ---------- 
HW         6 

当知道了,lob对象发生高水位争用时,该怎么办呢?metalink(740075.1)为我们提供了几种解决办法,仅供参考 
引用
1. When using Automatic Segment Space Management (ASSM) 

   a) As temporary workaround, manually add extra space to the LOB segment 
      ALTER TABLE <lob_table> 
      MODIFY LOB (<column_name>) (allocate extent (size <extent size>)); 
OR 
   b) It may related Bug 6376915. 
   Refer to Note 6376915.8 "Bug 6376915 HW enqueue contention for ASSM LOB segments" 
   In 10.2.0.4 or above, this fix has been included, and can be enabled by setting event 44951 to a value 
   between 1 and 1024.  A higher value would be more beneficial in reducing contention. 
   EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL < 1 - 1024 >" 
OR 
  c) Consider partitioning the LOB  in a manner that will evenly distribute concurrent DML across multiple partitions  

2. When using Manual Segment Space Management(MSSM) 

a) As temporary workaround, manually add extra space to the LOB segment 
    ALTER TABLE <lob_table>    
    MODIFY LOB (<column_name>) (allocate extent (size <extent size>)); 
OR 
     b) Consider partitioning the LOB in a manner that will evenly distribute concurrent DML across multiple partitions

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP