Performace issue due to very high DML
Hi All,
I have a procedure that inserts around 150 million rows in a two hour period. I have two indexes on the table which is partitioned on range [business_date]. I end up getting very high buffer busy waits, free buffer waits on the two partitioned indexes during the two hour period. At present I am using ASSM and the disk is on a Raid 5.
As a part of the improvement process I have increased the sequence CACHE size used in the table. I am also planning to replace the current range with range-hash partition. I am also planning to create a new tablespace where segment space management is manual and have multiple freelists/inittrans for the table as well as for the index. I am also looking to add couple of DB writers and am also increasing the the db_buffer_cache based of the cache advice. I cannot use reverse key as that would be a big code change for us. I am planning to find
0