Re: Probably Simple Question - Execute immediate

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 18 May 2001 11:47:08 -0400
Message-ID: <c7gagtcp4n7m59bmddc2kvfcr80olsqmlp@4ax.com>

A copy of this was sent to "Daniel A. Morgan" <dmorgan_at_exesolutions.com> (if that email address didn't require changing) On Thu, 17 May 2001 18:52:05 -0700, you wrote:

>John Doe wrote:
>
>> Oracle, Sybase, Ingres & Informix all have an "EXECUTE IMMEDIATE" command with
>> slight variations. I get the idea, we immediately parse and excute some dynamic
>> SQL, PL/SQL block etc., Makes sense to me. But, do I have a choice? I can't
>> just use "EXECUTE" can I? The only way I can think of to *not* parse and
>> immediately execute a piece of dynamic SQL is to use dbms_sql and then I can
>> parse and then execute a half hour later if I want. In other words, an
>> "EXECUTE IMMEDIATE" command syntactically implies to me that there is an EXECUTE
>> command with a few variations - like maybe EXECUTE DEFFERED, EXECUTE
>> SOMEOTHERTIME.. etc., but they don't seem to exist. Some it seems to be a two
>> word command with no variations - is this correct? For example, I can't alter
>> database datafile drop.. I can only offline drop, or offline.. So.. after this
>> meandering, I guess my question is are there any variations to this syntax? Is
>> it faster then dbms_sql.parse dbms_sql.execute etc.,, ?
>>
>> Little confused
>> Thanks,
>> D
>
>There are no variations on the theme. It is EXECUTE IMMEDIATE and that is it. And
>yes it is very substantially faster than anything involving the DBMS_SQL package
>though there are some things that can be done in DBMS_SQL that can not be done by
>EXECUTE IMMEDIATE.
>
>Daniel A. Morgan

beg to differ. There are things native dynamic sql (NDS) does faster, things it does slower. If you execute a statement dynamically once or twice, NDS is faster. If you execute the same statement with different inputs many time -- dbms_sql is not only faster but much more scalable.

the problem with NDS is that a look like:

for i in 1 .. 100 loop
execute immediate 'insert into ' || tname || ' values ( :x )' using i; end loop;

is just like:

for i in 1 .. 100 loop

     open cursor
     parse statement
     bind
     execute
     close cursor

end loop

That would most efficiently be coded as:

   open cursor
   parse statement
   for i in 1 .. 100 loop

      bind
      execute

end loop
close cursor

DBMS_SQL beats NDS in such a condition. Additionally -- if you have a multi-user system dbms_sql is more scalable. Each iteration of the NDS loop soft parses the query. It is better then a hard parse -- but it is a parsing and will cause library cache contention.

Here is an example (single user mode just to show that NDS is not always the fastest way to do it). If we employ array processing (easy in dbms_sql) the results are even more apparent -- many times more.

scott_at_TKYTE816> create or replace

  2  procedure dbmssql( p_tname     in varchar2,
  3                     p_rows      in number default 500 )
  4  is
  5      l_stmt      long;
  6      l_theCursor integer;
  7      l_status    number;
  8  begin
  9      l_stmt := 'insert into ' || p_tname ||
 10                ' q2 ( a, b, c ) values ( :a, :b, :c )';
 11
 12      l_theCursor := dbms_sql.open_cursor;
 13      dbms_sql.parse(l_theCursor, l_stmt, dbms_sql.native);
 14
 15      for i in 1 .. p_rows
 16      loop
 17          dbms_sql.bind_variable( l_theCursor, ':a', i );
 18          dbms_sql.bind_variable( l_theCursor, ':b', sysdate+i );
 19          dbms_sql.bind_variable( l_theCursor, ':c', to_char(i) );
 20          l_status := dbms_sql.execute( l_theCursor );
 21      end loop;
 22      dbms_sql.close_cursor( l_theCursor );

23 end;
24 /

Procedure created.

scott_at_TKYTE816> create or replace
2 procedure native_dynamic( p_tname in varchar2,

  3                            p_rows   in number default 500 )

4 is
5 begin

  6      for i in 1 .. p_rows
  7      loop
  8          execute immediate
  9                'insert into ' || p_tname ||
 10                ' q3 ( a, b, c ) values ( :a, :b, :c )'
 11          using i, sysdate+i, to_char(i);
 12      end loop;

13 end;
14 /

Procedure created.

scott_at_TKYTE816> drop table t;
Table dropped.

scott_at_TKYTE816> create table t ( a int, b date, c varchar2(15) ); Table created.

scott_at_TKYTE816> truncate table t;
Table truncated.

scott_at_TKYTE816> declare
2 l_start number default dbms_utility.get_time; 3 begin

  4          dbmssql( 't', 50000 );
  5          dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs'

);
6 end;
7 /
3323 hsecs

PL/SQL procedure successfully completed.

scott_at_TKYTE816> truncate table t;
Table truncated.

scott_at_TKYTE816> declare
2 l_start number default dbms_utility.get_time; 3 begin

  4          native_dynamic( 't', 50000 );
  5          dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs'

);
6 end;
7 /
3963 hsecs

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri May 18 2001 - 10:47:08 CDT

Re: Probably Simple Question - Execute immediate - confused by sytax