Long running batch job - Performance improvement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Long running batch job - Performance improvement

  1. #1
    Join Date
    Dec 2002
    Posts
    6

    Long running batch job - Performance improvement

    Hi:

    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
    Location
    Colorado Springs
    Posts
    5,253
    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
    Location
    Chennai, India
    Posts
    2
    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.

    Regards
    Pandi

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by Pandi
    Commit after every record
    I refuse to link this statement with a thread subject.
    Tomaž
    "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