RE: PL/SQL memory usage?

  • From: "Post, Ethan" <Ethan.Post@xxxxxx>
  • To: <ryan_gaffuri@xxxxxxxxxxx>, <ldutra@xxxxxxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Tue, 2 Aug 2005 13:43:43 -0500

Not entirely true. Some OS version/levels have limits. I saw a situation
recently at which it was impossible to exceed 500MB with out ORA- error.
The tricky part was trying to figure out what array was using all the
memory and where in the PLSQL the array was being filled. Would be nice
to have a little event that traced these objects only.


From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Tuesday, August 02, 2005 1:38 PM
To: ldutra@xxxxxxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
Cc: Leandro Guimaraes Faria C. Dutra
Subject: Re: PL/SQL memory usage?

memory allocation for pl/sql tables is unbounced. you can make your
arrays so big that you use up so much memory on a server that you cannot
make a SSH connection to the server and cannot connect to sqlplus. 
A guy on a previous project of mine did a large unbounced bulk collect
and we had to hard kill oracle(we already had connections the server)
from the OS level. 
not sure where it gets stored, but it will keep taking free memory from
your system.
i dont think storing all this in an array would make that much of a
difference. The best way to i mprove performance of this kind of thing
is to look at the algorithm and see if there is a better way to do it
and to see if you can parallelize your steps. For example, can you open
up some oracle jobs and run 3 things at the same time. 


Other related posts: