To: Philip Taylor 
Subject: Re: How full is the shared pool?

On Wed, 1 Dec 1999, Philip Taylor wrote:

> > I've been running a fairly large instance for some time with the
> > initialisation parameter SHARED_POOL_SIZE set to 9,000,000. Suddenly this
> > week the users have been getting ORA-4031 messages when running certain
> > jobs. According to OERR 4031 is:
> > 
> > 04031, 00000, "unable to allocate %s bytes of shared memory
> > (\"%s\",\"%s\",\"%s"
> > *Cause:  More shared memory is needed than was allocated in the shared
> > pool.
> > *Action: Either use the dbms_shared_pool package to pin large packages,
> >          reduce your use of shared memory, or increase the amount of
> >          available shared memory by increasing the value of the
> >          init.ora parameter "shared_pool_size".
> > 
> > Basically, there wasn't enough free space in the shared pool to handle
> > certain stored procedures. In fact even stuff like Export stopped working
> > !!!
> > 
> > Fortunately I was in a position where I could increase the
> > SHARED_POOL_SIZE
> > and bounce the database - all was fine after this. 
> > 
> > My question is, how do I find out how full the shared pool is so that I'm
> > not caught out like this again. Is there any way to tell ?
> > 
> > Cheers,
> > 
> > Phil Taylor
> > Oracle DBA
> > Rolfe & Nolan
> 
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe@quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe@quickdoc.co.uk
> 

You can issue:

SELECT * FROM V$SGASTAT;

This view contains a plethora of information about the SGA. There is a
record for "free memory". So, you can deduce how full the SGA is from its
size and the amount free.

This is a great view for seeing all sorts of things about the SGA - dozens
of categories that vary depending on which version of Oracle you have.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 340+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page