Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: July 07, 2018 - 4:59 pm UTC

Last updated: July 09, 2018 - 5:03 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Team,

In current project, one of the batch process procedure is called in Java layer for Multi threading purpose. As current requirement, we need to call same stored procedure in PLSQL layer instead of Java layer. we tried to implement using DBMS_JOB method but its not efficient as like Java layer.

Do we have any methodology to run Stored procedure in threading.. Please explain..

and Connor said...

Check out DBMS_PARALLEL_EXECUTE.

But the first thing I would check out is - often Java uses multithreading because of processing in a row by row basis. If you can change that to set-based operations via SQL, it is common that you do not need the complexity of multithreading at all.

Here's a trivial demo of that....we start with a routine to "fix" a row value, we need to call it 1000000 times so we're quick to think of multi-threading solutions, when just an update would have been fine.

SQL> create table t as select rownum pk, d.* from dba_objects d,
  2   ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> create index ix on t ( pk );

Index created.

SQL>
SQL>
SQL> create or replace
  2  procedure fix_row(p_idx int, p_new_val varchar2) is
  3  begin
  4    update t
  5    set    owner = p_new_val
  6    where  pk = p_idx;
  7  end;
  8  /

Procedure created.

SQL>
SQL> set timing on
SQL> begin
  2  for i in 1 .. 1000000
  3  loop
  4    fix_row(i,upper(i));
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:36.10
SQL>
SQL> update t
  2  set    owner = upper(pk)
  3  where  pk <= 1000000;

1000000 rows updated.

Elapsed: 00:00:02.52
SQL>
SQL>



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library