Oracle parameter binding qualitative can practice

From the composition of the SGA of Oracle, it is highly recommended to use parameter bindings. Using parameter bindings makes it possible to use the share Pool efficiently, which can significantly improve performance by not having to harden the cached SQL.

The specific practice is as follows:

Sql>create table Test (a number (10));

Create a second stored procedure:

create or replace procedure p_test is
i number(10);
begin
i := 0;
while i <= 100000 loop
execute immediate ' insert into test values (' || to_char(i) || ')';
i := i + 1;
end loop;

commit;

end p_test;

Test is not bound with the parameter first:

After running p_test, spents 91.111 seconds

Then create a parameter-bound:

create or replace procedure p_test is
i number(10);
begin
i := 0;
while i <= 100000 loop
execute immediate ' insert into test values (:a)'
using i;
i := i + 1;
end loop;
commit;

end p_test;

After running p_test, spents 55.099 seconds.

From the above operating time can be seen, the difference between the two are 39.525%, visible, with no parameter binding in the performance of the difference is relatively large.

http://tb.blog.csdn.net/TrackBack.aspx?PostId=1416644