Skip to Main Content
  • Questions
  • Run procedures or functions in parallel

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Azzam.

Asked: November 11, 2018 - 10:33 am UTC

Last updated: November 12, 2018 - 11:51 am UTC

Version: 11

Viewed 1000+ times

You Asked

hello,
I've web app which is using procedures and functions inside packages most of the time,
in some cases procedures and functions execution taking long time to return data (as SYS_REFCURSOR).
the problem is that when other users execute other procedure they will have to wait until the executing procedure or function finishes.
I think procedures and functions are running in sequential (first come first serve).
can I execute procedures in parallel for each request without affecting each other ??

and Chris said...

the problem is that when other users execute other procedure they will have to wait until the executing procedure or function finishes.

This is FALSE.

Any number of users can run call the same function/procedure at the same time.

You can see this by creating a procedure that outputs the current time in 1s intervals 10 times:

create or replace procedure p ( 
  sess varchar2 
) as
begin
  for i in 1 .. 10 loop
    dbms_output.put_line ( 
      sess || ' ' || to_char ( sysdate, 'HH24:MI:SS ' )
    ) ;
    dbms_lock.sleep ( 1 ) ;
  end loop;
end p;
/


Then run it in two separate sessions. Session 1:

SQL> exec p('S1');
S1 03:47:44
S1 03:47:45
S1 03:47:46
S1 03:47:47
S1 03:47:48
S1 03:47:49
S1 03:47:50
S1 03:47:51
S1 03:47:52
S1 03:47:53


And session 2:

SQL> exec p('S2');
S2 03:47:45
S2 03:47:46
S2 03:47:47
S2 03:47:48
S2 03:47:49
S2 03:47:50
S2 03:47:51
S2 03:47:52
S2 03:47:53
S2 03:47:54


Notice how the times are overlapping? So they both execute it at the same time.

The exception is if you do something blocking inside the PL/SQL. Such as updating/deleting the same rows.

But if you're just running queries or procedural code, they can run at the same time.

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