How to increase next extent for an oracle table partition?

So you have been getting high water mark contention on your production database. You have already tried the below two solution and now you need more of a permanent fix and there is increase in the data coming in to the database.

1. Increased the inittran value in table and table partitions.
2. Pre-allocated an extent to the table partition or table.

Here is another thing we can do but we need to visit the architecture a little bit. We all know the logical architecture of the database is such that the smallest unit in an oracle database is a block which is 8k in size where data gets written. Some eight blocks make an extent which is a bigger logical storage space than an oracle block. Extents are kept in segment another logical storage unit in database bigger than an extent. Segments are kept in table spaces which are the largest logical storage unit and those tablespaces hold the datafiles which have actual data of the database tables and data.

Now the way ASM automatic storage management of oracle works is that it allocates an initial extent to the table or table partition to write data and as the need increases it allocate more extents. Now it has been often seen that HW contention get resolved if an extent is pre allocated to the partition or table to write to. But ASM is supposed to do the automatically ?

Yes, that is right ASM is trying. If we query the table partition and see what is the size given to the partition for initial and next extent we will understand it more. 

In my case the table is partitioned so i need to look on the partition level more than table level but concept remains the same. We try to find out the information of next extent of that table partition where the insert is writing for the table which shows up on the contention top OEM graph. At this point we know table name. This is how I queried the table to find the information.

select Partition_name, initial_extent, next_extent from all_tab_partitions where table_name='&table_name' and TABLE_OWNER='&owner';

I see its writing to my December 31st partition as lets say today is  31st December 2019 and the  above query will give the last partition its writing to and which is CLMT_Y2020_Q4_M12_W5_D31. 

I see that the next extent for this partition is 1M as below: 1048576bytes/1024/1024=1M

select Partition_name, initial_extent, next_extent from all_tab_partitions where table_name='IM_CLAIM_TRACKING_MESSAGE' and TABLE_OWNER='CRUSER' and partition_name='CLMT_Y2020_Q4_M12_W5_D31';

PARTITION_NAME                 INITIAL_EXTENT NEXT_EXTENT

------------------------------ -------------- -----------

CLMT_Y2020_Q4_M12_W5_D31                          1048576

Note: So ASM is getting 1M for the next extent every time it fills that 1M from ASM. We also know that the step two solution of adding a preallocated extent of 100M was not enough. This means a lot of data is coming in and writing and we need to reduce our overhead of ASM allocating extents, in turn HW contention.

Here is what we can do we can increase the value of next extent for the all future partitions. To generate a script for all partitions in this table you can use below query. 

select 'alter table '|| TABLE_OWNER||'.'||table_name||' modify partition '|| partition_name || ' storage (NEXT 100M);' from all_tab_partitions where  table_name='&table_name' and TABLE_OWNER='&owner'

You really  only need to update the future and current today's partition where the insert is writing or will write. As an example lets increase next extent value for CLMT_Y2020_Q4_M12_W5_D31                          

I want to make it 10M you can increase as per need. 100M will be ideal for busy table as we used pre-allocation and observed it finishes quickly. For this example i am making it 10M 

SQL> alter table table_owner.table_name modify partition CLMT_Y2020_Q4_M12_W5_D31 storage (NEXT 10M);

Table altered.

lets check the new next extent for my partition: 10485760bytes/1024/1024=10M

SQL> select Partition_name, initial_extent, next_extent from all_tab_partitions where table_name='IM_CLAIM_TRACKING_MESSAGE' and TABLE_OWNER='CRUSER' and partition_name='CLMT_Y2020_Q4_M12_W5_D31';

PARTITION_NAME                 INITIAL_EXTENT NEXT_EXTENT

------------------------------ -------------- -----------

CLMT_Y2020_Q4_M12_W5_D31                         10485760

This means the overhead will decrease for ASM next extent and bigger chunks will be allocated to extent with less n number of time ASM has to assign an extent. In turn we may get rid of HW Contention.

Hope this helps!