Home » RDBMS Server » Performance Tuning » Tuning with Oracle Collections (Oracle 9i)
Tuning with Oracle Collections [message #293426] Sat, 12 January 2008 09:24 Go to next message
Messages: 17
Registered: April 2005
Junior Member
Hi - this is intended for Ross Leishman, as a follow-up to that helpful Oracle tuning article he posted about a year ago (http://orafaq.com/node/1399)

I've been able to use Oracle collections extensively, and have dramatically improved performance of many programs.

Typically, our batch programs loop through a set of employees, do some calculations, then write to a table (this is a very common type of batch, as you noted).

Even though it's a not advisable, we almost always have SQL within the master loop. This would be stuff that's very hard to tie into the main query (as you recommend).

This is how I usually work around this problem: (haven't tried "concurrent cursors" yet)

FOR l_rec IN (SELECT value,person_id FROM slow_payroll_view) -- get lookup stuff
ass_array(l_rec.person_id) := l_rec.value;

OPEN main_cursor; -- loop through employees
FETCH main_cursor BULK COLLECT INTO nested_table LIMIT 1000;

FOR l_loop IN nested_table.FIRST..nested_table.LAST
IF ass_array(nested_table(l_loop).person_id) = 10 THEN

Here's my questions:

- The "ass_array" collection will typically have about 5000 records - is that too many for an associative array?
- Is it wrong to use that "FOR cursor" loop with ass_array? Will I lose any gains made later? Should a nested table be used to collect "slow_payroll_view" before it's added to "ass_array"?
(Just so you know, the above has worked nicely, but maybe it can be better).

Thanks for any suggestions!!

Bill Trowsdale

Re: Tuning with Oracle Collections [message #293513 is a reply to message #293426] Sun, 13 January 2008 20:35 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The initial FOR loop to load the associative array is no problem - so long as you do it only once. Benchmark it yourself and you will see that it takes less than a second for 5000 rows.

How big can an associative array be before it affects performance? It varies. The idea is to not exceed your available memory. However many such associative arrays you have loaded for the session must no be permitted to exceed memory, otherwise they will swap to disk.

5000 is definitely within the OK range - I wouldn't worry unless you had 10 or more such collections. If you had something pushing up around the 50-100K mark, it would be worth monitoring. If this happens, your DBA may be able to help tweak your session or the database setup as a whole to fit the collections in available memory - otherwise you need a different approach like Concurrent Cursors.

Ross Leishman
Re: Tuning with Oracle Collections [message #293529 is a reply to message #293426] Sun, 13 January 2008 23:20 Go to previous message
Messages: 17
Registered: April 2005
Junior Member
Thanks again, Ross, I appreciate the advice.

Previous Topic: Query wih bind variables
Next Topic: sga_max_size: Oracle reserves all at ones?
Goto Forum:

Current Time: Tue Aug 22 19:47:12 CDT 2017

Total time taken to generate the page: 0.10306 seconds