Re: desperate!!!!!!!!

We were using the exact same type of processing in some stored procedures and as a result, had terrible memory problems. The data was loaded into real memory and NEVER released. As a result, PGA and MAXPGA were always the same.

We detected this when the server crashed and monitored via utlbstat/utlestat and some homegrown monitoring packages. We then verified this with Oracle support.

To fix this, after the data is returned to the client, we issue the command dbms_session.free_unused_user_memory after control is returned to the client. This works for us as this mostly occured during application startup...there may be performance implications for other types of processing.

We are running on NT and but not MTS.

In article <373829b2.3214942_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to "sganesh" <sganesh_at_apple.com>
> (if that email address didn't require changing)
> On 11 May 1999 01:14:42 -0500, you wrote:
>
> >
> >Hi All,
> >
> > I am desperately working for last
> >few days to find out the solution to the
> >following. If anyone can help me , it will be
> >greatly appreciated.
> >
> >1) When I load data in to a PL/SQL array ,
> >commonly known as PL/SQL tabled, does it get
> >loaded to SGA of my instance or to real
> >memory. Basically , I am planning to load a
>
> it depends.
>
> If you are running forms and the plsql table is defined in forms, the
table
> resides in the client.
>
> If your plsql table is in the server, the plsql table will either be
in the real
> memory for the dedicated server or if you are using MTS (multi
threaded server),
> it'll be in the SGA.
>
> So, it can be in one of three places depending on where the table is
declared
> and how the server is configured
>
> >table have 100K records to PL/SQL array and
> >lookup on that. Is that advisable?
> >
>
> No, i don't think so. Thats a lot of stuff to stuff into a array.
I'd just
> leave it in a real table and let the buffer cache deal with it (many
users can
> use the same buffer cache, you would be creating a cache for each and
every user
> with a private plsql table).
>
> >Please help!
> >Thanks very much
> >Bye
> >Sanjay.
> >
> >
> >
> >--Posted from EarthWeb Discussions. http://discussions.earthweb.com
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
Oracle8i'...
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 11 1999 - 09:49:57 CDT