How to execute a stored procedure in another session at the same time in pl / sql

Question

In PL / SQL, in any case, you can call several sessions and simultaneously execute the procedure on several sessions.

I want to use this in real-time applications where 250 users enter the application. Users connect to Oracle through a client tool. (Power Builder is a Front End tool)

For example, if a user calls a stored procedure, this stored procedure must be executed 10 times with different parameter values.
I don't want to run this sequentially one after another 10 times in the same session, because it can take a lot of time.
I am looking for a way in which I can run a stored procedure in 10 different sessions at the same time.

I was thinking of posting 10 jobs using DBMS_JOB.SUBMIT, but due to the heavy workload (250 users * 10 = 2500 tasks can be scheduled in the task scheduler at the same time, etc.), our DBA group is looking for another best way.

Answer 1

To avoid publishing multiple Oracle jobs, you can try using the William Robertson Parallel PL / SQL launcher .

if you create a table " PQ_DRIVER " with 4 partitions and a parallel power of 4 with one row in each section and execute a query on the rows " SELECT /*+ PARALLEL(pq,4) */ thread_id FROM pq_driver pq ", which should convince PQ a controller for installing four PQ subordinate processes to work on it (one per section). And if you pass this query as a cursor parameter to a pipeline function with concurrency support, should you not create a situation where each line is processed by a separate PQ slave process? So, here is a way to use (well, crack) the PQ mechanism so that it handles arbitrary calls to PL / SQL procedures in parallel.

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

  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; 

The execute_command function 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; 

Answer 2

Alternatively, you can use the DBMS_PARALLEL_EXECUTE package to create the JOB .

Here are some hits:

Use create_chunks_by_sql with by_rowid => FALSE , i.e. using the identifier and create the exact number of blocks as necessary execution of the stored procedure.

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

Pass the procedure call as the sql_stmt parameter, for example.

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

Optionally, as you can see, you can pass the fragment number to the procedure, so you can use it as threadId .

Here is a complete example.

Create a task and 3 pieces

 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 using 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; / 

Delete task

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