The Blind Variable

14th December 2016

In this quick demonstration I show the performance benefits of using bind variables in dynamic SQL.

set echo on

set timing on

alter session set timed_statistics=true;

alter session set sql_trace=true;

create table a (b varchar2(20));

declare

s clob;

r pls_integer := 100000;

-- x varchar2(1);

begin

s :=

'insert /*+ APPEND */ into a

select dbms_random.string(''X'',20) as b

from dual

connect by rownum <= '||r;

execute immediate s;

commit;

end;

/

truncate table a;

declare

s clob;

r pls_integer := 100000;

-- x varchar2(1);

begin

s :=

'insert /*+ APPEND */ into a

select dbms_random.string(''X'',20) as b

from dual

connect by rownum <= :1';

execute immediate s using r;

commit;

end;

/

We will view the trace file through TKPROF (see my previous post SQL Trace and the Command Line Interface for further on doing this).

...

Let's tidy up.

alter session set sql_trace=false;

alter session set timed_statistics=false;

drop table a;

Back to blogs