一、引言：

??? Oracle 9i引入pga_aggregate_target，可以自動(dòng)對(duì)PGA進(jìn)行調(diào)整；

??? Oracle 10g引入sga_target，可以自動(dòng)對(duì)SGA進(jìn)行調(diào)整；

??? Oracle 11g則對(duì)這兩部分進(jìn)行綜合，引入memory_target，可以自動(dòng)調(diào)整所有的內(nèi)存，這就是新引入的自動(dòng)內(nèi)存管理特性。

二、本文說明：

????? 操作系統(tǒng)：rhel 5.4 x32

????? 數(shù)據(jù)庫：oracle 11g r2

三、memory_target的介紹：

??? 3.1、下面通過示例了解一下memory_target的設(shè)置與PGA和SGA關(guān)系：

SQL> show parameter memory_target;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 316M 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=200m 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.

??? 設(shè)置memory_target參數(shù)后，實(shí)際上Oracle會(huì)自動(dòng)設(shè)置并調(diào)整一下兩個(gè)參數(shù)來分配SGA和PGA的內(nèi)存，這和Oracle 10g自動(dòng)設(shè)置sga_target后分配db_cache_size和shared_pool_size的機(jī)制是一樣的。

SQL> col ksppinm for a20; SQL> col ksppstvl for a20; SQL> select a.ksppinm name,b.ksppstvl value2 from x$ksppi a,x$ksppcv b3 where a.indx = b.indx4 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、討論一下11g中memory_target設(shè)置和不設(shè)置對(duì)SGA/PGA的影響：

????3.2.1、如果memory_target設(shè)置為非0值

??????(下面有四種情況來對(duì)SGA和PGA的大小進(jìn)行分配)

??????3.2.1.1、sga_target和pga_aggregate_target已經(jīng)設(shè)置大小

??????? 如果Oracle中已經(jīng)設(shè)置了參數(shù)sga_target和pga_aggregate_target，則這兩個(gè)參數(shù)將各自被分配為最小值為他們的目標(biāo)值。

????????memory_target = sga_target + pga_aggregate_target，大小和memory_max_size一致。

??????3.2.1.2、sga_target設(shè)置大小，pga_aggregate_target沒有設(shè)置大小

????????那么pga_aggregate_target初始化值=memory_target-sga_target

??????3.2.1.3、sga_target沒有設(shè)置大小，pga_aggregate_target設(shè)置大小

????????那么sga_target初始化值=memory_target-pga_aggregate_target

??????3.2.1.4、sga_target和pga_aggregate_target都沒有設(shè)置大小

??????? Oracle 11g中對(duì)這種sga_target和pag_aggregate_target都沒有設(shè)定大小的情況下，Oracle將對(duì)這兩個(gè)值沒有最小值和默認(rèn)值。Oracle將根據(jù)數(shù)據(jù)庫運(yùn)行狀況進(jìn)行分配大小。但在數(shù)據(jù)庫啟動(dòng)是會(huì)有一個(gè)固定比例來分配：

????????sga_target = memory_target*60%

????????pga_aggregate_target = memory_target*40%

??? 3.2.2、如果memory_target沒有設(shè)置或 = 0(在11g中默認(rèn)為0)

????? 11g中默認(rèn)為0則初始狀態(tài)下取消了memory_target的作用，完全和10g在內(nèi)存管理上一致，完全向下兼容。(也有三種情況來對(duì)SGA和PGA的大小進(jìn)行分配)

??????3.2.2.1、sga_target設(shè)置值，則自動(dòng)調(diào)節(jié)SGA中的shared pool,buffer cache,redo log buffer,java pool,larger pool等內(nèi)存空間的大小。PGA則依賴pga_aggregate_target的大小。sga和pga不能自動(dòng)增長和自動(dòng)縮小。

????? 3.2.2.2、sga_target和pga_aggregate_target都沒有設(shè)置

??????? SGA中的各組件大小都要明確設(shè)定，不能自動(dòng)調(diào)整各組件大小。PGA不能自動(dòng)增長和收縮。

??????3.2.2.3、memory_max_target設(shè)置而memory_target =?0這種情況先和10g一樣，不做說明。?

三、ORA-00845

??? 最后談一下ORA-00845的由來和解決方案：

??? 如果memory_max_target/memory_target設(shè)置過大，可能導(dǎo)致instance無法啟動(dòng)，報(bào)ORA-00845錯(cuò)誤。

[oracle@yft bin]$ oerr ora 00845 00845, 00000, "MEMORY_TARGET not supported on this system" // *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized // *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at leacle instance running on the system.SQL> alter system set memory_max_target=400m scope=spfile;System altered.SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-00845: MEMORY_TARGET not supported on this system

?這個(gè)錯(cuò)誤有點(diǎn)誤導(dǎo)，實(shí)際上這并不是說該平臺(tái)版本上不支持AMM特性，只是設(shè)置的memory_max_target超過了系統(tǒng)中設(shè)置的share memory(/dev/shm)而已。

[oracle@yft bin]$ df -h Filesystem Size Used Avail Use% Mounted on tmpfs 395M 0 395M 0% /dev/shm

在Oracle 11g for linux中似乎是用了一種新的機(jī)制來管理共享內(nèi)存段，而不是傳統(tǒng)的sys /dev/shm了。在alert.ora中可以找到更準(zhǔn)確的錯(cuò)誤描述：

Mon Feb 25 12:13:21 2013 Starting ORACLE instance (normal) WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 419430400 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 413466624 and used is 0 bytes. Ensure that the mount point is /dev/shm for this directory. memory_target needs larger /dev/shm

?

?解決的辦法之一是增加tmpfs文件系統(tǒng)的容量:

????? 修改/etc/fstab中tmpfs對(duì)應(yīng)的行；將原來的tmpfs?? /dev/shm? tmpfs defaults 0 0 改成tmpfs /dev/shm tmpfs default,size=1024M 0 0,這樣tmpfs增大為1G，重新mount /dev/shm使之生效。

[root@yft ~]# vi /etc/fstab tmpfs /dev/shm tmpfs defaults,size=420m 0 0[root@yft ~]# mount -o remount /dev/shm [root@yft ~]# df -h Filesystem Size Used Avail Use% Mounted on tmpfs 420M 0 420M 0% /dev/shmSQL> startup ORACLE instance started.Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 406849948 bytes Database Buffers 4194304 bytes Redo Buffers 6103040 bytes Database mounted.

?

總結(jié)

以上是生活随笔為你收集整理的Oracle初始化参数之memory_target的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。