Increase the size of the SGA

UDPATE: If you're looking for the actual commands on how to do this, view this post (click on the following link):

Otherwise, read on to see what people say is the best way to determine the actual SGA size.

If you've installed Oracle Enterprise Manager, you've probably seen the following error:

Increase the size of the SGA by setting the parameter "sga_target" to 1800 M.

(The size might not be the same).

Personal Notes

• Changing the SGA_TARGET is possible while the system is up, but it cannot be higher than the sga_max_target
• Changing the SGA_MAX_TARGET requires a restart of the DB it looks like
• How to see all the sga parameters/settings:

select * from v$sga;

• How to see your current SGA_TARGET value:

select sum(value)/1024/1024 "Megabytes" from v$sga;

• The Rule of Thumg (ROT) is assigning 40% of your memory to the SGA value, BUT, see the Ask Tom article below…

Here are a few links to useful info regarding this issue.

From Tuning Oracle

Taking a look at your current settings:

Oracle Database 10g Release 2 can automatically tune the size of the shared pool, buffer cache, Java pool, large pool, and streams pool. However, you must still manually tune several SGA components, including the log buffer (at startup time only), the keep buffer cache, the recycle buffer cache, and all of the nonstandard block-size buffer caches (for example, the 32K and 16K buffer caches set by the db_32K_cache_size and db_16K_cache_size parameters).
Manually sized components consume SGA from the target value first, and then the remainder of SGA memory is spread across the various autosized pools. In other words, if you set parameter values for any of these manually tuned pools, Oracle Database 10g Release 2 subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools.
The SGA_TARGET setting value also includes a small amount of fixed-size SGA. Oracle Database 10g Release 2 sets the fixed size based on the operating system and other criteria. You can see the amount (in bytes) of the fixed-size SGA and totals of other major elements that occupy the SGA by querying the V$SGA view, as follows:

SQL> select * from v$sga;
NAME                      VALUE
------------------        ----------
Fixed Size                1247780
Variable Size             124319196
Database Buffers          41943040
Redo Buffers              262144

You can query this same view to determine an initial size for SGA_TARGET when you switch from manual to autotuning, by summing all the components as follows:

SQL> select sum(value)/1024/1024 "Megabytes" from v$sga;

Megabytes
------------------
160

Listing 1 shows an example of total real memory allocation for the current SGA from the V$SGA_DYNAMIC_COMPONENTS view (introduced in Oracle9i Database), which contains both manual and autotuned SGA components.
Code Listing 1: Query of V$SGA_DYNAMIC_COMPONENTS

SQL> select component, current_size from v$sga_dynamic_components;

COMPONENT                         CURRENT_SIZE
-------------------------------   -------------------------
shared pool                       92274688
large pool                        8388608
java pool                         8388608
streams pool                      12582912
DEFAULT buffer cache              33554432
KEEP buffer cache                 4194304
RECYCLE buffer cache              4194304
DEFAULT 2K buffer cache                0
DEFAULT 4K buffer cache                0
DEFAULT 8K buffer cache                0
DEFAULT 16K buffer cache               0
DEFAULT 32K buffer cache               0
ASM Buffer Cache                       0

13 rows selected.

From Ask Tom

About estimating your SGA and the Rule of Thumb:

Your DBA's rule of thumb (ROT) here is "you want to use 40/50% of RAM for the SGA leaving the other 50% for the dedicated servers (processes — they allocate PGA) and 10% or so for the OS and related processes"
That is, their ROT is how to maximize the use of memory for a typical dedicated server configuration.
That ROT is totally wrong if you are using shared server (there the SGA might be 80-90% of RAM)
That ROT is totally wrong if you are not the only game in town (not the only thing on the server).
That ROT does not mean you are using only 40% of the additional gig of ram you are putting in, it means "40% of it will go towards the SGA, the remaining will be available for dedicated servers to do sorts and hashes and stuff"
but the major considerations here are:
a) how much do you want to assign to your buffer cache for maximum performance
b) how big is your shared/java pool (a function of how much sql/plsql/java you run in your database, no magical number for all to use)
c) do you run in shared server (than the large pool is used and will be large — that is part of the sga) or in dedicated server — then you need to leave OS memory for dynamic allocations
d) what else is going on in the machine.

Do we need to set the various memory params?:

Hi
So if we set sga_target we should forget about setting parameters for shared pool, large pool, java
pool and buffer cache correct?
Followup   December 20, 2004 – 10am Central time zone:
that would be the prefered way, you can set the others — then they are used as "minimums" — but in for a penny — in for I pound on this one I say
you would either
o use auto SGA sizing
o or not

How to see how much SGA is used:

select * from v$sgastat;

or

select  round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
from
v$sgastat;

What is the Fixed Size SGA?:

Followup   July 24, 2009 – 8am Central time zone:
<quote src=Expert Oracle Database Architecture>
Fixed SGA
The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is "compiled" into the Oracle binary itself at installation time (hence the name "fixed"). The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a `bootstrap¿ section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
</quote>
the variable part is everything else that isn't the buffer cache, the fixed size, the redo buffers – it includes things like the shared pool, large pool, java pool, etc.

ops$tkyte%ORA10GR2> select pool, sum(bytes) from v$sgastat where pool is not null group by pool
order by pool;

POOL         SUM(BYTES)
------------ ----------
java pool      16777216
large pool     16777216
shared pool   268440148

Conclusion
I think that's enough for now.
But let me end with a quote from Tom about whether to manually adjust the settings or let the system automatically manage it:

For a system that has a DBA dedicated to the care and feeding thereof, manual will probably be used for the immediate future. For the system that runs on a box in the corner that you don't have time to really look at closely, auto will rule the day. (opinion). I'm finding on "ok systems", the automatic is doing all right. I haven't heard of any horror stories — but I haven't really seen personally a big system doing it as yet. Some time to get acceptance on it I guess.