Niall's Oracle Pages

Those of you who have a much more active developer hat than I do would probably do well to take a look at Mark Rittman's Oracle Weblog. We don't do very much from the BI/ Data Warehouse perspective at my employer so I don't feel especially qualified to comment on the content, but this is an excellently put together web site .

One thing I did find mentioned was the use of BULK bind and collections in PL/SQL. It just so happens that I was inspired by an example in Connor McDonald et als new book, to look at the resource requirements and performance implications of this technique. The script I have used can be found here. The main lesson to learn is that processing multiple rows can indeed gain you significant performance improvements - the main resource used appears to be memory. There is a law of diminishing returns however and it therefore probably makes sense for most people to use the LIMIT clause and limit to a few hundred rows at most. You don't want many session all using huge amounts of ram for an extra couple of % performance improvement.

My testing also appears to indicate that the performance improvements in 10g are significant in this area - but again you pay the price in memory even as compared to 9. Both the tests below are done on the same laptop with similaraly configured databases. The figures shown are extracts from v$mystats (check out the script for the exact meaning). NB the figure for memory in version 10 is probably inaccurate for the first few results (until it starts increasing).

9.2.0.5 first

NIALL 25-MAY-2004 14:00@nl9204>exec bulk_test
Rows:1
- Time: 867
- Max Mem: 369004
- CPU: 0
Rows:4
- Time: 295
- Max Mem: 451504
- CPU: 0
Rows:16
- Time: 134
- Max Mem: 451504
- CPU: 0
Rows:64
- Time: 94
- Max Mem: 451504
- CPU: 0
Rows:256
- Time: 86
- Max Mem: 501192
- CPU: 0
Rows:1024
- Time: 85
- Max Mem: 632800
- CPU: 0
Rows:4096
- Time: 86
- Max Mem: 879448
- CPU: 0
Rows:16384
- Time: 97
- Max Mem: 1964240
- CPU: 0
Rows:65536
- Time: 99
- Max Mem: 6515112
- CPU: 0
Rows:262144
- Time: 98
- Max Mem: 24750552
- CPU: 0
Rows:1048576
- Time: 109
- Max Mem: 46750112
- CPU: 0

PL/SQL procedure successfully completed.



10.1.0

NIALL 25-MAY-2004 14:12@nl1010>exec bulk_test
Rows:1
- Time: 834
- Max Mem: 1743436
- CPU: 0
Rows:4
- Time: 238
- Max Mem: 1743436
- CPU: 0
Rows:16
- Time: 91
- Max Mem: 1743436
- CPU: 0
Rows:64
- Time: 54
- Max Mem: 1743436
- CPU: 0
Rows:256
- Time: 45
- Max Mem: 1743436
- CPU: 0
Rows:1024
- Time: 41
- Max Mem: 1808972
- CPU: 0
Rows:4096
- Time: 43
- Max Mem: 2136652
- CPU: 0
Rows:16384
- Time: 50
- Max Mem: 3381836
- CPU: 0
Rows:65536
- Time: 55
- Max Mem: 8493644
- CPU: 0
Rows:262144
- Time: 58
- Max Mem: 28940876
- CPU: 0
Rows:1048576
- Time: 59
- Max Mem: 53647948
- CPU: 0

PL/SQL procedure successfully completed.