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
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;
fake rolex uk are crafted from the finest raw materials and assembled with scrupulous attention to detail.fake rolex air king Every component is designed, developed and produced in-house to the most exacting standards.
ReplyDeletereplica omega de ville watches
ReplyDeletereplica omega globemaster watches
replica omega planet ocean watches
replica cartier santos watches
replica cartier santos dumont watches
replica cartier santos dumont 7 watches
replica cartier tank americaine watches
replica cartier tank francaise watches
replica cartier tank louis watches
replica iwc 150 years watches
cheap balenciaga trainers. The innovative designs of Spanish high fashion designer Cristò bal cheap balenciaga speed clear sole sneaker are known for their sculptural volume and strict modernity.
ReplyDelete