SGA

The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.

SGA Components

In general, the SGA consists of the following sub-components, as can be verified by querying the V$SGAINFO:

SELECT * FROM v$sgainfo;

The common components are:

• Data buffer cache - cache data and index blocks for faster access.
• Shared pool - cache parsed SQL and PL/SQL statements.
• Dictionary Cache - information about data dictionary objects.
• Redo Log Buffer - committed transactions that are not yet written to the redo log files.
• JAVA pool - caching parsed Java programs.
• Streams pool - cache Oracle Streams objects.
• Large pool - used for backups, UGAs, etc.

SGA Size

Here are two methods that can be used to determine the current SGA's size. All values are in bytes:

SQL> SHOW SGA
Total System Global Area  638670568 bytes
Fixed Size                   456424 bytes
Variable Size             503316480 bytes
Database Buffers          134217728 bytes
Redo Buffers                 679936 bytes

SQL> SELECT * FROM v$sga;
NAME                      VALUE
-------------------- ----------
Fixed Size               456424
Variable Size         503316480
Database Buffers      134217728
Redo Buffers             679936

The size of the SGA is controlled by the SGA_TARGET and SGA_MAX_TARGET parameters.

SQL> select * from v$sgainfo;
NAME                             BYTES                  RESIZEABLE 
-------------------------------- ---------------------- ---------- 
Fixed SGA Size                   2109352                No   
Redo Buffers                     13533184               No    
Buffer Cache Size                3103784960             Yes 
Shared Pool Size                 822083584              Yes  
Large Pool Size                  67108864               Yes 
Java Pool Size                   134217728              Yes  
Streams Pool Size                134217728              Yes 
Shared IO Pool Size              0                      Yes 
Granule Size                     16777216               No 
Maximum SGA Size                 4277059584             No 
Startup overhead in Shared Pool  251658240              No 
Free SGA Memory Available        0      

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

Dynamic sizing

When automatic shared memory management is enabled, Oracle will adjust the memory parameters on the fly. To see currently allocated sizes:

select * from v$sga_dynamic_components;

Also see

• Database Concepts and Architecture
• PGA and UGA

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#