In one of our 11204 database, we
encountered the deadly ‘Enq: HW contention’ on critical business day. The
majority of the DB activity is insert and the table has CLOBs. The CLOBs were
traditional BASIC LOB, With the below note able to identify the LOB segment on
which the contention is.
Analyzing
'enq: HW - contention' Wait Event (Doc ID 740075.1)
Manual extent allocation or event
44951 didn’t
help much, so finally decided to migrate the CLOBs to SECURE FILES. Due to the
segment size is in 7+ Terabyte considering the time /storage we decided to
create new table and let the logs write there. This may not be feasible to many
of your environment. This particular table is monthly RANGE partitioned to
truncate the older partition every 2 months. Now it is created with hash sub
partition to distribute the inserts and reduce concurrency.
I could not locate appropriate
DDL for the RANGE-HASH composite partition, so the below test case developed.
Currently the new table is with secure file LOBs and hash sub partitioned. The
performance is great with the same amount of load.
Note from 12c the default LOB is Secure file LOB.
TEST CASE
CREATE TABLE hash_test
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
)
PARTITION BY RANGE
(time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION
BY HASH (cust_id)
SUBPARTITIONS 4
STORE IN (USERS)
(PARTITION "P0" VALUES LESS THAN (TO_DATE(' 2018-01-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
set long 99999 lines
200 pages 9999
col DDL for a200
select
dbms_metadata.get_ddl('TABLE','HASH_TEST','SYS') DDL from dual;
insert into hash_test
values(10023,101,sysdate);
insert into
hash_test values(10024,104,sysdate);
insert into
hash_test values(10025,107,sysdate);
insert into
hash_test values(10026,110,sysdate);
insert into
hash_test values(10027,113,sysdate);
insert into
hash_test values(10028,116,sysdate);
insert into
hash_test values(10029,119,sysdate+30);
insert into
hash_test values(10030,122,sysdate+30);
insert into
hash_test values(10031,125,sysdate+30);
insert into
hash_test values(10032,128,sysdate+30);
insert into
hash_test values(10033,131,sysdate+30);
insert into
hash_test values(10034,134,sysdate+30);
insert into
hash_test values(10035,137,sysdate+30);
insert into
hash_test values(10036,140,sysdate+60);
insert into
hash_test values(10037,143,sysdate+60);
insert into
hash_test values(10038,146,sysdate+60);
insert into
hash_test values(10039,149,sysdate+60);
insert into
hash_test values(10040,152,sysdate+60);
insert into
hash_test values(10041,155,sysdate+60);
insert into
hash_test values(10042,158,sysdate+60);
insert into hash_test
values(10043,161,sysdate+60);
SQL> select
count(*) from hash_test subpartition
(SYS_SUBP19335);
COUNT(*)
----------
1
SQL> select
count(*) from hash_test subpartition
(SYS_SUBP19336);
COUNT(*)
----------
1
SQL> select
count(*) from hash_test subpartition
(SYS_SUBP19337);
COUNT(*)
----------
5
SQL> select
count(*) from hash_test subpartition
(SYS_SUBP19338);
COUNT(*)
----------
0
SQL> select
count(*) from hash_test partition (SYS_P19339);
COUNT(*)
----------
7
exec
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYS' ,tabname =>
'HASH_TEST',cascade => true, estimate_percent =>
dbms_stats.auto_sample_size,method_opt=>'for all indexed columns size AUTO',
granularity => 'ALL', degree => 8);
alter table hash_test
truncate partition SYS_P19339;
CREATE INDEX
prod_id_ix ON hash_test(prod_id) LOCAL;
Subpartition
Partition
Tablespace Last Analyzed High
Value High Value
Length Num Rows
B-Level
Distinct Keys
Name Name
SYS_SUBP19326
P0
USERS 09-Feb-20
10:57:26
AM
0 0
0 0
SYS_SUBP19327
P0
USERS 09-Feb-20
10:57:26
AM
0 0
0 0
SYS_SUBP19328
P0
USERS 09-Feb-20
10:57:26
AM
0
0
0 0
SYS_SUBP19329
P0
USERS 09-Feb-20
10:57:26
AM
0 0
0 0
SYS_SUBP19330
SYS_P19390 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19331
SYS_P19390 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19332
SYS_P19390 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19333
SYS_P19390 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19335
SYS_P19391 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19336
SYS_P19391 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19337
SYS_P19391 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19338
SYS_P19391 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19340
SYS_P19392 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19341
SYS_P19392 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19342
SYS_P19392 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
SYS_SUBP19343
SYS_P19392 SYSTEM
09-Feb-20 10:57:26
AM
0
0
0 0
DATA Generator.
DECLARE
t_seed
NUMBER(20);
BEGIN
-- Initialize
Random Number Generator
SELECT
TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))
INTO t_seed
FROM Dual;
DBMS_RANDOM.INITIALIZE(t_seed);
-- Generate
Random Data
FOR t_counter
IN 1..1000 LOOP
INSERT INTO aolmob.HASH_TEST (
PROD_ID, CUST_ID, TIME_ID
)
VALUES (
DBMS_RANDOM.VALUE(0.00,
999999.00), DBMS_RANDOM.VALUE(0.00, 2147483647.00),
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2415020, 2488068)), 'J')
);
END LOOP;
DBMS_RANDOM.TERMINATE;
END;
/
Please leave your valuable comments to improve the blog and
let me know if any specific topic to be explained.
