Friday, October 19, 2012

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 :

Description of Figure 14-1 follows

* Image from Oracle

How to Check SGA size

show sga
show parameter sga
select * from V$SGA;
select * from V$SGAINFO;



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


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$'free memory' and v$'shared_pool_size' and v$sgastat.pool='shared pool'



  • 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.


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



  • 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.



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



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;


IceRocket Tags: ,,,



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates