Oracle Program Global Area

1.  its mainly used for sorting purpose
2. A PGA is nonshared memory created by Oracle Database when a server or background process is    started.
3. Using v$session we can check whether dedicated server or shared server
4. One pga for each server process
5. One PGA exists for each Server Process and each Background Process.
6. It stores data and control information for a single Server Process or a single Background Process.
7. The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process.        

Monitor

GA 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;

Pga memory divede into three area

1. SESSION MEMORY
2. PRIVATE SQL AREA
3. SQL WORK AREA

1.Session Memory:/session information:

Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session.

for eg:
deine_editor=vi
set serveroutput on;
set autotrace on
set pagesize;
set linesize;

monitor session information:-

we can check total PGA memory used by processes using this below query

SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

we can find PGA usage for a specific session using this below query

SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM 
v$sesstat a,  v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# 
AND SID = 80;

To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:

SELECT :MAX_CONNECTED_SESSIONS*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB 
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';

2.private sql area:-

•  Stores information for a parsed SQL statement – stores bind variable values and runtime memory allocations.
• Cursor information also maintained in this area

Dedicated Server environment  – the Private SQL Area is located in the Program Global Area.

  

 Shared Server environment      – the Private SQL Area is located in the System Global Area.

However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA

Private sql area have two part

i)PERSISTENT  AREA:-

The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas,The persistent area contains the bind variable values and this area is released only when the cursor is closed.

ii)Runtime memory:

The runtime area contains information about an executed query, including the progress of a full table scan and the current state of the SQL work areas. These PGA areas are allocated to run RAM intensive operations such as sorting and hash-joins. For DML operations, a runtime area is released when the command completes.

Tempory information,parameters.

Query execution statment info...etc

3.SQL WORK AREA:-

• These areas are allocated as needed for memory-intensive operations like sorting or hash-joins.
• Memory allocated for sort, hash-join, bitmap merge, and bitmap create types of operations.
• For DML, the run-time area is freed when the statement finishes running.For queries, it is freed after all rows are fetched or the query is canceled.
• Oracle 9i and later versions enable automatic sizing of the SQL Work Areas by setting the WORKAREA_SIZE_POLICY = AUTO parameter (this is the default!)

PGA parameters:-

SORT_AREA_SIZE=size           ---order by,group by,roll up,window   functions                                                      
HASH_AREA_SIZE=size           ---where   clause                                                                                                           
BITMAP_MERGE_AREA_SIZE=size   ---index merge area                                                                                                                 
CREATE_BITMAP_AREA_SIZE =size ---Index creation area

1.the above parameters from 9i,
PGA_AGGREGATE_TARGET = size are individually and seperately allocated manually
2. the above parameters from 10g,
PGA_AGGREGATE_TARGET = size
WORKAREA_SIZE_POLICY = auto/manual
if you set above workarea_size_policy=manual then set size above all parameter manually otherwise f you set workarea_size_policy=auto then no need to set size above all parameter it automatically set memory all the  parameter
3.the above parameters from 11g(AMM),
MEMORY_TARGET=SGA+PGA
MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

# df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                  1029884    350916    678968  35% /dev/shm
#

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.
ORA-00845: MEMORY_TARGET not supported on this system

To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.

# umount tmpfs
# mount -t tmpfs shmfs -o size=1200m /dev/shm

Make the setting permanent by amending the "tmpfs" setting of the "/etc/fstab" file to look like this.

 tmpfs                   /dev/shm                tmpfs   size=1200m   0  0

Configuration Of AMM
The Database Configuration Assistant (DBCA) allows you to configure automatic memory management during database creation.

When creating the database manually, simply set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database.

Enabling automatic memory management on a system that didn't previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.

MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")

The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.

Individual values.

COLUMN name FORMAT A30
COLUMN value FORMAT A10

SELECT name, value
FROM   v$parameter
WHERE  name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';

Calculate MEMORY_TARGET

SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
     (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,
     (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;

Assuming our required setting was 5G, we might issue the following statements.
CONN / AS SYSDBA

Set the static parameter. Leave some room for possible future growth without restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;

Set the dynamic parameters. Assuming Oracle has full control.

ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

Restart instance.
SHUTDOWN IMMEDIATE;
STARTUP;

Once the database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

AMM MONITOR
SELECT  component, current_size, min_size, max_size
FROM    v$memory_dynamic_components
WHERE   current_size != 0;
COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool                       197132288  192937984  197132288
large pool                          4194304    4194304    4194304
java pool                          41943040   41943040   41943040
SGA Target                        318767104  285212672  318767104
DEFAULT buffer cache               71303168   41943040   75497472
PGA Target                        104857600  104857600  138412032

6 rows selected.

The V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS views provide information on current and previous component resize operations.

The V$MEMORY_TARGET_ADVICE view provides information to help tune the MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET settings, as factors of the current setting, and estimates the potential DB Time to complete the current workload based on these memory sizes.

SELECT * FROM v$memory_target_advice ORDER BY memory_size;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        303                .75         3068              1.0038          2
        404                  1         3056                   1          2
        505               1.25         3056                   1          2
        606                1.5         3056                   1          2
        707               1.75         3056                   1          2
        808                  2         3056                   1          2

6 rows selected.

PGA VIEWs:

Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:
V$SYSSTAT

V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE

The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:
PGA_USED_MEM

PGA_ALLOCATED_MEM
PGA_MAX_MEM
MEMORY MANAGEMENT IN ORACLE:
1)Automatic memory management:
>DBA specifies the target size for instance memory.
>The database instance automatically tunes to the target memory size.
>Database redistributes memory as needed between the SGA and the instance PGA.
2) Automatic shared memory management:
>This management mode is partially automated.
>DBA specifies the target size for the SGA.
> DBA can optionally set an aggregate target size for the PGA or managing PGA work areas individually.
3)Manual memory management:
 Instead of setting the total memory size, the DBA sets many initialization parameters to manage components of the SGA and instance PGA individually
NOTE: if pga is fill the all queries will go to temporay tablespace that is disk level sorting