Parallelize procedure call

2785725

Is there a simple way to make a parallel procedure call?

I wan't something like this:

declare

  l_first int;

  l_second int;

  l_third int;

begin

  l_first := start_new_thread('call my_procedure(1)'); 

  l_second := start_new_thread('call some_procedure(2)');

  l_third := start_new_thread('call some_procedure(3)');

 

  join_thread(l_first);

  join_thread(l_second);

  join_thread(l_third):

end;

The variant when you create task, create chunks, run task and after all you drop task it's not convenient I'm just want to run my procedure in new thread, i don't need chunk some table by rowid or something else.

Billy Verreynne

It seems that you want to use threading and not necessarily parallel processing.

The difference? Well, parallel processing is taking a single workload and doing that in parallel (thus the need for chunking the workload) . Threading on the other hand can be different workloads done in parallel - e.g. in a flight simulator one thread can do the rendering, another the sound, another the flight model, another the weather model, etc.

And from this (and your other) posting, is seems you want threading and not specifically parallel processing.

One can implement threads as background processes (aka jobs).  But unlike the typical Windows/Posix thread, synchronisation between threads, and access to the main threads data segment, are not really possible.

Here is an example of a basic Thread class for use in PL/SQL:

SQL> create or replace type TThread authid current_user as object(
  2          thread_code     varchar2(32767),
  3          job_id          integer,
  4
  5          constructor function TThread( plsqlCode varchar2, startImmediate boolean default true ) return self as result,
  6          member procedure StartThread(  self in out TThread ),
  7          member function ThreadCompleted return boolean
  8  );
  9  /

Type created.

SQL>
SQL> create or replace type body TThread as
  2
  3          constructor function TThread( plsqlCode varchar2, startImmediate boolean default true ) return self as result is
  4          begin
  5                  self.thread_code := plsqlCode;
  6                  if startImmediate then
  7                          self.StartThread();
  8                  end if;
  9                  return;
10          end;
11
12          member procedure StartThread( self in out TThread ) is
13                  pragma autonomous_transaction;
14          begin
15                  DBMS_JOB.Submit(
16                          job => self.job_id,
17                          next_date => sysdate,
18                          what => self.thread_code
19                  );
20                  commit;
21          exception when OTHERS then
22                  rollback;
23                  raise;
24          end;
25
26          member function ThreadCompleted return boolean is
27                  i       integer;
28          begin
29                  select 1 into i from user_jobs where job = self.job_id;
30                  return( false );
31          exception when NO_DATA_FOUND then
32                  return( true );
33          end;
34
35  end;
36  /

Type body created.

SQL>
SQL> declare
  2          thread1 TThread;
  3  begin
  4          thread1 := new TThread( 'dbms_lock.sleep(10);' );
  5          dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread running as job '||thread1.job_id );
  6
  7          while not thread1.ThreadCompleted() loop
  8                  dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread busy...' );
  9                  dbms_lock.sleep(1);
10          end loop;
11
12          dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread completed' );
13
14  end;
15  /
07:52:50: thread running as job 767
07:52:50: thread busy...
07:52:51: thread busy...
07:52:52: thread busy...
07:52:53: thread busy...
07:52:54: thread busy...
07:52:55: thread busy...
07:52:56: thread busy...
07:52:57: thread busy...
07:52:58: thread busy...
07:52:59: thread busy...
07:53:00: thread busy...
07:53:01: thread busy...
07:53:02: thread busy...
07:53:03: thread completed

PL/SQL procedure successfully completed.

SQL>

Note that no validation exist to ensure the StartThread() method is not invoked multiple times - this class is a mere test template that needs to further evolve before production use.

L-MachineGun

Please clarify what you mean by:

. . . i don't need chunk some table by rowid or something else.

Preferably in Oracle speak.

2785725

I want call my procedure in many threads with different parameters.

I don't want do all this stuff:

  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', USER, 'SOME_TABLE', true, 100); -- i don't need that

  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'just call my procedure, no tables', DBMS_SQL.NATIVE,

  parallel_level => 3);

  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');

GregV

Hi,

I use the Scheduler for that. It lets you run procedures in a parallel fashion.

Billy Verreynne

2785725 wrote:

I want call my procedure in many threads with different parameters.
I don't want do all this stuff:
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', USER, 'SOME_TABLE', true, 100); -- i don't need that
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'just call my procedure, no tables', DBMS_SQL.NATIVE,
  parallel_level => 3);
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');

You do need all this "stuff" and YES, you DO need to "chunk".

Seems like you do not understand what parallel processing is.

A single workload is divided up into multiple smaller workloads (in order to process the smaller workloads in parallel). Where each smaller workload is a unique portion of the entire workload. This process is called chunking.

The result of chunking is smaller workloads, with each workload identified by a range, indicated by bind variables :START_ID and :END_ID.

The task is then given as "<do SQL workload> where <col> between :START_ID and :END_ID". Or as "begin <plsql_procedure>( :START_ID, :END_ID ); end;". This task is then executed per chunk, in parallel. Where each parallel task does a specific chunk (as identified by its start and end identifiers).

And this is what basic parallel processing entails on any system, any platform, any language. Break the workload into chunks. Process the chunks in parallel.

If you don't want this "stuff" and do not need to "chunk", then you are failing to understand the basic concepts of what parallel processing is, and how to apply it to your problem.

Billy Verreynne

It seems that you want to use threading and not necessarily parallel processing.

The difference? Well, parallel processing is taking a single workload and doing that in parallel (thus the need for chunking the workload) . Threading on the other hand can be different workloads done in parallel - e.g. in a flight simulator one thread can do the rendering, another the sound, another the flight model, another the weather model, etc.

And from this (and your other) posting, is seems you want threading and not specifically parallel processing.

One can implement threads as background processes (aka jobs).  But unlike the typical Windows/Posix thread, synchronisation between threads, and access to the main threads data segment, are not really possible.

Here is an example of a basic Thread class for use in PL/SQL:

SQL> create or replace type TThread authid current_user as object(
  2          thread_code     varchar2(32767),
  3          job_id          integer,
  4
  5          constructor function TThread( plsqlCode varchar2, startImmediate boolean default true ) return self as result,
  6          member procedure StartThread(  self in out TThread ),
  7          member function ThreadCompleted return boolean
  8  );
  9  /

Type created.

SQL>
SQL> create or replace type body TThread as
  2
  3          constructor function TThread( plsqlCode varchar2, startImmediate boolean default true ) return self as result is
  4          begin
  5                  self.thread_code := plsqlCode;
  6                  if startImmediate then
  7                          self.StartThread();
  8                  end if;
  9                  return;
10          end;
11
12          member procedure StartThread( self in out TThread ) is
13                  pragma autonomous_transaction;
14          begin
15                  DBMS_JOB.Submit(
16                          job => self.job_id,
17                          next_date => sysdate,
18                          what => self.thread_code
19                  );
20                  commit;
21          exception when OTHERS then
22                  rollback;
23                  raise;
24          end;
25
26          member function ThreadCompleted return boolean is
27                  i       integer;
28          begin
29                  select 1 into i from user_jobs where job = self.job_id;
30                  return( false );
31          exception when NO_DATA_FOUND then
32                  return( true );
33          end;
34
35  end;
36  /

Type body created.

SQL>
SQL> declare
  2          thread1 TThread;
  3  begin
  4          thread1 := new TThread( 'dbms_lock.sleep(10);' );
  5          dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread running as job '||thread1.job_id );
  6
  7          while not thread1.ThreadCompleted() loop
  8                  dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread busy...' );
  9                  dbms_lock.sleep(1);
10          end loop;
11
12          dbms_output.put_line( to_char(sysdate,'hh24:mi:ss')||': thread completed' );
13
14  end;
15  /
07:52:50: thread running as job 767
07:52:50: thread busy...
07:52:51: thread busy...
07:52:52: thread busy...
07:52:53: thread busy...
07:52:54: thread busy...
07:52:55: thread busy...
07:52:56: thread busy...
07:52:57: thread busy...
07:52:58: thread busy...
07:52:59: thread busy...
07:53:00: thread busy...
07:53:01: thread busy...
07:53:02: thread busy...
07:53:03: thread completed

PL/SQL procedure successfully completed.

SQL>

Note that no validation exist to ensure the StartThread() method is not invoked multiple times - this class is a mere test template that needs to further evolve before production use.