I don't carry the book with me - and am on travel, so you'll need to refresh my memory here.
(but I probably said "the RIGHT way to do this is a single sql statement!" - meaning #1 is the right thing to do)
I'd want a test case to work with - like I give you....
What does "NB" mean?
But - there was a big change in 10g as far as cursor processing goes. Here is a excerpt from a paper I recently wrote:
.......
Well, fortunately, I wont write like that. Too many times in the past when just providing my experiences, Ive been wrong. I use pages where others might use paragraphs and try to convey an understanding of why what I say is true (or not, you can see for yourself!). Here is a quote from that book (very similar to others youll see in fine books like Mastering Oracle PL/SQL: Practical Solutions by Connor McDonald) showing how I truly believe all technical information needs to be presented. Your ideas come out, what you think is true comes out, some evidence supporting that is shown, the numbers are discussed, caveats explored it is all right there:
<quote src=Effective Oracle by Design>
Use Bulk Processing When It Has Dramatic Effects
As an example, we'll compare processing the EMP table 14 rows at a time versus processing it a row at a time. Here is the version for row-at-a-time processing:
ops$tkyte@ORA920> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> begin
2 for i in 1 .. 5000
3 loop
4 for x in ( select ename, empno, hiredate from emp )
5 loop
6 null;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
And here is the version that uses bulk processing:
ops$tkyte@ORA920> declare
2 l_ename dbms_sql.varchar2_table;
3 l_empno dbms_sql.number_table;
4 l_hiredate dbms_sql.date_table;
5 begin
6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
Running Runstats to compare the versions shows the following:
ops$tkyte@ORA920> exec runstats_pkg.rs_stop(10000);
Run1 ran in 274 hsecs
Run2 ran in 132 hsecs
run 1 ran in 207.58% of the time
This shows that fetching our entire result set using BULK COLLECT in one SQL statement runs faster (about twice as fast in this case) than doing the same thing a single row at a time.
The response times you see will be a function of the amount of data you array-fetch, as well. More or less data in the result set will have a definite impact on the performance here. The more data you bulk-fetch, up to a point, the better relative performance you will see from the BULK COLLECT over time. For example, when I put 56 rows in EMP, the BULK COLLECT version was 380% better. When I put 1 row in EMP, both versions ran in the same amount of time. At some point, however, the BULK COLLECT will cease being more efficient, as the amount of RAM it consumes increases greatly. Where that point is varies, but I find a BULK COLLECT size of about 100 rows to be universally "good" in practice. Later, we'll look at using the LIMIT clause to control this.
Looking further in the Runstats report, we see some interesting numbers:
Name Run1 Run2 Diff
STAT...session logical reads 80,522 15,525 -64,997
STAT...consistent gets 80,003 15,004 -64,999
STAT...buffer is not pinned co 70,000 5,000 -65,000
STAT...no work - consistent re 70,000 5,000 -65,000
STAT...table scan blocks gotte 70,000 5,000 -65,000
STAT...recursive calls 75,003 5,003 -70,000
LATCH.cache buffers chains 162,601 32,582 -130,019
Overall latching is reduced.
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
188,736 58,658 -130,078 321.76%
PL/SQL procedure successfully completed.y
That is analogous to what we observed in SQL*Plus in Chapter 2, when we played with the ARRAYSIZE setting while using AUTOTRACE. The larger the array size, the fewer consistent gets we performed, and the better the performance and scalability. The same rules apply here, but the impact is not as transparent as just adjusting an ARRAYSIZE setting is. Here, we needed to rewrite the code using PL/SQL table types or collections. We needed to declare variables to fetch into. We used more memory in our session. We can use V$MYSTAT, a dynamic performance view, to see the net effect on memory usage.
It is for these reasons that I recommend using bulk processing only where and when it would have the most dramatic effect. In the example shown here, it looks dramatic. But that is only because we did it for 14 5,000 rows. It would be worthwhile here, if you did that process many times. If you did that process for 50 rows, you would discover they run in about the same amount of time and that the BULK COLLECT actually does more latching!
</quote>
There you have pretty much all of the facts and perhaps most importantly you have some way to verify the truth. Say you just bought that book (I wrote that book as Oracle 9iR2 was in full swing, Oracle 10g was still a glint in our eyes) and you read the advice. You might be tempted to take it to heart and write all of your code using BULK COLLECT, or even worse, rewrite all of your existing code to do so.
Only to discover the expert is totally wrong.
Because things change. (just a side note, most everything else in that books applies to 10g! And you have the examples available to you to see that)
But fortunately you bought the book with evidence, and you grabbed the examples from the website. And you ran it in 10g. And you observed:
ops$tkyte@ORA10G> exec runStats_pkg.rs_start
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> begin
2 for i in 1 .. 5000
3 loop
4 for x in ( select ename, empno, hiredate from emp )
5 loop
6 null;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> exec runStats_pkg.rs_middle
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> declare
2 l_ename dbms_sql.varchar2_table;
3 l_empno dbms_sql.number_table;
4 l_hiredate dbms_sql.date_table;
5 begin
6 for i in 1 .. 5000
7 loop
8 select ename, empno, hiredate
9 bulk collect into l_ename, l_empno, l_hiredate
10 from emp;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> exec runStats_pkg.rs_stop(10000)
Run1 ran in 51 hsecs
Run2 ran in 48 hsecs
run 1 ran in 106.25% of the time
Name Run1 Run2 Diff
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
58,709 58,859 150 99.75%
PL/SQL procedure successfully completed.
Well, it would seem hardly worth it to do the bulk collect here wouldnt it? The reason in Oracle 10g, PLSQL is silently array fetching 100 rows at a time for us, when we do for x in ( select * from t PLSQL has already bulk collected 100 rows. We no longer needed to do that extra code, the extra work.
And, had I not had a test case, I might still not know that. I might still be giving the advice bulk collect, everywhere. Someone reading my book was kind enough to email me and ask why do I get these numbers when I run your example. 60 seconds of research and I found out why (tkprof reveals a lot! A simple sql_trace=true and away we go).