SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> set serveroutput on
SQL> declare
2 l_start number;
3 l_cnt number;
4 begin
5 l_start := dbms_utility.get_time;
6
7 for i in 1 .. 100
8 loop
9 execute immediate
10 'select count(*) from dual where dummy = ''' || to_char(i) || '''' INTO l_cnt;
11 end loop;
12
13 dbms_output.put_line( 'No Binds ' || (dbms_utility.get_time-l_start) || ' hsecs' );
14 l_start := dbms_utility.get_time;
15 for i in 1 .. 100
16 loop
17 execute immediate 'select count(*) from dual where dummy = :x'
18 INTO l_cnt
19 USING to_char(i);
20 end loop;
21 dbms_output.put_line( 'Binding ' || (dbms_utility.get_time-l_start) || ' hsecs' );
22 end;
23 /
No Binds 7 hsecs
Binding 1 hsecs
PL/SQL procedure successfully completed.
SQL>