How To Analyze the Wait Statistic: 'enq: HW - contention' [ID 419348.1]

In this Document
Goal
Solution

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later [Release: 10.2 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.

Solution

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

Event	% Wait Time	P1 Parameter	P1 Value	P2 Parameter	P2 Value	Parameter 3	P3 Value
enq: HW - contention	62,81	name|mode	1213661190	table space	4	# block	17005691

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/22308399/viewspace-750998/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22308399/viewspace-750998/