Reg bind variables

Giganews Newsgroups
Subject: Reg bind variables
Posted by:  balu (krishna0…@gmail.com)
Date: Tue, 24 Jul 2007

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.

Replies