• Questions
  • Parallel execution of procedure like multithreading in java



Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, swapnil.

Asked: December 01, 2019 - 1:34 pm UTC

Answered by: Connor McDonald - Last updated: December 02, 2019 - 7:46 am UTC

Category: PL/SQL - Version: 11

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Oh those tricky exception sections! Why won't it handle my error?

You Asked

Hi All,
Consider below sample Data model:
Application is maintaining information of different countries - States -Cities (each of this is individual tables).
At the end of quarter we are doing assessment and calculating different metrics at country level ,citi level.
Example : No of cars in Country/City , No of parks in Country/City etc.
Queries for all this metrics are stored in config table .
Procedure runs at the EOQ and calculates the metrics and stores in METRICS table.
For each combination of Country-State-City-Quarter-Calendar it generates map id and stores all metrics as a column values for it .
Currently procedure takes map id at a time and populates the table.
I want to execute the procedure in parallel for set of mapids , it should run independently something like threads in java .
I am aware of DBMS_PARALLEL_EXECUTE - where i can execute individual metric queries in parallel.
I want procedure execution also to happen in parallel

and we said...

DBMS_PARALLEL_EXECUTE is not about issuing queries in parallel, it is about performing any nominated task in parallel chunks.

So in your case, you currently have

- a procedure that takes a map id
- has a list of map id's (country/state/etc combinations) to process

So all you need do is create chunks based on the map id, eg

  l_input clob;
  l_input := 'select distinct map_id, map_id from test_tab';

  dbms_parallel_execute.create_chunks_by_sql(task_name => 'proc_task',
                                             sql_input  => l_input,
                                             by_rowid  => false);

and then run the proc using that map id's as input

  l_task varchar2(32767);
  l_task := 'my_proc(:start_id,:end_id)';

  dbms_parallel_execute.run_task(task_name      => 'proc_task',
                                 sql_input       => l_task,
                                 language_flag  => dbms_sql.native,
                                 parallel_level => 10);

and you rated our response

  (1 rating)


December 05, 2019 - 5:23 am UTC

Reviewer: swapnil shimpi

Thanks for the clarification . Checking feasibility for suggested approach.