Oracle SGA Sizing
Introduction
By default, the database is created with an SGA size of 700MB (the memory in Oracle Shared Global Area for this database). To increase Oracle performance, increase this value.
According to Oracle directives, the cumulative amount of SGA sizes for all running databases on the server should be less than server RAM / 3. So, according to SGA sizes defined for all the present databases, as well as the server RAM, you can increase the DB SGA.
Checking the SGA Size
To check the SGA size:
- Log on as the oracle user.
- Enter the following commands to check the SGA size for database <DB_NAME>. In the following example, the SGA size for the connected database is 700 MB:
|
idle> show parameter memory **** Hit return to continue **** NAME TYPE VALUE -------------------------- ------------- -------------------- memory_max_target big integer 700M memory_target big integer 700M |
Increasing the SGA Size
Oracle SGA resizing should be performed by an Oracle expert only. When performing an upgrade, we recommend referencing the parameters of the DB used in a previous version (the version to be upgraded).
Oracle recommends that you set the SGA size to 1/3 of the RAM size on a non-dedicated DB server. The following is a table with recommended Oracle SGA sizes for installations where the DB and the application are installed on the same server:
| RAM Size (GB) | Oracle SGA Size (MB) |
|---|---|
| 8 | 2000 |
| 16 | 4000 |
| 32 | 10000 |
| 64 | Consult your DBA or Ex Libris Sizing Manager |
- To increase SGA size to 2000 MB (for example) for database <DB_NAME> for Oracle 11, type the following, as the oracle user:
|
> setenv ORACLE_SID <DB_NAME> > sqlplus /nolog idle> connect /as sysdba idle> alter system set MEMORY_MAX_TARGET=2000M SCOPE=spfile; idle> alter system set MEMORY _TARGET=2000M SCOPE=spfile; |
- Restart the database by entering the following commands:
|
idle> shutdown immediate idle> startup |
Solving an SGA Parameter Error
On Solaris 10, since a classic system parameter change in the /etc/system file has no effect, you may encounter problems when increasing the SGA size after a DB restart:
|
ORA-27102: out of memory SVR4 Error: 22: Invalid argument |
In this case, change the relevant OS memory parameter as the root user and run the following:
|
# projmod -s -K "project.max-shm-memory=(privileged, 10GB,deny)" group.dba |
If a problem occurs while setting this SGA parameter, define the appropriate Solaris project for Oracle by running the following commands as the root user:
|
# projadd group.dba # projmod -s -K "project.max-shm-memory=(privileged, 10GB,deny)" group.dba |
When this has been completed, restart the database.