Re: ORA-04031 SGA Error! HELP!

Andrew Zitelli wrote:
>
> The answer to your question depends on which version of Oracle you are
> running. There has been much discussion about this, in this newsgroup
> over the past few months. Oracle 7.2.2.3, 7.2.2.4 and possibly other
> versions have several know bugs that cause this ORA-4031 error. Poor
> tuning of initialization parameters can also cause this error to occur.
> There are now patches for Oracle for 7.2.2.3 and 7.2.3 to correct the
> bugs. Some of the bugs are fixed in 7.2.3 and others will be fixed in
> 7.3. If the problem is caused by the Oracle bugs, and not by poor
> tuning, your best solution is to upgrade and/or apply a patch.
> Increasing the size of the shared pool and other techniques like
> "ALTER SYSTEM FLUSH SHARED_POOL" may help reduce the problem but will
> not fix it. When I encountered the problem in November, Oracle
> support indicated there where as many as three or four known bugs that
> contributed to the problem. I first saw the problem when upgrading
> from 7.1.6 to 7.2.2.3 under HP/UX.
>
> -- Andy Zitelli, Silicon Systems, Inc.

Just last week we had the ORA-04031 error in our 7.1.6 database on HP/UX, Unable to allocate 4120 bytes of shared memory. I've been trying to follow most of the discussions about this error in the newsgroup in recent months, but some of the messages may have passed me by without my reading them. I am wondering if we are running into the same bug in 7.1.6, or suffering from incorrectly tuned INIT.ORA parameters. Perhaps someone can give some insight. Please respond via private e-mail as well as to this newsgroup.

This particular job is a Pro*COBOL batch program that does a big sort (~50M). We are running the MTS with 'db_block_buffers = 45000', 'shared_pool_size = 9000000' and 'sort_area_size = 104857600'. We were experiencing lousy performance with a very small SORT_AREA_SIZE because the sort was getting put on disk, so we increased the SORT_AREA_SIZE parameter to the value above to force the sort to take place in memory.  We then started getting ORA-04031 errors. After doing some reading, we found that when using the MTS, sorts are done in the SGA (in the shared pool) instead of in external memory with a dedicated server. So we kept increasing the SHARED_POOL_SIZE parameter until the sort went through at about 80MB (I don't recall the exact value). If we leave a small SHARED_POOL_SIZE (as above) and switch to dedicated server, the job runs fine, not to mention much faster.

I have a few questions:

1. Why are we getting the ORA-04031 error? It seems to me that Oracle should know that there is no room in the shared pool for the sort to take place and immediately put it to disk. Is this the infamous 4031 bug?
2. Does anyone know the steps Oracle goes through with dedicated AND shared server processes when performing a sort?
3. How about a more detailed explanation of the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE parameters? I have been through the Administrators Guide and I understand how SORT_AREA_SIZE works, but I'm still not completely clear on the RETAINED parameter. Can someone explain it better?
4. For large sort jobs like this, is it better to run shared or dedicated server? I figured the shared server might be slower, but we're seeing 2 and 3 times slower. Is that normal?

Thanks in advance,
Brian

-- 
Brian M. Biggs                             mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.                       voice: (513) 677-7661
http://www.cincom.com/