Re: Shared pool usage

From: Adrian J. Shepherd <theshepherds_at_hotmail.com>
Date: 2000/05/03
Message-ID: <6p0Q4.13969$r6.84221_at_stones>


Perhaps your recycled SQL within the procedure is syntactically different every time and the poll gets filled with new versions all of the time ? Have you queried the SQL area to see what SQL is around ? Assuming you have some kind of SQL in the wait loop...

"Alla Gribov" <alla.gribov_at_metatel.com> wrote in message news:391097E4.24E5D47C_at_metatel.com...
> 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

Original text of this message