RE: Stored procedure memory problem

Thanks a lot guys!
Now I have some understanding about how oracle handles cursors. Mark: can you please point me to some document or site which could put some more light on what you said below. Few hundreds of thousands was NOT typo... 100000 - 500000

-Venu

-----Original Message-----
Sent: Wednesday, December 18, 2002 1:04 PM To: Multiple recipients of list ORACLE-L

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
mark_at_bobak.net
"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: mark_at_bobak.net

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: ListGuru_at_fatcity.com (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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Venu Gopal Andem
  INET: venu.andem_at_wipro.com

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: ListGuru_at_fatcity.com (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).



text/plain attachment: Wipro_Disclaimer.txt