Re: Help with Shared Pool Problem

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_at_freelists.org 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.
> --
> http://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.



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