It's important to realize that Oracle does not materialize all the rows to be returned from a cursor in memory. If you do 'select * from one_million_row_table;', at no time are all one million rows going to be in the PGA. However, there are cases where some or all of the data DOES have to be materialized, such as in a sort. This is why the sort_area_size and sort_area_retained_size parameters are important. They govern how much memory a single process can consume. Sorts larger than that threshold will automatically write to disk. If you're on 9i, you may wish to investigate pga_aggregate_target instead.
You said "few hundreds of thousands"?? Is that a typo?? (Perhaps you meant "hundreds or thousands"??) Something else to consider is a shared server (aka MTS) configuration for large numbers of users in an on-line type environment, where the users have a large amount of think time. Shared server is a bad idea for batch type users, but could be a memory saver for an environment where there are thousands of users connected, but with long think times, the concurrency is limited. Hope that helps get you pointed in the right direction. -Mark On Tue, 2002-12-17 at 22:53, Venu Gopal Andem wrote: > A small question to all gurus on the list... > > I have a stored procedure (SP) which runs at specific intervals. It > fetches a list of user names and few (2 columns) of their details into a > cursor and processes them one by one in a cursor FOR loop. > > Now my question is, Is there a chance of the SP running out of memory > when the number of users increases (may be a few hundreds of thousands). > If yes how to estimate the maximum number of user names my cursor can > handle? Is there any calculation for this? How to overcome this problem? > Is there anything else that has to be taken into account when doing > this? > > Thanks in advance! > Venu G. -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).