Timing Bind variable
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>