enq: HW – contention

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”.

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.

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.

We identify the lob segment with datafile and block information.

We find the table where the detected lob segment belongs from the dba_lobs image .

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

2 comments

  1. Hi

    What if the segment_type is a table subpartition and not a lob?

    Thanks

    Jose Martins

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *