René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Why is dynamic SQL bad

You may have heard it, or you may haven't, but dynamic SQL is bad. This article tries to explain why that is. In the first -simpler- example, I demonstrate it with one session only while in the second there are two sessions involved.

One Session

create table test_ (i number);

set timing on
begin
  for v_i in 0..99999 loop
    insert into test_ values(v_i);
  end loop;
end;
/
This is the prefered way of inserting records. As we'll see, it's going to take much longer if we're inserting the records using execute immediate: 
truncate table test_;

begin
  for v_i in 0..99999 loop
    execute immediate 'insert into test_ values(' || v_i ||')';
  end loop;
end;
/

Multiple Sessions

We want more than one session to issue dynamic SQL statements. Up to seven sessions will insert 5040 (=1*2*3*4*5*6*7) records in our table.
In order to do this we need to synchronize sessions so as to have those sessions run concurrently. The first session will only be used to synchronize the sessions: 
set feedback off

var v_lockhandle varchar2(200)

declare
  v_result number;
begin

  dbms_lock.allocate_unique('exec_imm_vs_sql_lock', :v_lockhandle);

  v_result:=dbms_lock.request(:v_lockhandle);

  dbms_output.put_line(case when v_result=0 then 'Lock allocated' 
                            when v_result=1 then 'Timeout'
                            when v_result=2 then 'Deadlock'
                            when v_result=3 then 'Parameter Error'
                            when v_result=4 then 'Already owned'
                            when v_result=5 then 'Illegal Lock Handle'
                       end);
    
end;
/
If you've run the anonymous block above, this (first) session will hold a lock by the name of exec_imm_vs_sql_lock. This lock controls the other sessions. The other session will try to aquire the lock and will run as soon the lock is released. First, we do the test for the preferred way: 
var v_lockhandle varchar2(200)

declare
  v_result      number;
  v_tim         number;
begin
  dbms_lock.allocate_unique('exec_imm_vs_sql_lock', :v_lockhandle);

  v_result := dbms_lock.request(:v_lockhandle, dbms_lock.s_mode);

  dbms_output.put_line(case when v_result=0 then 'Lock allocated' 
                            when v_result=1 then 'Timeout'
                            when v_result=2 then 'Deadlock'
                            when v_result=3 then 'Parameter Error'
                            when v_result=4 then 'Already owned'
                            when v_result=5 then 'Illegal Lock Handle'
                       end);

  v_tim := dbms_utility.get_time;

  for v_i in 1 .. 5040 loop
    insert into test_ values (v_i);
  end loop;

  dbms_output.put_line('Time to insert: ' || to_char((dbms_utility.get_time - v_tim)/100,'000.00'));
end;
/
This session will wait until you issue the following snippet on the first session: 
declare 
  v_dummy number; 
begin
  v_dummy:=dbms_lock.release(:v_lockhandle);
end;
/
  Preferred Exec Imm.
1 0.49 1.18
2 0.88 1.90
3 1.62 2.90
4 1.80 3.57
5 2.00 4.59
6 2.62 5.31
7 2.68 6.31