Overhead of dynamic PL/SQL block execution

View All Scripts Login to Run Script

Script Name Overhead of dynamic PL/SQL block execution
Description You can use execute immediate to run SQL statements constructed at run-time. You can also do the same for PL/SQL blocks. But how expensive is it to do that? This script computes elapsed time for executing static PL/SQL 1,000,000 and then doing the same dynamically. Without a doubt, you pay a price for dynamic execution, but when I ran this on a Free Tier ATP instance, I found that it took about 7.3 seconds to execute 1,000,000 dynamic PL/SQL blocks. So I suggest that unless you are writing code to run under the most demanding circumstances, you should not worry about utilizing dynamic PL/SQL. Of course, you should only use execute immediate when you *need* to. Mostly this means you cannot for some reason complete the construction of your SQL or PL/SQL before your code runs. But sometimes you might want to switch to dynamic PL/SQL to avoid lots of code repetition and make your program easier to maintain. In this case, knowing that execute immediate has such a low overhead should encourage you to take that step with confidence.
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday March 22, 2021

Statement 1

We'll use the get_time function to compute elapsed time down to the hundredth of a second.

Small timer utility

create or replace package tmr authid definer is
   procedure capture;
   function elapsed return number;
   procedure show_elapsed;
end tmr;

Statement 2

create or replace package body tmr is
   last_timing number := null;

   procedure capture is
   begin
      last_timing := DBMS_UTILITY.GET_CPU_TIME;
   end;

   function elapsed return number is
   begin
      return ( mod(DBMS_UTILITY.GET_CPU_TIME - last_timing + POWER(2, 32), POWER(2, 32)) );
   end;

   procedure show_elapsed is
   begin
      DBMS_OUTPUT.PUT_LINE(elapsed);
   end;
end tmr;

Statement 3

I wanted to do as little work as possible while still avoiding the optimizer from taking a shortcut that would distort results. I think this will do it.

Static PL/SQL

declare
   n number;
begin
   commit;
   tmr.capture;
   for indx in 1..1000000 loop
      n := n + indx;
   end loop;
   dbms_output.put_line('Static after computing ' || n);
   tmr.show_elapsed;
end;

Statement 4

Same code now inside a dynamic PL/SQL block.

Dynamic PL/SQL

declare
   n number;
begin
   commit;
   tmr.capture;
   for indx in 1..1000000 loop
      execute immediate 'begin :n := :n + :i; end;'
         using in out n, in indx;
   end loop;
   dbms_output.put_line('Dynamic after computing ' || n);
   tmr.show_elapsed;
end;

Statement 5
And now lets avoid the in out parameter and see how much impact that has.
```
create or replace package g authid definer is
   n number;
end;
/
```

Statement 6

Static SQL with global variable

begin
   commit;
   tmr.capture;
   for indx in 1..1000000 loop
      g.n := g.n + indx;
   end loop;
   dbms_output.put_line('Static after computing ' || g.n);
   tmr.show_elapsed;
end;
/

Statement 7

I see a slight reduction in elapsed time, something like 5%, when executed on my ATP instance.

Dynamic PL/SQL with global variable

begin
   commit;
   tmr.capture;
   for indx in 1..1000000 loop
      execute immediate 'begin g.n := g.n + :i; end;'
         using in indx;
   end loop;
   dbms_output.put_line('Dynamic after computing ' || g.n);
   tmr.show_elapsed;
end;
/

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database