Re: Reg bind variables

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. Received on Tue Jul 24 2007 - 05:33:14 CDT