ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Analyze the Wait Statistic: 'enq: HW - contention' (文档 ID 419348.1)

How To Analyze the Wait Statistic: 'enq: HW - contention' (文档 ID 419348.1)

原创 Linux操作系统 作者:rongshiyuan 时间:2013-09-27 09:16:29 0 删除 编辑
How To Analyze the Wait Statistic: 'enq: HW - contention' (文档 ID 419348.1)
 

In this Document

Goal
Fix

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal

 ***Checked for relevance on 02-Aug-2011*** 

The goal of this article is to explain how to determine which object the 'enq: HW - contention' wait statistic is reported on.

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for "enq: HW - contention" it is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. If this is the case then manual allocation of extents may resolve the issue

Fix

Given the following situation. This information can be retrieved from a statspack, ADDM, ASH report or v$session_wait query:

Event% Wait TimeP1 ParameterP1 ValueP2 ParameterP2 ValueParameter 3P3 Value
enq: HW - contention62,81name|mode1213661190table space4# block17005691

 

1. Determine the correct file and block number:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK#
from dual;

FILE# BLOCK#
---------- ----------
4 228475

2. Determine the object to which this block belongs to

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


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

 

Additionally, if the lock contention is currrently observed, we can find out the underlying segment using the following query:


select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
from v$lock
where type = 'HW';




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-773446/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3198652