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
| 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 | # |
