Re: Reg bind variables

On Jul 24, 9:28 am, balu <krishna...@xxxxxxxxx> 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


.

Relevant Pages

  • Re: Dynamic sql
    ... Oracle I'm reading instead that it is cool? ... Only because of the bind variables way to secure from sql injection? ... I wonder where you read dynamic sql generation is 'cool' in Oracle; ... count ')) LOOP ...
    (comp.databases.oracle.server)
  • Re: Reg bind variables
    ... end loop; ... SQL> declare ... Dynamic sql can use bind variables, it is hard parsed at least once. ... anonymous blocks) which is not wrapped in 'EXECUTE immediate' or OPEN ...
    (comp.databases.oracle.server)
  • Re: Dynamic SQL Method 4 : Avoid Re-parsing?
    ... applications that cannot know a finite set of SQL at compile-time; ... completely dynamic SQL. ... queries) using bind variables with a single function that communicates ... cursor, open cursor, fetch, and close cursor each and every time. ...
    (comp.databases.oracle.server)
  • Reg bind variables
    ... SQL> declare ... end loop; ... If i use bind variables it is taking nearly 4min where as without bind ...
    (comp.databases.oracle.server)
  • Re: Library Cache
    ... clause conditions you could change it to use bind variables in the ... You could replace the SQL in tables with SQL housed in stored ... no criteria parameter for a given query, then it is not appended to ...
    (comp.databases.oracle.server)
Loading