You Asked

Hi folks,

I wrote a procedure that downloads spatial information for power lines via WFS from a web site and merges this into an existing table in my ADB. I wrote it in a way that it should use the parallel mode (pipelined table function, no triggers, jala jala...). This works great as long as I start the procedure from my PL/SQL-Developer. I am connected to the DB with the service TP_URGENT.
When I start the procedure from a scheduler task it only runs in a single thread mode (not parallel).
Are there any special things to remember to make the procedure running in parallel mode even when started from a scheduler task?

Cheers and thx
Knut

and Chris said...

You can alter the session to force parallel before calling the procedure in the job. i.e. the job runs a block like:

begin 
  execute immediate 'alter session force parallel dml';
  execute immediate 'alter session force parallel query';
  <your proc>;
end;

Which looks like:

create table t1 ( c1 int );

create or replace procedure load_data
as
begin 
  insert into t1 
    select level from dual connect by level <= 1000;
end;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'parallel_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
begin 
  execute immediate 'alter session force parallel dml';
  execute immediate 'alter session force parallel query';
  load_data; 
end;]',
    start_date      => SYSTIMESTAMP,
    enabled         => TRUE);
END;
/

Looking at the plan, you can see that statement did run in parallel:

select p.*
from v$sql, lateral ( 
  select * from dbms_xplan.display_cursor ( sql_id => sql_id, format => 'BASIC' )
) p
where  sql_text like 'INSERT INTO T1%';

PLAN_TABLE_OUTPUT
----------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
INSERT INTO T1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000
 
Plan hash value: 3272074205
 
-------------------------------------------------------
| Id  | Operation                          | Name     |
-------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |
|   1 |  PX COORDINATOR                    |          |
|   2 |   PX SEND QC (RANDOM)              | :TQ10001 |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| T1       |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |
|   5 |      PX RECEIVE                    |          |
|   6 |       PX SEND ROUND-ROBIN          | :TQ10000 |
|   7 |        CONNECT BY WITHOUT FILTERING|          |
|   8 |         FAST DUAL                  |          |
-------------------------------------------------------

Rating

(1 rating)

Comments

Running a procedure in parallel mode

Knut Göttling, February 18, 2026 - 8:17 am UTC

Hi Chris,

thank you so much for your answer. That also helped to do the trick.
Beside altering the session to force parallel execution it was also needed to place the job in the consumer group TP_URGENT.
But now the import job runs in parallel mode as hoped. Thanks again!
Knut

February 19, 2026 - 5:26 pm UTC

Great, glad you got this sorted.