Memory Management in Oracle

Memory Management in Oracle

Memory is divided into 3 broad categories

1.UGA – User Global Area

2.PGA – Process Global Area

3.SGA – System Global Area

User Global Area – UGA is related to user session. So depending on connection type i.e. Shared/dedicated, its location is decided.

Process Global Area – PGA is related to Oracle processes. It consists of UGA, sorting area, hashing area, bitmap merging etc. Its management can be auto/manual. Decided by param “workarea_size_policy”.

Manual PGA Memory Management –

Set workarea_sort_policy to manual.

Sort_area_size – RAM used for sorting. Extra information will be swapped to memory

Sort_area_retained_size – Memory used to hold sorted data after operation. Is part of sort area. Is located inside UGA.

Sort_area_size > sort_area_retained_size.

Sort_area_size-sort_area_retained_size is inside PGA.

Hash_area_size – sorting hash tables in memory. Typically used while performing hash joins.

Auto PGA Memory Management –

Set workarea_size_policy to auto.

Set pga_aggregate_target > 0.

Pga_aggregate_target is just an indicative upper bound. If required, system may extend that limit.

As no of session increases in this mode, memory utilization benefit is observed. If PGA target limit is reached, subsequent sorting happens on disk. Experiment.

PGA ----------> UGA

-------------- Session specific state

----------> sorting

----------> hashing

System Global Area – On *nix SGA can be seen as shared memory segment.

Command : ipcs –m | grep ora

On *nix, SGA is allocated as chunk of memory and then shared across oracle processes.

On windows, SGA is allocated under process area of Oracle and all other oracle processes are forked as threads under oracle.exe.

It consists of following components:

•Java pool : memory allocated to JVM

•Large pool : shared server connections/ parallel executions/ for message buffers

•Shared pool : shared cursors/stored...