SGA VS PGA 

---------------------------------------------------------------------------------


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


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.


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.


PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.
[edit] Auto tuning
PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.

To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.

PGA usage statistics:

select * from v$pgastat;

Determine a good setting for pga_aggregate_target:

select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process:

select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

A good explanation was given on SGA on following link :







https://community.oracle.com/thread/904540



Happy blogging :)