Query running slower and slower
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query running slower and slower

  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Query running slower and slower

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    have you run statspack? traced the process? tuned the sql?

    are you using the rbo or cbo?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width