Re: DYNAMIC SQL AND BIND VARS.

As a follow-up to Jonathan's comments, this subject came up a few months ago,
and someone demonstrated this with some simple SQL examples. Sorry, I don't
remember who it was. (If I had to guess, I'd say maybe Connor McDonald, but
I'm not certain.)

Anyhow, I took that and cleaned it up a bit, and the following is the result.
I think it pretty clearly demonstrates the performance impact difference:

set echo off
-- bind_test.sql
-- by M. Bobak, based on script found on comp.databases.oracle.server newsgroup
-- 06/14/02, source of original script is unknown accept how_many prompt 'number of loops: [25000] ' default 25000 set feedback off
set verify off
alter system flush shared_pool;
set timing on

prompt non-shared dynamic SQL (no bind variables) declare
 x number;
 begin
   for i in 1 .. &&how_many loop

       execute immediate 'select '||i||' into :b1 from dual' into x;    end loop;
end;
/

set timing off
alter system flush shared_pool;
set timing on

prompt shared dynamic SQL (with bind variables) declare
 x number;
begin
  for i in 1 .. &&how_many loop
    execute immediate 'select :i into :x from dual' into x using i;   end loop;
end;
/

set timing off
alter system flush shared_pool;
prompt shared static SQL (with bind variables) set timing on

declare
 x number;
begin
  for i in 1 .. &&how_many loop
    select i into x from dual;
  end loop;
end;
/
set timing off
set feedback on
set verify on

---------end of script----------------------

On my box, it produces the following results: non-shared dynamic SQL (no bind variables) Elapsed: 00:00:18.02
shared dynamic SQL (with bind variables) Elapsed: 00:00:07.81
shared static SQL (with bind variables)
Elapsed: 00:00:03.86

So, the effect is substantial and noticable.

-Mark

PS I did a quick search, and the origin of the idea was Connor McDonald,
as can be seen here:
http://groups.google.com/groups?q=bind+variables+connor+mcdonald+group:comp.databases.oracle.server&hl=en&lr=&ie=UTF-8&selm=3CFE705A.1E5F%40yahoo.com&rnum=6

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ao43lh$cik$1$8302bc10_at_news.demon.co.uk>...
> You might have started an argument with this one.
>
> It is using bind variables (for the rowstamp) -
> and it is vastly better than:
>
> > EXECUTE IMMEDIATE 'SELECT ' || LV_FIELD || ' FROM ' || LV_TABLENAME
> >|| ' WHERE ROWSTAMP = ' || LV_ROWSTAMP
> > INTO LV_VALUE;
>
> It keeps the number of different SQL statements used
> down to
> no. of lv_field_names x no. of lv_tablenames
>
> It keeps the quantity of code written to a minimum.
>
>
> However, if you have a very high pressure system
> it is not the best way to code things, as this
> will still require Oracle to go through a number
> of steps of soft parsing - especially since the
> 'EXECUTE IMMEDIATE' call forces a parse call
> to take place.
>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> John Alen wrote in message
> <69ad472f.0210100539.9c51b61_at_posting.google.com>...
> >I have the following bit of code that will be called a lot within a
> >package. Can anyone tell me if this is the making use of bind
> >variables?
> >
> > FUNCTION F_GET_VAR_VALUE (LV_FIELD VARCHAR2
> > ,LV_TABLENAME VARCHAR2
> > ,LV_ROWSTAMP VARCHAR2)
> > RETURN VARCHAR2 IS
> >
> > LV_VALUE VARCHAR2(2000);
> >
> > BEGIN
> > EXECUTE IMMEDIATE 'SELECT ' || LV_FIELD || ' FROM ' || LV_TABLENAME
> >|| ' WHERE ROWSTAMP = :ROWSTAMP'
> > INTO LV_VALUE
> > USING LV_ROWSTAMP;
> >
> > RETURN LV_VALUE;
> >
> > END F_GET_VAR_VALUE;
> >
> >Is this the best way doing this?
> >
> >Thanks in advance.
> >
> >John
Received on Fri Oct 11 2002 - 15:40:33 CDT