Forum Stats

  • 3,781,282 Users
  • 2,254,499 Discussions
  • 7,879,632 Comments

Discussions

Parallelize procedure call

2785725
2785725 Member Posts: 7
edited Nov 4, 2014 1:19AM in SQL & PL/SQL

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.

SazibaIgoroshka

Best Answer

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond
    edited Nov 4, 2014 1:19AM Accepted Answer

    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.

    Igoroshka

Answers

  • L-MachineGun
    L-MachineGun Member Posts: 926

    Please clarify what you mean by:

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

    Preferably in Oracle speak.

  • 2785725
    2785725 Member Posts: 7
    edited Nov 3, 2014 4:29PM

    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
    GregV Member Posts: 3,075 Gold Crown

    Hi,

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

    Saziba
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond
    edited Nov 4, 2014 12:20AM
    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
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond
    edited Nov 4, 2014 1:19AM Accepted Answer

    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.

    Igoroshka
This discussion has been closed.