Query running slower and slower
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?
have you run statspack? traced the process? tuned the sql?
are you using the rbo or cbo?
You said "same execution plan"
Question is: how good is that execution plan? Would you mind in posting it?
Posting the actual query and specs of available indexes wouldn't hurt either.
Couple of quick questions...
1- Do you have fresh stats in both table and indexes?
2- Is performance degradation directly related to table growth? meaning (x rows => t time)... ( x*2 rows => t*2 time) or some relationship like these?
Last edited by PAVB; 08-04-2007 at 02:21 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
If you started off taking 30 minutes to process 100 records then it sounds very much like you're processing them one at a time instead of using bulk sql operations, which would almost certainly be a great deal faster.
Click Here to Expand Forum to Full Width