The relationship between two parameters in oracle three two AMM Mechanism

Oracle three on the relationship between memory parameters 2AMM mechanism involves the relationship between a pair of parameters to explore the AMM mechanism involves a pair of parameters: MEMORY_TARGET and MEMORY_MAX_TARGET. Memory_targetdefavaluvalue0 (SGAautotuningisdisabledforDEFERREDmodeautotuningrequests, butallowedforIMME

Oracle three on the relationship between memory parameters 2AMM mechanism involves the relationship between a pair of parameters to explore the AMM mechanism involves a pair of parameters: MEMORY_TARGET and MEMORY_MAX_TARGET. MEMORY_TARGET Default value 0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMME

The relationship between two parameters in oracle three two AMM Mechanism

The AMM mechanism involves one pair of parameters: MEMORY_TARGET and MEMORY_MAX_TARGET.

MEMORY_TARGET

Default value	0(SGA autotuning is disabledDEFERREDMode autotuning requests, but allowedIMMEDIATEMode autotuning requests)
Modifiable	Alter system (that is, this parameter is a dynamic parameter)
Range of values	152 MBMEMORY_MAX_TARGET

See http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams143.htm#I1010285

MEMORY_MAX_TARGET

Default value	0
Modifiable	No(That is, this parameter is a static parameter)
Range of values	0To the physical memory size available to the Oracle Database

See http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams142.htm

The above description shows that,

1. When MEMORY_TARGET = is not 0, MEMORY_TARGET must be 152 MBMEMORY_MAX_TARGETWithin this range.

2,

Because MEMORY_TARGET always <=MEMORY_MAX_TARGET, So

When MEMORY_TARGET = 0,MEMORY_MAX_TARGETThe value range is0To the physical memory size available to the Oracle Database.

When MEMORY_TARGET = Not 0, MEMORY_TARGET must be 152 MBMEMORY_MAX_TARGETWithin this range, soMEMORY_MAX_TARGETThe value range is 152 MB to the physical memory size available to the Oracle Database.

Note:

The MEMORY_TARGET value must be greater than or equal to the sum of sga_target and pga_aggregate_target..

For example,

WhenSga_target = 744 M, pga_aggregate_target = 556 M, MEMORY_TARGET = 1400M,

SQL & gt; alter system set memory_target = 115 m;

Alter system set memory_target = 115 m

*

Row 3 has an error:

ORA-02097: The parameter cannot be modified because the specified value is invalid

ORA-00838: the value of the specified MEMORY_TARGET is too small, at least 1400 M

Note:

"ORA-00838: the value of the specified MEMORY_TARGET is too small, at least 1400M" 1400 M in the error message, which is equal to the sum of sga_target and pga_aggregate_target,

Because MEMORY_TARGET = sum of sga_target and pga_aggregate_target.

MEMORY_TARGET and MEMORY_MAX_TARGETThe relationship between the two is

0. MEMORY_TARGET always <=MEMORY_MAX_TARGET.

For example, When MEMORY_MAX_TARGET = memory m,

SQL & gt; alter system set memory_target = 120 m;

Alter system set memory_target = 120 m

*

Row 3 has an error:

ORA-02097: The parameter cannot be modified because the specified value is invalid

ORA-00837: the specified value of MEMORY_TARGET is greater than MEMORY_MAX_TARGET

1. When MEMORY_TARGET = is not 0 (that is, when AMM is started)

1.1

When MEMORY_TARGET = Not 0 in the parameter file,MEMORY_MAX_TARGET= If the value is not 0, the instance is started successfully.

1.2

When MEMORY_TARGET = Not 0 in the parameter file,MEMORY_MAX_TARGET= 0, the instance is started successfully.

After the instance is started, the value of MEMORY_TARGET is automatically assignedMEMORY_MAX_TARGET. That is

In a text-based initialization parameter file, if you omitMEMORY_MAX_TARGETAnd include a valueMEMORY_TARGET, Then the database automatically setsMEMORY_MAX_TARGETTo the valueMEMORY_TARGET.

2. When MEMORY_TARGET = 0 (that is, when AMM is disabled)

2.1

When MEMORY_TARGET = 0 in the parameter file,MEMORY_MAX_TARGET= If the value is not 0, the instance is started successfully.

After the instance is started, view the MEMORY_TARGET and MEMORY_MAX_TARGETBoth are original values (use showparameters mem or select * from v $ parameter where name like '% mem % ';). That is

If you omit the lineMEMORY_TARGETAnd include a valueMEMORY_MAX_TARGET,MEMORY_TARGETParameter defaults to zero. After startup, you can then dynamically changeMEMORY_TARGETTo a nonzero value, provided that it does not exceed the valueMEMORY_MAX_TARGET. (Detailed article)

2.2

When MEMORY_TARGET = 0 in the parameter file,MEMORY_MAX_TARGET= 0, the instance is started successfully.

After the instance is started, view the MEMORY_TARGET and MEMORY_MAX_TARGET(Use showparameters mem or select * from v $ parameter where name like '% mem %';), you can find MEMORY_MAX_TARGETIs adjusted to a non-0 value.

The following is an example of a specific experiment:

Parameter file:

*. Memory_max_target = 0

*. Memory_target = 0

*. Pga_aggregate_target = 100 M

*. Sga_max_size = 0

*. Sga_target = 0

SQL> startuppfile = E: \ app \ hc \ product \ 11.2.0 \ dbhome_1 \ database \ INITmonkey. ORA

The ORACLE routine has been started.

Total System Global Area 150667264 bytes

Fixed Size 1373152 bytes

Variable Size 92277792 bytes

Database Buffers 50331648 bytes

Redo Buffers 6684672 bytes

The database has been loaded.

The database has been opened.

SQL> select name, value from v $ parameter where name like '% target %' orname like

'% Sga % ';

NAME VALUE

--------------------------------------------------------------------------------

Sga_max_size 150994944 (144 M) (value changed)

Pre_page_sga FALSE

Lock_sga FALSE

Sga_target 0

Memory_target 0

Memory_max_target 121634816 (116 M)(Value changed)

Archive_lag_target 0

Fast_start_io_target 0

Fast_start_mttr_target 0

Db_flashback_retention_target 1440

Pga_aggregate_target 104857600

Parallel_servers_target 32

12 rows have been selected.

SQL> create pfile from memory;

The file has been created.

【

In the parameter file obtained by this command:

Sga_max_size = 144 M# Internallyadjusted

Sga_target = 0

]

The results of the preceding two statements show that,

Sga_max_size 150994944 (144 M)

Sga_max_size = 144 M# Internallyadjusted

Memory_max_target 121634816 (116 M)

In the parameter file, when memory_max_target = 0 and memory_target = 0, memory_max_target is changed to a non-0 value and memory_max_target is a static parameter, the value modified during the running of the Instance does not work during the running of the instance. Therefore, during the running of the instance, memory_max_target is always non-zero, memory_max_target must be greater than memory_target (this is also the case in the parameter file when the instance is started, and oracle will check whether this condition is met)

When sga_max_size = 0 and sga_target = 0, oracle automatically adjusts the sga_max_size value after the instance is started.

As for memory_max_target, it is unknown whether oracle will automatically adjust the value or write a value at will after the instance is started .???

Memory_target = 0Sga _The target value can be 0 because memory_target = 0Sga _Target is 0, indicating that sga is manually managed, that is, the sum of parameters such as shared_pool_size and so on is sga.

Additional

MEMORY_MAX_TARGET is a static parameter. Therefore, the modified value of MEMORY_MAX_TARGET takes effect at the next Startup even when the instance is running. Therefore, you do not need to test the effect of modifying the static parameter of MEMORY_MAX_TARGET on MEMORY _ ARGET during instance running..