Re: Reg bind variables

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