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

Thread: performace tune a 12 hours batch process running

  1. #1
    Join Date
    Feb 2004
    Location
    MI, USA
    Posts
    2

    performace tune a 12 hours batch process running

    I have a PS/Oracle batch sqr being tested right now but initial performance results is not acceptable. Ideas have been thrown about in the shop and the prevailing idea is to provide a range of EMPLID for 5-6 processes to work with and run all of them in parallel at night. Estimates has it we'll be able to do it in 3 hours vs 12. The program is going against the person table (450,000 employees and dependents rows) joined 1 to many to their benefits (900,000) rows. The join returns a row 760 bytes long and drives the process. It also selects the employment (300,000 rows) of each employee. All the program has to do is gather all these (with some data editing) and insert a row to an output table (will contain about 5 million). The person and benefits tables are indexed on EMPLID, Dependent #, the employemnt on EMPLID. The tables are not partitioned and cost based. The program is restartable and making commits every 10,000 person. My concerns however with the approach we seemingly is taking are 1) the aggregate run time of the processes in parralel will obviously exceed that of the single process, and will degrade other processes running the same time as well. To me that is not an improvement but simply a reduction in wall clock. 2) when one of the splits goes down and rollback occurs, it would seem likely the the rest will also go down on timeouts.

    What do you guys think. Will partitioning the tables help? Will splitting the join (read each person then read the corresponding benefits) also help? I'm not sure what other index that will help. Just not comfortable with the approach, i'd like to hear alternatives or suggestions. thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "Commiting every 10,000 rows" suggests that you have adopteed a row-by-row processing methodology, instead of a SQL set-based methodology. Maybe you are doing this to be able to commit every 10,000 rows? I would try to avoid this altogether -- try to change the method to entirely SQL-based, ie. "INSERT INTO ... SELECT ... FROM person, employment,benefits ..." etc.

    If necessary, you could set your target table to nologging, disable any indexes, and "insert /*+ append */ " to reduce the logging actvity.

    You might also try enabling parallelism on the sources (if you have that option).
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Feb 2004
    Location
    MI, USA
    Posts
    2
    the program does data cleansing and multiple lookup as necessary... set processing is not a good option in my case.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I did a lot of perf tuning in PS SQRs.
    Some SQR ran 36 hrs before tuning.
    After tuning I brought down to 3 hrs.
    Tell me the SQR name, that may help me to identify the problem.

    Tamil

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    don't forget you can get your developers to add the ol 10046 event in their sqr's to see that the heck is happening
    I'm stmontgo and I approve of this message

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