SGA

From Oracle FAQ

Jump to: navigation, search

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.

Contents

[edit]

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.
[edit]

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 DB_CACHE_SIZE parameter.

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

[edit]

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;
[edit]

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 #
Retrieved from "http://www.orafaq.com/wiki/SGA"
Personal tools