This wait can have an impact to production and deteriorate performance on a DB, so lets investigate…
Lets see the output from a AWR report where this event is 23% of the total wait time:
Top Timed Events DB/Inst: PROD_DB/PROD_DB1 Snaps: 32287-32288
-> Instance '*' - cluster wide summary
-> '*' Waits, %Timeouts, Wait Time Total(s) : Cluster-wide total for the wait event
-> '*' 'Wait Time Avg (ms)' : Cluster-wide average computed as (Wait Time Total / Event Waits) in ms
-> '*' Summary 'Avg Wait Time (ms)' : Per-instance 'Wait Time Avg (ms)' used to compute the following statistics
-> '*' [Avg/Min/Max/Std Dev] : average/minimum/maximum/standard deviation of per-instance 'Wait Time Avg(ms)'
-> '*' Cnt : count of instances with wait times for the event
Wait Event Wait Time Summary Avg Wait Time (ms)
--------------------------------------------------- ---------------------- ------------------------------ ----------------------------------------
I# Class Event Waits %Timeouts Total(s) Avg(ms) %DB time Avg Min Max Std Dev Cnt
---- ---------- ---------------------------------------- ------------ --------- ------------- ------- -------- -------- -------- -------- -------- ----
* Configurat enq: HW - contention 897,229 3.6 526,247.20 586.5 23.78 719.44 258.25 1180.63 652.22 2
Other block change tracking buffer space 475,058 89.0 444,324.44 935.3 20.08 939.31 933.58 945.03 8.10 2
DB CPU N/A N/A 409,874.75 N/A 18.52 2
Concurrenc buffer busy waits 12,345,097 0.0 187,904.05 15.2 8.49 17.68 14.25 21.10 4.84 2
Cluster gc buffer busy acquire 12,999,693 0.2 128,460.30 9.9 5.80 8.66 6.59 10.74 2.93 2
Other cr request retry 11,189,744 97.5 103,385.16 9.2 4.67 9.26 9.23 9.28 0.03 2
User I/O cell single block physical read 289,619,915 0.0 82,946.50 0.3 3.75 0.29 0.29 0.29 0.00 2
Cluster gc buffer busy release 654,707 0.2 46,417.12 70.9 2.10 70.36 48.51 92.21 30.90 2
Concurrenc enq: TX - index contention 351,843 0.0 45,980.36 130.7 2.08 129.05 97.22 160.89 45.02 2
Concurrenc latch: cache buffers chains 12,476,971 0.0 33,659.74 2.7 1.52 2.92 2.61 3.22 0.43 2As per “Analyzing ‘enq: HW – contention’ Wait Event (Doc ID 740075.1)”, this can be generated by multiple sessions fighting each other allocating space.
Lets see what object was generating this issue:
SQL> SELECT event_id, name, parameter1, parameter2, parameter3
FROM v$event_name
WHERE name = 'enq: HW - contention';
EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- -------------------------- ----------------- -------------------- -------------
1645217925 enq: HW - contention name|mode table space # block
SQL> SELECT event, p1, p2, p3, Count(1)
FROM dba_hist_active_sess_history
WHERE event_id = 1645217925
GROUP BY event, p1, p2, p3
order by 5;
EVENT P1 P2 P3 COUNT(1)
------------------------------ ---------- ---------- ---------- ----------
...
enq: HW - contention 1213661190 17 25241170 39
enq: HW - contention 1213661190 22 448829394 51
enq: HW - contention 1213661190 17 349234098 52
enq: HW - contention 1213661190 17 25330626 127
enq: HW - contention 1213661190 0 4194432 282
enq: HW - contention 1213661190 17 1677529874 438
enq: HW - contention 1213661190 17 1520175170 777
enq: HW - contention 1213661190 17 1660762946 897
enq: HW - contention 1213661190 16 4278533507 1027
enq: HW - contention 1213661190 17 156612530 2822
enq: HW - contention 1213661190 19 234750090 18554
enq: HW - contention 1213661190 17 349104930 255525 <<<---------
715 rows selected.
SQL>
What file/block?
SQL> SELECT
dbms_utility.Data_block_address_file(349104930) FILE#,
dbms_utility.Data_block_address_block(349104930) BLOCK#
FROM dual;
FILE# BLOCK#
---------- ----------
83 977698
SELECT owner, segment_type, segment_name, PARTITION_NAME
FROM dba_extents
WHERE file_id = 83
AND 977698 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME
------------------ ------------------ ------------------------ ------------------------------
TEST_USER TABLE PARTITION TABLE_AFFECTED SYS_P1871So problem is generated by Partition SYS_P1871.
Lets see the extents allocated to this partition:
col OWNER format a10
col PARTITION_NAME format a20
col TABLESPACE_NAME format a20
select OWNER, SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME,BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_SIZE from dba_segments where segment_name ='DETB_RTL_TELLER' and PARTITION_NAME = 'SYS_P1871' ;
OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_SIZE
---------- -------------------- ---------------- ---------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 9096396800 1110400 1735 5242880 5242880 1 2147483645
We can see this partition has many extents across many datafiles:
SQL> SELECT owner, segment_name, PARTITION_NAME, tablespace_name, file_id,
block_id AS "FROM BLOCK", block_id+blocks AS "TO BLOCK"
FROM dba_extents
WHERE segment_name = 'TABLE_AFFECTED' AND owner = 'TEST_USER' and PARTITION_NAME = 'SYS_P1871';
OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME FILE_ID FROM BLOCK TO BLOCK
---------- -------------------- -------------------- -------------------- ---------- ---------- ----------
...
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5325 35848 36488
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5331 42888 43528
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5334 43528 44168
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5340 29448 30088
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5346 42248 42888
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5353 13448 14088
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5354 23048 23688
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5360 29448 30088
TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5365 44808 45448
1735 rows selected.
SQL> How to fix? We can preallocate extents so sessions are not waiting for the space to be allocated.
We have 2 options:
1:- Preallocate across all datafiles:
SQL> ALTER TABLE TEST_USER.TABLE_AFFECTED MODIFY PARTITION SYS_P1871 ALLOCATE EXTENT (size 100M );
Table altered.
SQL>
2:- Preallocate on a specific datafile:
SQL> select FILE_ID, FILE_NAME, autoextensible, bytes/1024/1024/1024 as "GB" , maxbytes/1024/1024/1024 as "Max GB" from dba_data_files where FILE_ID = 5365;
FILE_ID FILE_NAME AUT GB Max GB
---------- ----------------------------------------------------------- --- ---------- ----------
5365 +DATAFC01/PROD_DB/datafile/USERS.5791.1074924187 YES 10 31.9999847
SQL> ALTER TABLE TEST_USER.TABLE_AFFECTED MODIFY PARTITION SYS_P1871 ALLOCATE EXTENT (size 100M DATAFILE '+DATAFC01/PROD_DB/datafile/USERS.5791.1074924187');
Table altered.
SQL>
After this change, wait event is gone:
awrrpt_rac_32293_32294.txt:
Top Timed Events DB/Inst: PROD_DB/PROD_DB1 Snaps: 32293-32294
-> Instance '*' - cluster wide summary
-> '*' Waits, %Timeouts, Wait Time Total(s) : Cluster-wide total for the wait event
-> '*' 'Wait Time Avg (ms)' : Cluster-wide average computed as (Wait Time Total / Event Waits) in ms
-> '*' Summary 'Avg Wait Time (ms)' : Per-instance 'Wait Time Avg (ms)' used to compute the following statistics
-> '*' [Avg/Min/Max/Std Dev] : average/minimum/maximum/standard deviation of per-instance 'Wait Time Avg(ms)'
-> '*' Cnt : count of instances with wait times for the event
Wait Event Wait Time Summary Avg Wait Time (ms)
--------------------------------------------------- ---------------------- ------------------------------ ----------------------------------------
I# Class Event Waits %Timeouts Total(s) Avg(ms) %DB time Avg Min Max Std Dev Cnt
---- ---------- ---------------------------------------- ------------ --------- ------------- ------- -------- -------- -------- -------- -------- ----
* DB CPU N/A N/A 576,719.97 N/A 77.33 2
User I/O cell single block physical read 254,058,086 0.0 74,271.48 0.3 9.96 0.29 0.29 0.29 0.00 2
Commit log file sync 4,060,020 0.0 54,537.90 13.4 7.31 13.49 1.14 25.85 17.48 2
Cluster gc cr grant 2-way 114,312,467 0.0 19,046.61 0.2 2.55 0.17 0.16 0.17 0.01 2
Cluster gc current block 2-way 79,873,390 0.0 14,897.24 0.2 2.00 0.19 0.18 0.19 0.01 2
Concurrenc latch: cache buffers chains 5,473,596 0.0 6,056.18 1.1 0.81 1.09 0.68 1.50 0.58 2
Cluster gc buffer busy acquire 6,196,635 0.0 2,230.30 0.4 0.30 0.40 0.33 0.47 0.10 2
Cluster gc cr block 2-way 7,759,649 0.0 1,959.33 0.3 0.26 0.25 0.25 0.26 0.01 2
User I/O cell multiblock physical read 988,210 0.0 1,507.00 1.5 0.20 1.39 1.03 1.75 0.50 2
Cluster gc cr block busy 1,100,814 0.0 1,354.63 1.2 0.18 1.23 1.15 1.32 0.12 2
Comments