Combat with an enqueue wait event (enq: HW–Contention)

Recently I’ve been combating a high water mark enqueue wait (enq: HW – contention) on a single node within an Exadata I’m supporting.   I first noticed the wait when I was looking at the performance page for the node in Oracle Enterprise Manager 12c.  What I noticed was the a lot of brown looking spikes (Image 1).  These spikes correspond to a Configuration wait.

Image 1:

When I clicked on Configuration in the legend on the side of the graph, I’m taken to the Active Session Waiting: Configuration  (Image 2)  page.  On this page, I can clearly see that the wait event I’m waiting on is the “enq: HW – contention” wait.

Image 2:

Now, that I know what wait event I’m waiting on, I needed to figure out what object was causing the wait.  Everyone has a different approach to how they performance tune; I find it easier to identify objects causing the wait by using the Active Session History (ASH) report.  ASH reports can be ran directly from the OEM Active Session Waiting page (page with the graphs) or from the command line.   Since I like the pretty graphs that OEM provides, I prefer to run ASH from the command line and use the graphs to help identify what direction my tuning is going.

Active Session History (ASH) Report

The Active Session History (ASH) report is handy in helping to identify what object(s) the wait is waiting on.  In order to run the ASH Report from the command line,  there are a few requirements needed.  They are:

• Set ORACLE_HOME
• Move to the directory where you would like to save the report

Once these requirements have been meet, the ASH Report can be ran from the command line using the ashrpt.sql script. 

                SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sh

When the script starts to run, it will ask for input on what format you would like the report in. For search ability purposes (from command line), I go with the “Text” option.   Next, it asks for a timeframe (Image 3); provide a timeframe as per the layout listed.

Image 3:

Lastly, it will ask you what you would like to name the report.  I normally prefix it with my initials and an underscore (bc_) (Image 4). 

Image 4:

Once I press enter, the report runs and the output will be stored in the directory you started SQL*Plus from.

Reading the ASH Report

In reading the ASH Report, I want to identify the primary object that is holding the “enq: HW – contention” wait.   In order to do this, I need to look at the “Top Event P1/P2/P3 Values” section of the report (Image 5).  Reviewing this section, I see that the Top Event is the “enq: HW – contention”, the same as I saw from the OEM performance pages.  Next, I need to find the values of P2 and P3.   

Image 5:

The values for P2 and P3 will tell me what data file (P2) to look at for the block (P3) holding the wait.  The values P2 & P3 for the wait are currently “85” and “16018”.

Finding the Object

Armed with the values for P2 and P3 from the ASH Report, I can now go and locate the object via SQL.  The SQL used to identify the object is as follows:

select /*+ parallel(a dba_extents, 4) */ a.owner, a.segment_type,a.segment_name
from DBA_EXTENTS a
where a.FILE_ID = &P2
and &P3 between a.block_id and a.block_id + blocks – 1;

Note: I’m using a parallel hint; this is to speed of the identification of the block.  Can help in VLDBs. 

Once the script is done running, it returns the owner, segment_type, and segment_name of the object holding the enq: HW – contention wait (Image 6).  In this case, the wait is on a system generated LOB index. 

Image 6:

Resolving the Wait

At this point, I have identified the following:

• The wait: enq: HW – contention
• The object holding the wait

Next, I want to resolve the wait as fast as I can.  Knowing that the wait is a high watermark (enq: HW –contention) wait event, there needs to be an basic understanding of high watermarks and how extents are allocated.  The basic reason why the ‘enq: HW – contention’ raises is because the  additional extents are not being allocated to in a timely manner for the data being inserted into the object. 

In order to resolve this wait, additional extents need to be allocated.  The environment I’m working in is using big file data files with automatic segment space management (ASSM).  When initially looking into how to increase the extents; there are many different ways.  Being that this database is using ASSM, makes the extent allocation simpler.  To allocate another extent for the object identified, use the following:

             SQL> alter index <owner>.SYS_IL0000253792C00002$$ allocate extent;

The above alter statement is correct; however, the problem is that I was trying to allocate an extent on a system generated index.  The below error message states that this is not allowed:

Error starting at line : 32 in command –
alter index <OWNER>.SYS_IL0000253792C00002$$ allocate extent
Error report –
SQL Error: ORA-22864: cannot ALTER or DROP LOB indexes
22864. 00000 –  “cannot ALTER or DROP LOB indexes”
*Cause:    An attempt was made to ALTER or DROP a LOB index.
*Action:   Do not operate directly on the system-defined LOB index.
           Perform operations on the corresponding LOB column.

In order to add extents, I needed to identify the object that is using the system generated LOB index.  This inform
ation can be found in DBA_LOBS (SQL below).

select owner, table_name, column_name, segment_name, index_name
from DBA_LOBS
where index_name = ‘SYS_IL0000253792C00002$$’;

The SQL returns the name of the owner, table_name and LOB column within the table that is using the system generated LOB index (Image 7).   Using this information, I can now allocate additional extents to the LOB.

Image 7: 

In order to allocate an extent on the object identified, the following was used:

alter table <owner>.<table> modify lob(<column>) (allocate extent (size 5M));

Once this completed, I was expecting wait (enq: HW – contention) to go away.  After sometime, I saw no improvement.  This lead me to open an service request (SR) with Oracle Support in trying to resolve. 

Research

Opening the SR has lead me to dig a bit and do some research on extent allocation and LOBs while waiting on Oracle’s answer.  Prior to Oracle Database 11g (11.2.0.1), there is a known bug (6376915).  This bug is related to high watermark enqueue contention for ASSM LOB segments (Note ID: 6376915.8).  This bug was actually fixed in release 11.2.0.1 but it needs to be “turned on” in later releases.  To turn the fix for this bug on in 11.2.0.2+, an event needs to be set in the spfile.

SQL> ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>” scope=spfile;

By setting this event between 1 and 1024 will identify the number of chunks to be cleaned up each time a reclamation operation is performed.  In turn this reduces the number of requests against the high watermark enqueue. 

Once the event has been set in the spfile, the database instance needs to be rebooted to make the change effective. 

Conclusion

The steps in resolving this enqueue event are not difficult; however, when extents need to be allocated manually it becomes very interesting.  The downside to setting the event, per Oracle Support, is the need to reboot the database instance to make the event active.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: