Memory_target of Oracle Initialization parameters

First, Introduction:

Oracle 9i introduces Pga_aggregate_target to automatically adjust the PGA;

Oracle 10g introduces Sga_target to automatically adjust the SGA;

Oracle 11g combines these two parts, introducing Memory_target, which automatically adjusts all memory, which is the newly introduced automatic memory management feature.

Second, this article explains:

Operating system: Rhel 5.4 x32

Database: Oracle 11g R2

Iii. Introduction of Memory_target:

3.1. Below is an example of how the Memory_target is set up with the PGA and SGA relationships:

Sql>Show parameter Memory_target;name TYPE VALUE------------------------------------ ----------- ------------------------------Memory_target Biginteger316MSQL>show SGA; Total System Global Area330600448bytesfixed Size1336344bytesvariable Size247466984bytesDatabaseBuffers75497472Bytesredo Buffers6299648Bytessql> AlterSystemSetMemory_target=200m scope=SPFile; System altered. SQL> AlterSystemSetSga_target=0Scope=SPFile; System altered. SQL> AlterSystemSetPga_aggregate_target=0Scope=SPFile; System altered. SQL> shutdownimmediate;Databaseclosed.Databasedismounted. ORACLE instance shut down. SQL>Startuporacle instance started. Total System Global Area209235968bytesfixed Size1335528bytesvariable Size201330456bytesDatabaseBuffers4194304Bytesredo Buffers2375680bytesDatabasemounted.DatabaseOpened.

After setting the Memory_target parameter, Oracle automatically sets and adjusts two parameters to allocate the SGA and PGA memory, which is assigned Db_cache_size and Shared_pool_ after Oracle 10g automatically sets Sga_target The mechanism for size is the same.

Sql>Col KSPPINM forA20; SQL>Col KSPPSTVL forA20; SQL> Selecta.ksppinm NAME,B.KSPPSTVL Value2      fromX$ksppi A,X$KSPPCV b3   whereA.indx=B.indx4       and(A.KSPPINM like '%sga_target%'  5  orA.ksppinm like '%pga_aggregate_target%'); NAME VALUE-----------------------     -------------------Sga_target0__sga_target142606336Pga_aggregate_target0__pga_aggregate_target67108864

3.2. Discuss the effect of memory_target setting and not setting on SGA/PGA in 11g:

3.2.1, if Memory_target is set to a value other than 0

(There are four scenarios below to allocate the size of the SGA and PGA)

3.2.1.1, Sga_target, and Pga_aggregate_target have been set to size

If the parameters Sga_target and Pga_aggregate_target are already set in Oracle, the two parameters will each be assigned a minimum value of their target value.

Memory_target = Sga_target + pga_aggregate_target, size and memory_max_size consistent.

3.2.1.2, Sga_target set size, pga_aggregate_target not set size

Then Pga_aggregate_target initializes the value of =memory_target-sga_target

3.2.1.3, Sga_target not set size, Pga_aggregate_target set size

Then Sga_target initializes the value of =memory_target-pga_aggregate_target

3.2.1.4, Sga_target, and Pga_aggregate_target are not set to size

Oracle 11g does not have a set size for this sga_target and Pag_aggregate_target, Oracle will have no minimum and default values for these two values. Oracle allocates the size based on the database health. But at the start of the database there will be a fixed scale to allocate:

Sga_target = memory_target*60%

Pga_aggregate_target = memory_target*40%

3.2.2, if Memory_target is not set or = 0 (default is 0 in 11g)

The default in 11g is 0, which cancels the function of memory_target in the initial state, fully and 10g in memory management, completely backwards compatible. (There are also three cases where the size of the SGA and PGA is allocated)

3.2.2.1, Sga_target sets the value, automatically adjusts the size of the memory space such as shared pool,buffer Cache,redo log Buffer,java Pool,larger pool in the SGA. The PGA relies on the size of the pga_aggregate_target. The SGA and PGA do not automatically grow and shrink automatically.

3.2.2.2, Sga_target and Pga_aggregate_target are not set

The size of each component in the SGA must be explicitly set, and the size of each component cannot be adjusted automatically. The PGA does not automatically grow and shrink.

3.2.2.3, memory_max_target settings and Memory_target = 0 This situation is the same as 10g, do not explain.

Third, ORA-00845

Finally talk about the origin and solution of ORA-00845:

If the memory_max_target/memory_target is set too large, it may cause instance to fail to start, and the ORA-00845 error is reported.

[[email protected] bin]$ Oerr ora0084500845,00000, "Memory_target notSupported onThis system "// *Cause:the Memory_target parameter was notSupported onThis operating systemor /Dev/SHM was notsized// *Action:refer toDocumentation forA list ofSupported operating systems.Or, size/Dev/Shm toBe at Leacle instance running onThe system. SQL> AlterSystemSetMemory_max_target=400m scope=SPFile; System altered. SQL> shutdownimmediate;Databaseclosed.Databasedismounted. ORACLE instance shut down. SQL>Startupora-00845: Memory_target notSupported onThis system

This error is somewhat misleading, in fact this does not mean that the AMM feature is not supported on the platform version, but that the memory_max_target is set beyond the share memory (/DEV/SHM) set in the system.

[[email protected] bin] - Hfilesystem            Size  use%ontmpfs                 395M     0  395M   0 % /Dev/SHM

In Oracle 11g for Linux, it seems that a new mechanism is used to manage shared memory segments rather than traditional sys/dev/shm. A more accurate description of the error can be found in Alert.ora:

Mon Feb -  A: -: +  -starting ORACLE instance (normal) warning:you is trying to  UseThe Memory_target feature. This feature requires the/Dev/ShmfileSystem toBe mounted forAt least419430400bytes./Dev/Shm isEither notMountedor  isMounted withAvailableSpaceLess than this size. Memory_target can Work  asExpected. CurrentAvailable is 413466624  andUsed is 0bytes. Ensure that Mount point is /Dev/Shm forThis directory.memory_target needs larger/Dev/Shm

One way to solve this is to increase the capacity of the Tmpfs file system:

Modify the TMPFS corresponding line in the/etc/fstab, change the original TMPFS/DEV/SHM tmpfs defaults 0 0 to Tmpfs/dev/shm tmpfs default,size=1024m 0 0 so that the TMPFS increases to 1G , and re-mount/dev/shm to make it effective.

[[email protected] ~]# VI/etc/Fstabtmpfs/Dev/SHM Tmpfs defaults,size=420m0 0[[email protected] ~]# mount-o remount/Dev/SHM[[email protected] ~]# DF-hfilesystem Size used Avail Use%Mounted onTmpfs 420M0420M0% /Dev/Shmsql>Startuporacle instance started. Total System Global Area418484224bytesfixed Size1336932bytesvariable Size406849948bytesDatabaseBuffers4194304Bytesredo Buffers6103040bytesDatabaseMounted.

Memory_target of Oracle Initialization parameters