How to resolve HW contention contention

The first thing we can think is, we can think of increasing the init tran value of the tables, its partitions and index partitions.

How to increase Inittran value for a table, table partitions and its indexes?


After this has been done and if now the spike is still showing up. While some may believe that ASM will manage inittran and we dont have to allocate or change or increase its value but other think right we do have to do some sort of intervention.

Here is what should resolve it, simple but very conceptual. When an Extent is allocated to a table or its partition and now its needs more it takes time.

If we do preallocation of this extent and new inserts need extent right away this preallocated extent can be used by table or table partition and no further delay for ASM to do this part.  Oracle suggests 100MB for each partition and my table is like 2TB. Based on the storage available and your table size you can decide with your team the size you want to give to extent.

NOTE: Explicitly allocating an extent with this clause does not change the values of the NEXT and PCTINCREASE storage parameters, so does not affect the size of the next extent to be allocated implicitly by Oracle Database/ASM. 

Check table extents:
select bytes/1024, count(*) from user_extents where segment_name = 'TABLE_NAME' group by bytes/1024 order by 1;

Add new 100M extent:
alter table TABLE_NAME allocate extent (size 100M);

Check the new extent added:
select bytes/1024, count(*) from user_extents where segment_name = 'TABLE_NAME' group by bytes/1024 order by 1;

Here is how you can generate a script for your table paritions:
select 'alter table table_name modify partition '|| PARTITION_NAME|| ' allocate extent (size 100M)' from all_tab_partitions where table_name='table_name' and TABLE_OWNER='Schema_owner';

Use the above generated script and allocate 100M to each partition. 

This should resolve the HW contention coming from multiple inserts in same table which you increased the init tran and gave a preallocated entext. Monitor and the spike should not come back. 

NOTE: This will resolve the spike but for long term you should increase next allocation of extent on partition or table level and make it bigger so ASM allocates next extent with a bigger size. Mine was 1GB and decided to make it 100GB based on the usage. So ASM takes care of next allocations and extent management.

Hope this helps.