| Tuning with Oracle Collections [message #293426] |
Sat, 12 January 2008 09:24  |
trowsdale
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
LOOP
ass_array(l_rec.person_id) := l_rec.value;
END LOOP;
OPEN main_cursor; -- loop through employees
LOOP
FETCH main_cursor BULK COLLECT INTO nested_table LIMIT 1000;
EXIT WHEN nested_table.FIRST IS NULL;
FOR l_loop IN nested_table.FIRST..nested_table.LAST
LOOP
....
IF ass_array(nested_table(l_loop).person_id) = 10 THEN
.....
ELSE
.....
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  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
