You Asked
Hi Tom,
I have a Procedure which distributes data from 3 Tables to other tables.
Therefore the procedure fetches every single row from the 3 tables (checks if the data of the 3 rows are ok). Then the date are inserted, updated into other tables.
The code is very complex but I don't want to change it.
My question:
How can I start for example 4 jobs paralell with each job giving to the procedure a parameter (starting_id, ending_id). So each job works on a part of the data.
Can I use DBMS_SCHEDULER?
So my Procedure uses not only one core and it will run faster.
How can I monitor the jobs? DBMS_LOCK.SLEEP then check every 10 secs. if the Job-Status was succeeded?
Thank you for your ideas.
and Tom said...
... So my Procedure uses not only one core and it will run faster.
...
change that to: "and it MIGHT run faster, hopefully it won't actually run slower"
parallel is not a cure for all performance issues. Architecture is. These words:
The code is very complex but I don't want to change it.
will almost certainly prevent you from achieving anything huge here.
If you want something to go 100 times faster, you'll be looking at..... dare I say it.... changing code :)
In any case - read about dbms_parallel_execute. It does this - it can slice a table up by rowid ranges or key ranges.
https://asktom.oracle.com/pls/asktom/asktom.search?p_string=dbms_parallel_execute http://www.oracle.com/au/products/database/o30asktom-082672.html