SGA (System Global Area)
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.
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.
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;
Fixed Size 456424
Variable Size 503316480
Database Buffers 134217728
Redo Buffers 679936
The size of the SGA is controlled by the DB_CACHE_SIZE parameter.
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.
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;