ORA-04031 - Shared Memory

[THoey]

All,

I started receiving an ORA-04031 error for a database that I have. The rest of the error message is:

Unable to allocate 8192 bytes of shared memory("large object","unknown object","sort subheap","sort key&quot

This database supports some software we bought from a company (name not disclosed). When we received the server & software, I installed Oracle 8.1.6 following the defaults (Software company didn't say to make any changes).

I called them for support, but their one Oracle DBA (and I am not sure that title has been attained) is not allowed to take phone calls from mere clients. They expect each client to have their own DBA. Well, that's me (I know I haven't attained that title).

I did search, and find that this error usually means that the SHARED_POOL_SIZE needs to be increased. So I did. As far as I can tell, this has solved the problem. But now for my question. Are their any general rules for setting up the SHARED_POOL_SIZE, LARGE_POOL_SIZE, etc? Even if all you do is point me to a great book on setting up and maintaining Oracle 8.1.6 in an NT environment, it will help.

Thanks in advance...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[karluk]

I also would like to hear about some good reference books on this topic.

As far as your strategy of increasing the shared_pool_size it certainly seems like a good approach. The only question I would ask is where did the extra memory come from? I contacted Oracle last May about memory allocations and the main thing they stressed was to limit the total SGA to 30%-50% of available memory on the server. If you have gone over this threshold you may need to reduce elsewhere, such as the db buffer cache.

My understanding of large_pool_size is that it may not be needed depending on your server configuration, and that if it is needed Oracle will calculate a default value which may need to be adjusted later. With this in mind I would let it default and then monitor usage. Apparently it's mostly needed for multithreaded server and certain parallel operations.

Another parameter to consider is shared_pool_reserved_size. That's the one that allocates a separate area of memory for large objects. The rule of thumb I have heard is to start this at 10% of the shared_pool size and then adjust depending on uage.

 

[Issanov]

You probably have misspelled error message:

Unable to allocate 8192 bytes of shared memory("large object","unknown object","sort subheap","sort key&quot

instead of

Unable to allocate 8192 bytes of shared memory("large POOL","unknown object","sort subheap","sort key&quot

I believe that you are running Oracle in shared mode
(Multi-thread server). In this case it DOES require
large pool, and needs it to be big enouph.

Provided I'm right you have to increase large_pool_size
parameter in your init.ora