Oracle8i Enterprise Edition Release 220.127.116.11.0 - Production
PL/SQL Release 18.104.22.168.0 - Production
CORE 22.214.171.124.0 Production
TNS for Solaris: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
This happens when batch job runs .massive writes ...
The main problem I think is the DBWR is not flusing the dirty buffer to the disk fast ?Once after completing the writes only the archival will come play ?
Check if u see relatively long time_waited for the “buffer busy wait” and “write complete wait” events in v$system_event.If yes , then DBWR is the culprit.
The solution to such a problem is either to compensate for the problem by making the checkpoint more aggressive, or to solve the problem by making the I/O more
Check out the parameter FAST_START_IO_TARGET Instead of performing large checkpoints at periodic intervals, the database writer tries to keep the number of dirty blocks in the buffer cache low enough to guarantee rapid recovery in the event of a crash.
Also since u mentioned it is a batch update , why not enable nologgin feature and do direct inserts and also drop index and recreate after batch update.This shall reduce redo generation. But be sure to take backup if u enable nologging.
if your batch job is very large you should consider increase redo log file size or add more groups plus above two parameters settings
you may also consider set FAST_START_IO_TARGET to 0 in order to disable checkpoints forced by this parameter if you dont have any kind of service level agreement with your customer (instance recovery with you redo log size arent very slow anyway)