Dynamic SGA

[Former Member]

Hi All,

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?

Regards,

[Former Member · Accepted solution]

>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?

Hello!

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

WORKAREA_SIZE_POLICY=AUTO

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>

HND -)

[stefan_koehler · Accepted solution]

Hello Alexey,

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

Yes you can, but in my experience it didn't work very well (several bugs with shrinking buffer caches, etc.).

Regards

Stefan

[Former Member · Accepted solution]

GT, Stefan!

As i told - the very good idea is upgrade to 10.2.0.4. The most bugs related to ASSM was fixed.

HND!

[Former Member]

Hi Arvind,

Yes you are correct. Setting db_cache_size to size the Oracle data buffer implicitly switches on Dynamic SGA. Setting db_cache_size is incompatible with the old (and static) parameter db_block_buffers: that one must be deleted from the profile or set to 0. Trying to set them both at the same time will result in an ORA-00381 error.

With Dynamic SGA enabled the data buffer (db_cache_size) and the shared pool (shared_pool_size) can be resized dynamically without having to stop the database. Their sum must remain below the value of the (static) parameter sga_max_size.

Regards,

Mark

[Former Member]

Hi

Check this link may help you

http://help.sap.com/erp2005_ehp_04/helpdata/EN/c4/3a72d9505211d189550000e829fbbd/frameset.htm

Regards

Uday

[Former Member]

Hello Arvind,

SGA is a feature of Oracle starting from 9i,so it is not that you have to activate SGA,it is already activated since Oracle 9i

Rohit