Re: Shared pool usage

Thanks for your responses.

No I am not running MTS and I don't have any cursors to close.

After doing some digging, I suspect that it's an external procedure problems. Any ideas on this???

Since the main part of the loop is reading data from a socket, which is done by C program anyway,
may be I'll just run it as a C program all together and won't bother calling it as an external procedure.

But I would love to hear some more ideas on what might be going on with my database.

Thanks

Alla

Bob Fazio wrote:
>
> The shared pool is used to hold SQL statements as they are parsed. It hold
> a variety of other information.
>
> The PGA is actually stored outside of the shared_pool as part of the server
> process. If you are running Multi-Threaded Server, which I doubt, the UGA
> portion of a connection is stored in the SGA.
>
> From Oracle's Documentation
>
> The shared pool portion of the SGA contains three major areas: library
> cache, dictionary cache, and control structures. Figure 6-1 shows the
> contents of the shared pool.
>
> The total size of the shared pool is determined by the initialization
> parameter SHARED_POOL_SIZE. The default value of this parameter is 3,500,000
> bytes. Increasing the value of this parameter increases the amount of memory
> reserved for the shared pool, and therefore increases the space reserved for
> shared SQL areas.
>
> Figure 6-1 Contents of the Shared Pool
>
> --
> Robert Fazio, Oracle DBA
> rfazio_at_home.com
> remove nospam from reply address
> http://24.8.218.197/
> <tremblaymartin_at_my-deja.com> wrote in message
> news:8epqa4$h8m$1_at_nnrp1.deja.com...
> > 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.
>
> [Image]
Received on Wed May 03 2000 - 00:00:00 CEST