Oracle Memory Management Methods

Automatic SGA Management

• You specify the total amount of SGA availabe using SGA_TARGET
• Oracle automatically distributes the memory among the various SGA components.
• SGA Components: Pools of memory used to satisfy different classes of memory allocation requests.
• Shared Pool (Library cache, Data Dictionary Cache, Server Result cache, reserved pool, Private sql areas, etc)
• Large Pool
• Database Buffer Cache
• Java Pool
• Streams Pool
• Fixed SGA
• If you are using an SPFILE, the database remember the sizes of the various components across instance shutdowns (no need to learn workload characteristics at each startup).
• Oracle allocates/deallocates memory in chuncks of allocation units (granules)
• If total sga size < 1G ==> granule = 4Mb.
• If total_sga_size > 1G ==> granule = 16Mb

1. Automatic Instance tuning New on Oracle 11g Set parameters: MEMORY_TARGET (dynamic) and MEMORY_MAX_TARGET (static and optional) Database redistributes memory as needed between SGA and PGA. Oracle automatically calculate the values for SGA_TARGET and PGA_TARGET. MEMORY_TARGET can be dynamically modified. If SGA_TARGET or PGA_AGGREGATE_TARGET are specified, then MEMORY_TARGET >= SGA_TARGET + PGA_AGGREGATE_TARGET

2. Automatic SGA and PGA tuning Until Oracle 10g, automatic memory management was configured for the SGA and PGA separately. The amount of memory allocated to both of them was fixed and unused space in one of them could not be used by the other. Set parameters: SGA_TARGET and SGA_MAX_TARGET (optionally) and PGA_AGGREGATE_TARGET

• You can query the V$SSGAINFO view to check the size of the various SGA components and see the allocation unit size (called granule size) that is being used by the instance.
• granule size is platform-specific and is determined by the total SGA size

SQL> select name, bytes, resizeable from v$sgainfo;

NAME                            BYTES      RES
------------------------------- ---------- ---
Fixed SGA Size                  1336260    No
Redo Buffers                    12582912   No
Buffer Cache Size               318767104  Yes
Shared Pool Size                385875968  Yes
Large Pool Size                 16777216   Yes
Java Pool Size                  16777216   Yes
Streams Pool Size               33554432   Yes
Shared IO Pool Size             0          Yes
Granule Size                    16777216   No
Maximum SGA Size                1272213504 No
Startup overhead in Shared Pool 67108864   No
Free SGA Memory Available       486539264

12 rows selected.

3. Automatic SGA and Manual PGA tuning Set parameters for SGA: SGA_TARGET and SGA_MAX_TARGET (optionally) Set parameters for PGA: SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE

4. Manual SGA and Automatic PGA tuning Set parameters for SGA: SHARED_POOL_SIZE DB_CACHE_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE Set parameters for PGA: PGA_AGGREGATE_TARGET

5. Fully manual (SGA and PGA) tuning Set parameters for SGA: SHARED_POOL_SIZE DB_CACHE_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE Set parameters for PGA: SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE