we are using version 11.2.0.3.0 of oracle version. We had one insert query which is inserts record into one of our transaction table, and daily we insert ~150+million records into this table from java batch in parallel. Suddenly we see the INSERT query is performing poorly and there were backlogs getting piled up at java end, and we are struggling to catch those up. As i see from dba_hist_Sqlstat, this query has started doing high disk reads(verified from disk_read_dlta column) as compared to other days making it to run longer for each execution. and i see the obj# is coming as its index partitions. in normal days we usd have Avg~150 reads/execution now its Avg ~600disk reads/execution.Initially this insert it was taking avg~.01seconds/execution in normal days, now its consuming ~.5seconds/execution. Execution plan is excatly same as previous and this insert is also very straight forward. also i see overall data base level useri/o(DB file sequential read) spike and on top, this query is coming in picture. Not seeing any other concurrency related wait though on to.
So want expert suggestion how should i find the root cause for this issue? we have not changed anything to the database. Our team suggesting to increase next extent value for this object and its partitions. this table is daily range partitions on a date column.
INSERT INTO tab1 (c1, c2, c3, c4,
c5, c6, c7, c8, c9) VALUES (sysdate, 'INFO',
:1 , :2 , :3 , :4 , :5 , :6 , :7 )
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Note
-----
- cpu costing is off (consider enabling it)