Oracle parameter binding performance practices

From the composition of Oracle's SGA, it advocates parameter binding. Using Parameter binding can effectively use the share pool. You do not need to perform hard parsing on cached SQL statements, which can significantly improve performance.

The specific practices are as follows:

SQL> Create Table Test (a number (10 ));

Create another 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;

First, test if no parameter is bound:

It takes 91.111 seconds to run p_test

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 (: )'
Using I;
I: = I + 1;
End loop;
Commit;

End p_test;

It takes 55.099 seconds to run p_test.

From the running time, we can see that there is a 39.525% difference between the two. It can be seen that there is a big difference in performance when you bind a parameter without parameters.