Re: Using DBMS_JOB and DBMS_PIPE to mimic multithreaded PL/SQL application

[Quoted] Hi Paul,

[Quoted] without seeing your complex logic I have to say 5 hours for 5000 records does not sound very good. I fixed a PL/SQL batch process a few years ago that processed millions (7-10 million) of financial transactions per night in 18 hours to process them in approx 2 or less. This process loaded data using sql*loader and used complex pl/sql (few thousand lines) and many different processes depending on transaction types to populate a lot of tables. We did parallel up some of the processing where possible but also tuned the bad SQL and PL/SQL. The tuning and sorting out badly laid out tablespaces/datafiles on disk fixed most of it. The i/o was all over the place and it didn't use RAID.

Maybe you should consider tracing profiling and tuning the SQL and PL/SQL first?

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.