db file scattered read for INSERT
Hi,
I have 10.2.0.1 database running on Solaris 10 on SPARC
I have a batch job which does insertion in a table (TRANSACTIONS) and this insertion in this table initiates triggers to do insertions/updations in other tables. Until 19th December, this job was finishing under 2 hours but now it is taking over 4 hours to finish.
AWR before 19th December shows that "INSERT INTO transactions..." statement is executing around 1200 times with average execution time of 3.8 seconds. On 20th and 21st December, now average execution time for this same INSERT statement is 11.8 seconds.
On difference is that ASH of 20th and 21st December shows a lot of "db file scattered read" against this "INSERT INTO transactions..." statement. I have really no idea why suddenly INSERT became very slow with this wait event during Insertion, having following in mind
1) No change in code.
2) No change in data.
3) No new index creation and no index dropping.
4) No stats collection on any table/index involved in this batch job on 19th December which may have changed executions time for 20th or 21st December.
Please help