Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Enqueues are shared memory structures that serialize access to database resources.(locks) They can be associated with a session or transaction.
HW (high water mark) is the bound between used and unused areas in the segment.
If AWR reports show a high number of “enq: HW – contention” pending events, the issue needs to be fixed. It is generally seen in insert operations performed on tables containing lob fields in the form of basicfile.
To correct the problem, the lob field in the corresponding table must be changed from basicfile to securefile.
The following steps can be followed to identify the problematic objects.
First of all, we detect event ids related to “enq: HW – contention”.
1 2 3 4 5 6 7 8 9 10 11 | SQL> SELECT event_id, name, parameter1, parameter2, parameter3 FROM v$event_name WHERE name = 'enq: HW - contention'; EVENT_ID NAME PARAMETER PARAMETER PARAMETER3 ---------- ---------------------- ------------------- ------------------- ------------------- 1645217925 enq: HW - contention name|mode table space # block |
Then, from the dba_hist_active_sess_history view, we determine the necessary information for the detection of file and block information about the event id.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> SELECT event, p1, p2, p3, COUNT (1) FROM dba_hist_active_sess_history WHERE event_id = 1645217925 GROUP BY event, p1, p2, p3; 2 3 4 5 6 7 8 9 10 11 EVENT P1 P2 P3 COUNT(1) ---------------------------------------------------------------- ---------- ---------- ---------- ---------- enq: HW - contention 1213661190 25 17761024 1 enq: HW - contention 1213661190 0 4294200 61 enq: HW - contention 1213661190 38 853351013 118356 enq: HW - contention 1213661190 38 373735013 1 |
With this information, the file and block information is detected with the dbms_utility package. The information used in the package is the information in the P3 field in the query above. The lob segment, which has too many waits, needs to be identified.
1 2 3 4 5 6 7 | SQL> SELECT DBMS_UTILITY.Data_block_address_file (853351013) FILE#, DBMS_UTILITY.Data_block_address_block (853351013) BLOCK# FROM DUAL; 2 3 FILE# BLOCK# ---------- ---------- 203 1907301 |
We identify the lob segment with datafile and block information.
1 2 3 4 5 6 7 | SQL> SELECT owner, segment_type, segment_name 2 FROM dba_extents 3 WHERE file_id = 203 AND 1907301 BETWEEN block_id AND block_id + blocks - 1; OWNER SEGMENT_TYPE SEGMENT_NAME ------------------------------ ------------------ --------------------------------------------------------------------------------- ADURUOZ LOB PARTITION SYS_LOB0000291772C00006$ |
We find the table where the detected lob segment belongs from the dba_lobs image .
1 2 3 4 5 6 7 8 9 10 11 | SQL> SELECT owner, table_name, column_name, chunk, securefile FROM dba_lobs WHERE segment_name = 'SYS_LOB0000291772C00006$'; OWNER TABLE_NAME COLUMN_NAME CHUNK SEC ---------------- --------------- --------------- --------------- --------------- ADURUOZ TESTTABLE DOSYA 8192 NO |
From the output of the above query, the relevant table is determined and it is seen that the lob field of this table is not securefile.
The main reason for this wait event is that the lob field is a basic file. To correct the problem, the corresponding lob field must be changed to securefile.
Hi
What if the segment_type is a table subpartition and not a lob?
Thanks
Jose Martins
This recommendation covers most use cases, specifically when relative_fno = file_id (which is NOT always the case).
Consider this 1 query below to answer most you questions
SELECT a.*,
(SELECT owner
|| ‘.’
|| object_name
|| ‘ – (‘
|| object_type
|| ‘-‘
|| NVL (subobject_name, ‘ ‘)
|| ‘)’
FROM dba_objects
WHERE object_id = CURRENT_OBJ#) OBJECT_NAME,
(SELECT x.tablespace_name || ‘ – ‘ || x.file_name
FROM dba_data_files x
INNER JOIN sys.ts$ y ON (y.name = x.tablespace_name)
WHERE x.relative_fno = a.relative_fno AND y.ts# = a.ts#) INFO
FROM ( SELECT COUNT (*) cnt,
p1,
p2 ts#,
p3,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (p3) relative_fno,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (p3) BLOCK#,
inst_id,
sql_id,
CURRENT_OBJ#
FROM GV$ACTIVE_SESSION_HISTORY
WHERE sql_id IS NOT NULL AND event = ‘enq: HW – contention’
GROUP BY p1,
p2,
p3,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (p3),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (p3),
inst_id,
sql_id,
CURRENT_OBJ#) a
ORDER BY 1 DESC