Skip to Main Content
  • Questions
  • Parallel execution of procedure like multithreading in java

Breadcrumb

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

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

declare
  l_input clob;
begin
  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);
end;
/


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

declare
  l_task varchar2(32767);
begin
  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);
end;
/


and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

December 05, 2019 - 5:23 am UTC

Reviewer: swapnil shimpi

Thanks for the clarification . Checking feasibility for suggested approach.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.