good question, lets see:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA717DEV.US.ORACLE.COM> declare
2 type strong is ref cursor return emp%rowtype;
3 type weak is ref cursor;
4
5 l_x number := 0;
6 l_start number;
7 l_strong strong;
8 l_weak weak;
9 l_rec emp%rowtype;
10 begin
11 insert into run_stats select 'before', stats.* from stats;
12
13 l_start := dbms_utility.get_time;
14 for i in 1 .. 1000
15 loop
16 open l_strong for select * from emp;
17 loop
18 fetch l_strong into l_rec;
19 exit when l_strong%notfound;
20 end loop;
21 close l_strong;
22 end loop;
23 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
24
25 insert into run_stats select 'after 1', stats.* from stats;
26
27 l_start := dbms_utility.get_time;
28 for i in 1 .. 1000
29 loop
30 open l_weak for select * from emp;
31 loop
32 fetch l_weak into l_rec;
33 exit when l_weak%notfound;
34 end loop;
35 close l_weak;
36 end loop;
37 commit;
38 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
39
40 insert into run_stats select 'after 2', stats.* from stats;
41 end;
42 /
162 hsecs
174 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA717DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 and a.name like '%redo%'
12 order by abs( (c.value-b.value)-(b.value-a.value))
13 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.redo writing 3 2 -1
STAT...redo entries 16 14 -2
LATCH.redo allocation 13 25 12
STAT...redo size 21488 21520 32
So, nothing significant really. Maybe a 7% decrease in runtime over 1,000 iterations. No difference in resource usage...