How to execute a stored procedure in a different session in same time in pl/sql

Question

Is there anyway possible in PL/SQL to invoke multiple sessions and run a procedure in the multiple sessions simultaneously.

I want to use this in a real time applications where there are 250 users logging into the application. The users are connected to Oracle through a client tool. (Power Builder is the Front End Tool)

For example, if an user calls an stored procedure, that stored procedure has to be run for 10 times with different parameter values.
I don't want to run this sequentially one after another for 10 times in the same session because it may take long time.
I am looking for a way where I can run the stored procedure in 10 different sessions simultaneously.

I thought about placing 10 jobs using DBMS_JOB.SUBMIT but because of the heavy job load ( 250 users * 10 = 2500 jobs may be scheduled in the Job scheduler at the same time and so on) our DBA group is looking for some other better way.

Solution 1

To avoid posting several Oracle jobs, you could try using William Robertson Parallel PL/SQL launcher.

if you create a table "PQ_DRIVER" with 4 partitions and a parallel degree of 4, with one row in each partition, and you execute a query along the lines of "SELECT /*+ PARALLEL(pq,4) */ thread_id FROM pq_driver pq", that should persuade the PQ controller to set four PQ slave processes to work on it (one per partition). And if you pass that query as a cursor parameter to a parallel-enabled pipelined function, then shouldn't that create a situation where each each row is processed by a separate PQ slave process? So here is a way to use (alright, hack) the PQ engine so that it processes arbitrary PL/SQL procedure calls in parallel.

The idea is to create a function using PARALLEL_ENABLE and PIPELINED features:

   function pq_submit
    ( p_job_list  varchar2_tt
    , p_pq_refcur rc_pq_driver )
    return varchar2_tt
    parallel_enable(partition p_pq_refcur by any)
    pipelined
 is
 ...
 loop
   execute_command(your_proc);
 end loop;

Function execute_command uses autonomous_transaction.

It looks like this:

procedure execute_command
  ( p_what log_times.what%type )
is
  pragma autonomous_transaction;
begin
  execute immediate p_what;
  commit;
end execute_command;

Solution 2

Alternatively to creating JOBs you may use the DBMS_PARALLEL_EXECUTE package.

Here some hits:

Use create_chunks_by_sql with by_rowid => FALSE, i.e. using ID and create exact the same number of chunks as the required execution of the stored procedure.

In run_task set the parallel_level to the required degree of parallelism. This is the same number as above or lower if you need o throttle the parallelism.

Pass the call of the procedure as the parameter sql_stmt e.g.

BEGIN
  test_proc(:start_id,:end_id);
END;

Optionally as you see it is possible to pass the chunk number to the procedure, so you may use it as a threadId.

Here a complete example

Create task and 3 chunks

DECLARE
  l_stmt CLOB;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => 'parallel PL/SQL');

  l_stmt := 'SELECT rownum, rownum FROM dual connect by level <= 3';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name => 'parallel PL/SQL',
                                             sql_stmt  => l_stmt,
                                             by_rowid  => FALSE);
END;
/

Run the task with DOP = 3

DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN
  l_sql_stmt := 'BEGIN
                   test_proc(:start_id,:end_id);
                END;';   

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => 'parallel PL/SQL',
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 3);
END;
/

Remove the Task

BEGIN
  DBMS_PARALLEL_EXECUTE.drop_task('parallel PL/SQL');
END;
/