Hi,

I'm facing a very critical performance issue now and need some guidance on how to solve the issues. Currently, i have a batch procesing that start running from 1.30am (off peak hour) to process bulk of records (approximately of 6000 to 8000 insertion and 1200 of update). The procesing including, select, update and insert. The batch processing is written in Java programming and it actually split the processing to first 1000 records and process the subsequent 1000 sequentially. Initially, it took about half an hour to process 1000 records but now it took more than 2 hour to process 1000 of records. No changes to the SQL statement and execution plan, really running out of idea why the query getting slower and slower over the months. Starting from taking half an hour to process same no.of records, it increase to 1 hour, then 1 1/2 hour, 2 hours. The table was properly indexed just that the size of the table getting bigger and bigger due to higher no. of records to process.

I'm using Oracle 8. Please advise what can i do to fine tune the database. Will update optimizer statistics help?

Thanks