Re: Reg bind variables

On Jul 24, 12:33 pm, balu <krishna..._at_gmail.com> wrote:
> On Jul 24, 1:53 pm, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jul 24, 9:28 am, balu <krishna..._at_gmail.com> wrote:
>
> > > Dear all,
>
> > > Question Reg Bind Variables.
>
> > > SQL> declare
> > > begin
> > > for i in 1..100000
> > > loop
> > > insert into x values(i);
> > > end loop;
> > > end; 2 3 4 5 6 7
> > > 8 /
>
> > > PL/SQL procedure successfully completed.
>
> > > Elapsed: 00:00:20.66
> > > SQL> declare
> > > begin
> > > for i in 1..100000
> > > loop
> > > execute immediate 'insert into t1 values(:x)' using i;
> > > end loop;
> > > end; 2 3 4 5 6 7
> > > 8 /
>
> > > PL/SQL procedure successfully completed.
>
> > > Elapsed: 00:04:45.08
>
> > > If i use bind variables it is taking nearly 4min where as without bind
> > > variables it takes only 20Sec to finish the job can you explain me the
> > > concept in detail that will be great help.
>
> > > 2. will soft parse undergo latches.
>
> > > 3. where we have to use bind variables and where not.
>
> > > Regards
>
> > > Balu.
>
> > code fragment 1 is an example of *static sql* in an anonymous block.
> > Static sql is always using bind variables, it is hard parsed once, and
> > soft parsed never.
> > code fragment 2 is an example of *dynamic sql* in an anonymous block.
> > Dynamic sql can use bind variables, it is hard parsed at least once.
> > When you are using bind variables subsequent invocations will be soft
> > parsed, and when you are using hard coded literals *all* invocations
> > will be hard parsed.
>
> > Consequently you aren't comparing 'using bind variables' vs 'not using
> > bind variables', both snippets use bind variables.
> > You are comparing dynamic sql vs static sql.
> > Obviously the code in snippet 2 is inferior to the code in snippet 1,
> > and as the *table name* is *static*, whoever implementing this code
> > should be kicked out.
>
> > Obviously soft parses will undergo latches.
> > You could easily demonstrate this with Tom Kytes runstat tool.
>
> > This is why dynamic sql should be avoided, and bind variables should
> > be used everywhere.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Hi,
>
> Thank's for your reply , but i am not clear with the concept what you
> have mentioned
>
> 1. what is the diffrence between static sql & dynamic sql , that's
> better if you give me some examples.
>
> 2. Every body says that we have to use bind variables , but in my
> second example i am using bind variables but the performance is very
> slow, then what is the use, where i am wrong in that can you pls
> correct me.
>
> Regards
>
> Balu.- Hide quoted text -
>
> - Show quoted text -

1 static sql is all sql in pl/sql (both stored procedures and anonymous blocks) which is not wrapped in 'EXECUTE immediate' or OPEN <ref cursor> FOR or dbms_sql.
so
begin
insert into foo values (1);
end;
/
is static sql
and
begin
update foo set bar=2;
end;
/
static sql
However
begin
execute immediate 'insert into foo values(1)'; end;
/
begin
execute immediate 'update foo set bar=2'; end;
/
declare
ref sys_refcursor;
begin
open ref for 'select * from foo';
end;
/
all dynamic sql.

2 In *both* examples you are using bind variables!! However: in the first example you have static sql: parsed once In the second example you have dynamic sql (execute immediate). Hard parsed 1 time, soft parsed 99999 times
Try

begin
for i in 1..100000
loop
execute immediate 'insert into t1 values ('||i||')'; end loop;
end;
so *dynamic sql* *not using bind variables* and notice this is yet much more slower.

SQL> ed
Wrote file afiedt.buf

  1 begin
  2 for i in 1..100000
  3 loop
  4 insert into x values(i);
  5 end loop;
  6* end;
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:15.04
SQL> commit
  2 /

Commit complete.

Elapsed: 00:00:00.00
SQL> ed
Wrote file afiedt.buf

  1 begin
  2 for i in 1..100000
  3 loop
  4 execute immediate 'insert into t1 values(:x)' using i;   5 end loop;
  6* end;
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:21.02
SQL> rollback
  2 /

Rollback complete.

Elapsed: 00:00:13.01
SQL> begin
  2 for i in 1..100000
  3 loop
  4 execute immediate 'insert into t1 values ('||i||')';   5 end loop;
  6 end;
  7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:57.02
SQL> so if you think you are comparing 'not using bind variables' vs 'using bind variables', you are mistaken.
In static sql you always use bind variables. You are comparing
static sql using bind variables
vs
dynamic sql using bind variables
Above you see the result of dynamic sql not using bind variables: means hard parse every time.

--
Sybrand Bakker
Senior Oracle DBA