Re: Help with Shared Pool Problem

  • From: Graeme.Farmer@xxxxxxxxxx
  • To: rlsmith@xxxxxxx
  • Date: Fri, 15 Oct 2004 09:57:18 +1000

Only if you can tell me why I keep running out of money!! Without my bank 
details your probably not going to be able to tell me! (Or you might 
suggest because my wife has a credit card!!)...
Seriously though, you have not provided any information which may help 
some of the people on Oracle-L to give you assistance, such as the error 
text (which will contain the name of the subheap that space could not be 
allocated from), Oracle version, related parameters (anything to do with 
shared pool/cursors, etc), how often it occurs, after how long, remedial 
action taken, etc, etc, etc

In the absence of any of this information the best advice that can be 
given is to first go and understand "why" you are getting these errors. 
There are some great Metalink notes on ORA-4031 errors which you should 
read and understand and then jump over to Steve's site (www.ixora.com.au) 
and grab the SQL that queries x$ksmsp (or write your own) and check the 
numbers and sizes of free space chunks to see if fragmentation is an 
issue, (you may also check for shared pool and library cache latch 
contention, if the degree of concurrency is high then these are indicators 
that the fragmentation is actually causing you performance degradation as 
well as space issues).

If you have a heapdump trace in the user_dump_dest directory when the 4031 
occurred then there is helpful information in it, if not then configure a 
level 2 heapdump triggered on the 4031 error and you can view the 
allocations in the shared pool at the time when the event occurred (which 
is important as it's hard to diagnose an issue that is no longer an 
issue).

Between Metalink and Steve's web site (and his book) you should have 
enough reading and understanding to keep you busy for a few lifetimes!! 

Graeme

oracle-l-bounce@xxxxxxxxxxxxx wrote on 14/10/2004 10:55:25 PM:

> Can someone tell me why I keep running out of shared pool space?  If old
> SQL statements are supposed to be aged out or written over
> automatically, how can I run out of space?  This is happening from time
> to time in several databases.  It appears it happens when large data
> loads are running.
> 
> Ron
> 
> 
> Important Notice!!
> If you are not the intended recipient of this e-mail message, any use, =
> distribution or copying of the message is prohibited.
> Please let me know immediately by return e-mail if you have received =
> this message by mistake, then delete the e-mail message.
> Thank you.
> --
> //www.freelists.org/webpage/oracle-l


-- 
This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please notify the 
sender and delete the transmission. The contents of this e-mail are the opinion 
of the writer only and are not endorsed by the Mincom Group of companies unless 
expressly stated otherwise.



--
//www.freelists.org/webpage/oracle-l

Other related posts: