Memory_target for Oracle initialization parameters

Memory_target for Oracle initialization parameters

I. Introduction:

Oracle 9i introduces pga_aggregate_target to automatically adjust the PGA;

Oracle 10 Gb introduces sga_target to automatically adjust SGA;

Oracle 11g combines these two parts and introduces memory_target to automatically adjust all the memory. This is the new automatic memory management feature.

Ii. Description:

Operating System: rhel 5.4x32

Database: oracle 11g r2

Iii. Introduction to memory_target:

3.1 The following example shows the relationship between the settings of memory_target and PGA and SGA:

SQL> show parameter memory_target;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Memory_target big integer 316 M
SQL> show sga;

Total System Global Area 330600448 bytes
Fixed Size 1336344 bytes
Variable Size 247466984 bytes
Database Buffers 75497472 bytes
Redo Buffers 6299648 bytes
SQL> alter system set memory_target = 200 m scope = spfile;

System altered.

SQL> alter system set sga_target = 0 scope = spfile;

System altered.

SQL> alter system set pga_aggregate_target = 0 scope = spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 201330456 bytes
Database Buffers 4194304 bytes
Redo Buffers 2375680 bytes
Database mounted.
Database opened.

After the memory_target parameter is set, Oracle will automatically set and adjust the two parameters to allocate SGA and PGA memory, this mechanism is the same as that for allocating db_cache_size and shared_pool_size after Oracle automatically sets sga_target for 10 Gb.

SQL> col ksppinm for a20;
SQL> col ksppstvl for a20;
SQL> select a. ksppinm name, B. ksppstvl value
2 from x $ ksppi a, x $ ksppcv B
3 where a. indx = B. indx
4 and (a. ksppinm like '% sga_target %'
5 or a. ksppinm like '% pga_aggregate_target % ');

NAME VALUE
------------------------------------------
Sga_target 0
_ Sga_target 142606336
Pga_aggregate_target 0
_ Pga_aggregate_target 67108864

3.2 discuss the impact of memory_target setting and not setting on SGA/PGA in 11g:

3.2.1 if memory_target is set to a non-0 value

(In the following four cases, the size of SGA and PGA is allocated)

3.2.1.1, sga_target, and pga_aggregate_target

If the sga_target and pga_aggregate_target parameters have been set in Oracle, these two parameters will be allocated to their respective minimum values as their target values.

Memory_target = sga_target + pga_aggregate_target. The size is the same as that of memory_max_size.

3.2.1.2. sga_target: set the size. pga_aggregate_target is not set.

Then pga_aggregate_target initialization value = memory_target-sga_target

3.2.1.3. No size is set for sga_target and pga_aggregate_target.

So the sga_target initialization value = memory_target-pga_aggregate_target

3.2.1.4, sga_target and pga_aggregate_target are not set.

If neither sga_target nor pag_aggregate_target is set in Oracle 11g, Oracle does not have the minimum and default values for these two values. Oracle allocates the size based on the database running status. However, when the database is started, a fixed proportion is allocated:

Sga_target = memory_target * 60%

Pga_aggregate_target = memory_target * 40%

3.2.2 If memory_target is not set or = 0 (the default value is 0 in 11g)

If the default value is 0 in 11g, The memory_target function is canceled in the initial state. It is completely consistent with 10g in memory management and completely backward compatible. (SGA and PGA are allocated in three cases)

3.2.2.1 when sga_target is set, the size of memory space such as shared pool, buffer cache, redo log buffer, java pool, and larger pool in SGA is automatically adjusted. PGA depends on the size of pga_aggregate_target. Sga and pga cannot automatically increase or decrease.

3.2.2.2, sga_target, and pga_aggregate_target are not set.

The size of each component in SGA must be clearly set. The size of each component cannot be adjusted automatically. PGA cannot automatically increase or contract.

3.2.2.3. If memory_max_target is set and memory_target is set to 0, this is the same as that of 10 Gb.

For more details, please continue to read the highlights on the next page:

1 2 Next Page