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 ryan_gaffuri@xxxxxxxxxxx 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.