Enqueues are shared memory structures that serialize access to database resources.(locks) They can be associated with a session or transaction.<\/p>\n
HW (high water mark) is the bound between used and unused areas in the segment.<\/p>\n
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.<\/p>\n
To correct the problem, the lob field in the corresponding table must be changed from basicfile to securefile.<\/p>\n
The following steps can be followed to identify the problematic objects.<\/p>\n
First of all, we detect event ids related to “enq: HW – contention”.<\/p>\n
SQL> SELECT event_id,\n name,\n parameter1,\n parameter2,\n parameter3\n FROM v$event_name\n WHERE name = 'enq: HW - contention';\n\n EVENT_ID NAME \t\t\t\t PARAMETER PARAMETER PARAMETER3\n---------- ---------------------- ------------------- ------------------- -------------------\n1645217925 enq: HW - contention name|mode\t table space # block<\/pre>\nThen, 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.<\/p>\n
SQL> SELECT event,\n p1,\n p2,\n p3,\n COUNT (1)\n FROM dba_hist_active_sess_history\n WHERE event_id = 1645217925\nGROUP BY event,\n p1,\n p2,\n p3; 2 3 4 5 6 7 8 9 10 11 \n\nEVENT\t\t\t\t\t\t\t\t\t P1\t P2\t P3 COUNT(1)\n---------------------------------------------------------------- ---------- ---------- ---------- ----------\nenq: HW - contention\t\t\t\t\t\t 1213661190\t 25\t 17761024\t 1\nenq: HW - contention\t\t\t\t\t\t 1213661190\t 0\t 4294200\t 61\nenq: HW - contention\t\t\t\t\t\t 1213661190\t 38\t853351013 118356\nenq: HW - contention\t\t\t\t\t\t 1213661190\t 38\t373735013\t 1<\/pre>\nWith 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.<\/p>\n
SQL> SELECT DBMS_UTILITY.Data_block_address_file (853351013) FILE#,\n DBMS_UTILITY.Data_block_address_block (853351013) BLOCK#\n FROM DUAL; 2 3 \n\n FILE# BLOCK#\n---------- ----------\n 203 1907301<\/pre>\nWe identify the lob segment with datafile and block information.<\/p>\n
SQL> SELECT owner, segment_type, segment_name\n 2 FROM dba_extents\n 3 WHERE file_id = 203 AND 1907301 BETWEEN block_id AND block_id + blocks - 1;\n\nOWNER\t\t\t SEGMENT_TYPE\t SEGMENT_NAME\n------------------------------ ------------------ ---------------------------------------------------------------------------------\nADURUOZ\t\t LOB PARTITION\t SYS_LOB0000291772C00006$<\/pre>\nWe find the table where the detected lob segment belongs from the dba_lobs image .<\/p>\n
SQL> SELECT owner,\n table_name,\n column_name,\n chunk,\n securefile\n FROM dba_lobs\n WHERE segment_name = 'SYS_LOB0000291772C00006$';\n\nOWNER\t\t\t TABLE_NAME\t\t COLUMN_NAME\t CHUNK\t\t\t SEC\n---------------- --------------- --------------- --------------- ---------------\nADURUOZ\t\t TESTTABLE DOSYA\t\t\t 8192 \t\t\t NO<\/pre>\nFrom the output of the above query, the relevant table is determined and it is seen that the lob field of \u200b\u200bthis table is not securefile.<\/p>\n
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.<\/p>\n\n
<\/div>\n