Each Oracle instance has two memory groups. SGA and PGA.SGA is the group of memory that dedicate to a Oracle instance. SGA includes :
* Image from Oracle
How to Check SGA size
The resize column means this can be manage by ASMM or AMM.
Show the current SGA status.
below query show shared pool free space
- 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.
- 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.
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.
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
select from KSMFS( Kernel Service Management Memory Fixed SGA ) can see the memory allocation.
select * from X$KSMFS
ASMM ( Automatic Shared Memory Management )
AMM ( Automatic Memory Management)
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.
We can set the value to typical or all. to change the value, we can set it at session or system level by using