SQL>
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 myStart number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for 'select object_name from all_objects where object_id = ' || i;
10 fetch l_rc into l_dummy;
11 close l_rc;
12 end loop;
13 dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||' seconds...' );
14 end;
15 /
20.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.27
SQL>
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 myStart number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for 'select object_name from all_objects where object_id = :x'
10 using i;
11 fetch l_rc into l_dummy;
12 close l_rc;
13 end loop;
14 dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||' seconds...' );
15 end;
16 /
.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL>
SQL> set timing off