Re: Shared pool usage

From: <tremblaymartin_at_my-deja.com>
Date: 2000/05/03
Message-ID: <8epqa4$h8m$1@nnrp1.deja.com>#1/1

In article <39105F0A.FF0E1E07_at_metatel.com>,   alla.gribov_at_metatel.com wrote:
> I have a problem that might sound trivial to most of you, but I need
 an
> urgent help. I don't know that much about how
> shared pool is working and can't find any answers myself so far.
>
> I have a PL/SQL procedure that connects to the server running
 somewhere
> and is listening for the data from the server,
> until the server closes the connection.
>
> The way it's done is as following: there is a server written in C that
> opens up a socket for sending data. I wrote
> a C program that has the following functions: open socket connection,
> read from the socket, write to socket and close connection.
> This C program is compiled as shared library, so I could call it's
> functions from the PL/SQL package as external procedures.
>
> Everything worked perfect, until I started running out of memory in
> shared pool. My shared_pool_size is set to about 50M on
> a Solaris box with 256M of RAM (this is just a development machine).
>
> I made an experiment and saw the following. I restarted DB and looked
 at
> the v$sgastat view. I had nothing running, so the
> amount of free memory in shared pool stayed the same. As soon as I
> started my procedure (it's done as a job using dbms_job
> package), the amount of free memory started going down. At this point
> the server was not sending any data. Nothing
> was going on except, that my procedure in it's loop was constantly
> trying to call the read_data external procedure.
>
> Can someone explain to me, why shared pool memory was decreasing and
> what it was used for?
>
> It's a really urgent problem for me, because if I can't figure out
 what
> is "eating" my memory, I'll have to re-design the
> whole thing and I have a really tight deadline (like yesterday :-))))
>
> Thank you very much for any help you can provide me with
>
> I appreciate you taking your time to answer
>
>

/***********************************************************************
*****/


>                                  Alla Gribov

>                           alla.gribov_at_metatel.com

> The greatest programming project of all took six days. On the seventh
> day the programmer rested. We've been trying to debug the thing ever
> since. Moral: design before you implement.
>

/***********************************************************************

*****/
>

I dont know if this could help you. Each time a program connect to a database (a new session) Oracle allocate memory in a structure called PGA (Program Global Area). If your PL/SQL program open CURSORS and dont close them the PGA memory will grow until you reach the OPEN_CURSORS limit. So the best way to debug your code is to monitor your sessions. There is a function in DBMS_UTIL package that force Oracle to release the unused memory. It could be a workaround for you. I have already seen something like that and i solved my problem by reconnecting my program in regular interval (Oracle kill the old session and release the unused memory).

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 03 2000 - 00:00:00 CDT