3 Oracle SGA( System Global Area)

Each Oracle instance has two memory groups. SGA and PGA.SGA is the group of memory that dedicate to a Oracle instance. SGA includes :

• Share Pool
• Data Buffer Cache
• Log Buffer Cache
• Fixed SGA
• Large Pool
• Java Pool
• Steams Pool

* Image from Oracle

How to Check SGA size

show sga

show parameter sga

select * from V$SGA;

select * from V$SGAINFO;

V$SGA_INFO

The resize column means this can be manage by ASMM or AMM.

V$SGASTAT

Show the current SGA status.

select * from v$sgastat where name = 'free memory';

below query show shared pool free space

select to_number( v$parameter.value) value, v$sgastat.bytes/1024/1024 "V$sgastat MB",(v$sgastat.bytes/v$parameter.value)*100 "percent free" 

from v$sgastat,v$parameter

where v$sgastat.name='free memory' and v$parameter.name='shared_pool_size' and v$sgastat.pool='shared pool'

LOCK_SGA

• Lock the SGA in the memory so it wont be page out to the swap/page file.
• Can not use it with AMM. ASMM is ok.
• Static value. needs to use scope=spifle to change and then bounce instance.

PRE_PAGE_SGA

When oracle instance start, the oracle only reserve the memory address in the memory and does not allocate those memory. Only allocate those memory when SGA is needed. However, it is possible when oracle really needs those reserve memory but the memory has been use for other application. To avoid that, we can set PRE_PAGE_SGA is TRUE. When set to True, the oracle instance will allocate all the SGA size in the memory. which means the startup time for oracle instance will increase. This parameter does not guarantee the memory being page out/in. To avoid the page, we need to use LOCK_SGA.

PRE_PAGE_SGA is also static.

alater system set pre_page_sga=true;

shutdown immediate

startup

USE_INDIRECT_DATA_BUFFERS

• only for 32-bit OS.
• The 32-bit application has the limit only can address 2GB memory. This is to allow oracle instance to address more than 2GB memory ( max to 16GB on windows). This option is similar to AWE in MS SQL server.

Dynamic SGA/Granule

Granule is the min memory allocation unit inside the SGA memory management. when oracle instance dynamically assign/change the component’s size, oracle instance is use granule as the smaller unit to adjust.

The granule size is vary depends on the version , SGA_MAX_SIZE and OS.

Granule size	OS	SGA_MAX_SIZE
4M	Linux/Win	<= 1G
8M	Win	> 1G
16M	Linux	>1G

select component,current_size,granule_size from v$sga_dynamic_components;

If not PRE_PAGE_SGA and LOCK_SGA, oracle would use 3 granule.

• Fixed SGA and Redo Log Buffer
• Data Buffer Cache
• Share Pool

When use alter system to change the memory size, the size is increment by granule size. For example if granule size is 4MB. if we want to set SGA_TARGET to 403 MB, the actual size would change to 404MB.

KSM/KGH

Oracle Memory Management is managed by TWO modules, Kernel Service Memory (KSM) and Kernel Generic Heap( KGH).

KSM responds for

• Fixed SGA
• Database Buffer Cache
• Redo Log Buffer
• Share Pool

KGH responds for

• Library Cache
• PGA

select from KSMFS( Kernel Service Management Memory Fixed SGA ) can see the memory allocation.

select * from X$KSMFS

ASMM ( Automatic Shared Memory Management )

See here

AMM ( Automatic Memory Management)

See here.

Other useful views/package

ASMM	V$SGA_DYNAMIC_COMPONENTS	show components size that under ASMM management
AMM	V$MEMORY_DYNAMIC_COMPONENTS	show components size that under AMM management

 

ASMM	V$SGA_RESIZE_OPS	show last 800 completed memory resize operation for ASMM
AMM	V$MEMORY_RESIZE_OPS	show last 800 completed memory resize operation for AMM

 

ASMM	V$SGA_CURRENT_RESIZE_OPS	show the current memory resize operation for ASMM
AMM	V$MEMORY_CURRENT_RESIZE_OPS	show the current memory resize operation for AMM

 

ASMM	V$SGA_TARGET_ADVICE	advice SGA_TARGET_SIZE
AMM	V$MEMORY_TARGET_ADVICE	advice MEMORY_TARGET_SIZE

In order to use advice package, we need to set the statistic level to at least typical . The default value is typical.

show parameter statistics level

We can set the value to typical or all. to change the value, we can set it at session or system level by using

alter system set statistics_level=true;

alter session set statistics_level=true;

Reference

• http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php
• http://andrewfraserdba.com/training/32bit.html
• http://docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#i21738

IceRocket Tags: oracle,AMM,ASMM,memory