DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Hybrid View

  1. #1
    Join Date
    Dec 2002

    Long running batch job - Performance improvement


    We have a long running batch job that takes hours to complete. The customer has about 100,000 records in a table. Our application has a stored procedure that goes through each record, performs some computations/validations on various columns and updates certain columns in the table. It performs a "commit" after processing a fixed count of records in the table(to have some control on the size required by the rollback segments). This serial processing of records in the table is causing performance problems.

    We are looking to see if we can do some parallel processing to speed up the execution time. Can I simultaneously spawn off a call to the same procedure (probably using dbms_job) with a criteria like:

    Execute the procedure on records 1-10000
    Execute the procedure on records 10001 - 20000
    Execute the procedure on records 90001 - 100000

    Will this cause any contentions especially with the procedure needing to do updates to the records in the table (although a different one each time). How about transactional integrity?

    If this is possible, should the environment have multiple processors to handle this and actually give a perfromance gain?

    Any help will be appreciated.
    Thank You.

  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    You might like to readdress the methodology entirely, and try to do the updates and validations etc in SQL, rather than PL/SQL. A pretty generic statement I know, but for bulk updates SQL is a better tool.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    May 2003
    Chennai, India
    You could parallally run the procedure with different ranges
    thru dbms_job. But you need to make sure the following:
    (a) Commit after every record
    - This would avoid other sessions to hold the lock
    on the rows. This is necessary if common rows are
    getting updated.
    (b) Contentions
    - Oracle by default has a good locking mechanism
    so that no contention would be there.
    - Chcek your code if there is a chance for deadlock.


  4. #4
    Join Date
    Jul 2001
    Originally posted by Pandi
    Commit after every record
    I refuse to link this statement with a thread subject.
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.