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

I've worked on many batch systems and this is the slowest I've seen! Ideally, yes, I would like to fix the PL/SQL. However, the client is overly sensitive to anyone touching the code due to the number of functional problems they've had. However, the SQL is definately something I will be tuning.

I plan on getting the processing down to max. 30 minutes which seems more than enough for 5000 records!!

Pete Finnigan <plsql_at_petefinnigan.com> wrote in message news:<8I2$qRB6J2u$QxbT_at_peterfinnigan.demon.co.uk>...
> Hi Paul,
>
> 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
Received on Thu Nov 20 2003 - 01:54:49 CET