I read somewhere that to check whether Dynamic SGA is activated or not you have to check the following;
DB_BLOCK_BUFFERS is set to some value and DB_CACHE_SIZE is set to 0 means Dynamic SGA is not activated.
Am I correct? Is there any other way to check whether it is activated or not?
Alexey Spirichev replied
>I read somewhere that to check whether Dynamic SGA is activated or not you have to check the following;
>DB_BLOCK_BUFFERS is set to some value and DB_CACHE_SIZE is set to 0 means Dynamic SGA is not >activated.
>Am I correct? Is there any other way to check whether it is activated or not?
Don't mix the dynamic parameters and dynamic memory allocation in your mind.
1. The upper limit of the SGA size is SGA_MAX_SIZE parameter for 9i, 10g.
2. In 9i version there are dynamic PARAMETERS, not entire SGA. This mean you can online adjust the size of pools only.
9.0 : Shared Pool, Default Buffer Cache
9.2: Shared Pool, Default Buffer Cache, Large Pool
PGA automaticaly tuned by Oracle Instance according the parameter PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY.
PGA_AGGREGATE_TARGET=3G #as example
3. Starting with 10g version you can use automatic memory management (ASMM). This feature contol the entire SGA including size of certain pool without manual operation.
If dynamic parameter SGA_TARGET have non-zero value, this mean SGA size, buffer cache, shared pool, large pool,java pool and streams pool (since 10.2) sized automaticaly.
You can precisely adjust the certain pool in case.
If SGA_TARGET=0 (default value) - ASMM disabled.
If you plan to use ASSM, don't miss:
- upgrade to 10.2.0.4 ( in earlier version was the bug related to memory broker, possibly the significant perfomance degradation)
- to set SGA_MAX_SIZE=<size> # this parameter is not dynamic,can be applid after restart instance.
- to set STATISTICS_LEVEL to TYPICAL or ALL
- to set SGA_TARGET=<size>