Increasing size of SGA / Reducing Use of SGA

Modify kernal parameters for both SEMAPHORES and SHARED MEMORY and rebuild the kernal.

SGA size is more critical in version 7 because of shared SQL area, and PGA - which is a private section of shared memory dedicated to each SESSION. Tuning the SGA has the second best payback in my experience. The first, of course, is tuning the [PL./]SQL code.

Happy computing!

---
<<MFF>>


-------------------------------------

Twenty-Five years ago it meant something to be Crazy!

			- Charles Manson


In article 978_at_gtewd.mtv.gtegsc.com, davidsonj_at_gtewd.mtv.gtegsc.com () writes:


>

>I should have posted this a week ago considering how responsive Oracle tech 


>support has been (I logged a TAR a week ago, have called back 3 or 4 times, 

>and have yet to have anyone call back).

>

>I'm willing to bet that the net is a least as responsive as #*&^%(^&(^*^&$$^&%

>(expletives deleted) ORACLE.  Anyway, here's the problem........

>

>I (and many of my co-developers) have been running into the following error

>while we have been trying to create stored procedures (and in some cases to

>execute stored procedures):

>

>create or replace package body SOME_NAME_HERE as

>*

>ERROR at line 1:

>ORA-04031: out of shared memory when trying to allocate 25868 bytes 

>(PL/SQL MPCODE,BAMIMA: Bam Buffer)

>

>

>Looking at the description of the error message in the appropriate Oracle 

>manual indicates that we are out of SGA (System/Shared Global Area) -- i.e.

>memory allocated to ORACLE -- and that we can either attempt to free up

>some of that memory or to increase the size of the memory allocated to the

>SGA by modifying our INITsid.ORA file.

>

>I looked at our INIT.ORA file (the one for our instance of the database and

>not the template provided by Oracle) and saw that ORACLE provided some

>default parameter values for SMALL, MEDIUM, and LARGE database and that we

>were currently using the parameter settings for the SMALL database.  I modified

>the INIT.ORA file to use the MEDIUM settings, shut down ORACLE, and restarted

>it using dbstart.  When I did this, I got the following messages:

>

>SQLDBA> Connected.

>SQLDBA> ORA-07252: spcre: semget error, could not allocate semaphores.

>ULTRIX Error:  28: No space left on device

>Additional Information: 1

>SQLDBA>

>SQL*DBA complete.

>

>Database "sid" warm started.

>

>

>I have no idea why I am getting this error and have been forced to revert to

>the original INIT.ORA file in order to continue development (although 

>significantly impeded by the original error).

>

>Two questions:

>a) Why am I unable to restart the database with the new INIT.ORA file when

>   I seem to have plenty of memory and diskspace?  According to the prologue

>   of the INIT.ORA file, I only need 6.8 MB - we have 164 MB or so - and

>   I don't think changing this file has much if any impact on disk space.

>

>b) What sort of things can I do to free up SGA?  I tried dropping MANY packages

>   and tables from various user accounts that were not currently in use and

>   this seemed to have no impact when I thought reducing the size of the

>   data dictionary would reduce the use of the SGA.  Can anyone explain

>   this?  Suggest how to reduce my use of SGA?

>

>MUCH THANKS IN ADVANCE...Let's see if you can beat ORACLE to the solution

>even when they have a 1 week head start!

>

>           jim

>

>p.s.  I am running version 7.0.12 or the Oracle server on Ultrix 4.2A