一：Oracle 11g自動記憶體管理概述

oracle11g中的新的記憶體管理特性自動化記憶體管理(automatic memory management)。在該新特性之下SGA與PGA將會根據需要自動的擴充套件與收縮。你只需要設定兩個記憶體管理相關的引數memory_target與memory_max_target.

1）memory_target:該引數設定整個oracle資料庫例項可以使用的記憶體量，oralce資料庫例項在執行過程中會根據需求自動的調節SGA與PGA的大小。可以使用相應的alter system命令動態的修改memory_target的值。該引數是動態初始化引數。

2)memory_max_target:該引數設定oracle例項可以使用的最大記憶體量。在調整memory_target的值時必須保持以下的約束關係memory_target<=memory_max_target.該引數是靜態初始化引數,修改需要重啟才能生效。

在建立資料庫的時候，你可以透過設定memory_target，memory_max_target初始化引數來開啟自動化記憶體管理特性。在資料庫建立以後也可以透過設定memory_target，memory_max_target初始化引數來開啟自動化記憶體管理，但是不會立即生效，需要重啟例項。

使用動態記憶體管理時，SGA_TARGET和PGA_AGGREGATE_TARGET代表它們各自記憶體區域的最小設定，要讓Oracle完全控制記憶體管理，這兩個引數應該設定為0。

關於設定memory_target的值的原則：如果伺服器只有資料庫，可以分配全部記憶體的2/3 到 3/4 給oracle的memory_target ；

檢視memory_target 以及SGA_TARGET和PGA_AGGREGATE_TARGET的設定的值，需要注意設定的值不會變化的，如下：

SQL> show parameter target

NAME TYPE VALUE

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

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 62848M

memory_target big integer 62848M

parallel_servers_target integer 512

pga_aggregate_target big integer 4G

sga_target big integer 8G

二：如何Oracle執行的某個時刻的各個記憶體的分配大小呢？

可以透過create pfile from spfile,檢視：

[oracle@JoinSpider dbs]$ cat initora11g.ora

ora11g.__db_cache_size=2281701376

ora11g.__java_pool_size=939524096

ora11g.__large_pool_size=805306368

ora11g.__oracle_base='/files/oracle'#ORACLE_BASE set from environment

ora11g.__pga_aggregate_target=57310969856 ##這是當前實際的分配pga的值，

ora11g.__sga_target=5637144576 ##這是當前實際的分配sga的值

ora11g.__shared_io_pool_size=0

ora11g.__shared_pool_size=3758096384

ora11g.__streams_pool_size=0

*.audit_file_dest='/files/oracle/admin/ora11g/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/files/oracle/oradata/ora11g/control01.ctl','/files/oracle/fast_recovery_area/ora11g/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ora11g'

*.db_recovery_file_dest='/files/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/files/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'

*.memory_max_target=65899345920

*.memory_target=65899345920

*.open_cursors=300

*.pga_aggregate_target=4294967296 ###這是最小值

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=8589934592 ###這是最小值

*.undo_retention=3600

*.undo_tablespace='UNDOTBS1'

三：Oracle11g記憶體管理如何開啟：

1：在資料庫建立期間設定自動記憶體管理：如果是手工建庫，那麼你只需要設定好如下類似初始化引數即可：

memory_target = 1000m

memory_max_target = 1500m

如果是採用DBCA建立資料庫，如果是建立新庫並且是高階安裝，你能選擇自動記憶體管理選項。如果是選擇基本安裝，那麼預設就是自動記憶體管理。

2：在資料庫建立後：你可以在資料庫建立後新增memory_max_target和memory_target.雖然memory_target是一個動態引數，但是當資料庫執行時，你不能交換到自動記憶體管理。必須重啟資料庫將引數應用到例項啟動過程才行。

四：Oracle11g記憶體管理的相關引數設定的含義和原則：

MEMORY_TARGET 設定為非零值時：

1:如果設定了SGA_TARGET 和 PGA_AGGREGATE_TARGET，則會分別將它們當作 SGA大小和 PGA 大小的最小值。

2:如果設定了 SGA_TARGET 但未設定PGA_AGGREGATE_TARGET，則仍會自動最佳化這兩個引數。PGA_AGGREGATE_TARGET 將初始化為以下值：(MEMORY_TARGET -SGA_TARGET)。

3: 如果設定了 PGA_AGGREGATE_TARGET 但未設定 SGA_TARGET，則仍會自動最佳化這兩個引數。SGA_TARGET 將初始化為值 min(MEMORY_TARGET -PGA_AGGREGATE_TARGET, SGA_MAX_SIZE（如果使用者已設定）)，系統將自動最佳化子元件。

4:如果未設定任何引數，則無需最小值或預設值即可自動最佳化這兩個引數。有這樣一個策略：在初始化過程中，將MEMORY_TARGET的按固定比率分配給SGA 和 PGA。具體如下：

sga_target =memory_target *60%

pga_aggregate_target=memory_target *40%

如果未設定 MEMORY_TARGET，或者將其顯式設定為 0（11g 中的預設值為 0）則取消了memory_target的作用，完全和10g在記憶體管理上一致。：

1.如果設定了 SGA_TARGET，則系統僅自動最佳化 SGA 的子元件大小。PGA 的自動最佳化與是否顯式設定 PGA 無關。但是，不會自動最佳化整個SGA (SGA_TARGET) 和 PGA (PGA_AGGREGATE_TARGET)，即 SGA 和PGA 不會自動增長或收縮。

2.如果既未設定SGA_TARGET，又未設定 PGA_AGGREGATE_TARGET，則SGA中的各元件大小都要明確設定，不能自動調整各元件大小。PGA不能自動增長和收縮。

3. 如果僅設定了 MEMORY_MAX_TARGET，則使用文字初始化檔案進行手動設定時，MEMORY_TARGET 預設為 0。SGA 和 PGA 的自動最佳化行為預設情況下與 10g R2 中的相同。

4.如果 SGA_MAX_SIZE 不是使用者設定的，則在使用者設定了 MEMORY_MAX_TARGET 的情況下，系統會在內部將其設定為 MEMORY_MAX_TARGET（與使用者是否設定SGA_TARGET 無關）

五：若將一個非自動化記憶體管理的11g庫系統改成自動化記憶體管理，該如何確認MEMORY_TARGET的值？

1.在一個系統上啟用自動記憶體管理其實很簡單，不需要事先做太多的事情，可以使用下面的計算公式來計算MEMORY_TARGET： MEMORY_TARGET=SGA_TARGET+GREATEST(PGA_AGGREGATE_TARGET,"maximum PGA allocated ");

下面的查詢語句向你展示有關的資訊，以及如何計算出需要的memory_target值：

1.方法一：使用sql查出SGA_TARGET、PGA_AGGREGATE_TARGET以及maximum PGA allocated的值，之後根據公式MEMORY_TARGET=SGA_TARGET+GREATEST(PGA_AGGREGATE_TARGET,"maximum PGA allocated ");算出結果，，注意函式GREATEST是求兩個數值中的最大值：

SELECT name, value/1024/1024

FROM v$parameter

WHERE name IN ('pga_aggregate_target', 'sga_target')

UNION

SELECT 'maximum PGA allocated' AS name, TO_CHAR(value)/1024/1024 AS value

FROM v$pgastat

WHERE name = 'maximum PGA allocated';

方法二：直接一個sql查出memory_target的值，單位為mb;

SELECT ( sga.value + GREATEST(pga.value, max_pga.value))/1024/1024 AS memory_target

FROM

(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,

(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,

(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;

我認為MEMORY_TARGET應該等於實際的sga的值加上PGA 最大分配的值，這是最真實的，但是一般就認為MEMORY_TARGET=SGA_TARGET+GREATEST(PGA_AGGREGATE_TARGET,"maximum PGA allocated ")即可

如果想更真實的確定MEMORY_TARGET的大小，那就要確定例項的sga_target的實際使用的大小，當前設定值減去剩餘值。然後再利用前邊的計算公式

SQL> select (

(select sum(value) from v$sga) -

(select current_size from v$sga_dynamic_free_memory)

)/1024/1024/1024 "sga_target"

from dual;

2.如果此時例項沒有設定memory_target或者說memory_target=0，並且把SGA_TARGET設定為0，即表示禁用10g的新特性——自動共享記憶體（sga)分配，這樣我們需要對各個sga記憶體元件的值進行單獨設定,那麼這種情況下該怎麼確定memory_target的值來開啟Oracle11g的自動記憶體管理呢？

SQL> select value/1024/1024 as value from v$pgastat where name='maximum PGA allocated';

VALUE

----------

45.7822266

可見pga_aggregate_target=46M，確定oracle例項啟動以來PGA的最大容量，可以理解為最大的使用量。

SQL> SELECT ((SELECT SUM(value) FROM V$SGA) -

(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) "SGA_TARGET"

FROM DUAL;

SGA_TARGET

----------

313626624

可見SGA_target的大小為300M，

1）如果馬上就修改memory_target=352M會報

值的範圍不合法的提示，

SQL> alter system set memory_target=352M;

alter system set memory_target=352M

ERROR at line 1:

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

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least

400M

因為要滿足memory_target>=pga_aggregate_target+sga_target.

但是pga_aggregate_target 的值是100M，而SGA_target的值為300M。

352M<300M+100M所有出錯了。

2）修改PGA或者sga的值為0M也會報錯的。因為還沒有設定memory_target的值，

SQL> alter system set pga_aggregate_target=0;

alter system set pga_aggregate_target=0

ERROR at line 1:

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

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

如果直接修改pga_aggregate_target的值會報上面的錯誤。因為我們還沒有設定momory_target的值，如果直接是設定PGA的大小為0M的話，那就等於不給PGA分配記憶體了，所有我們透過上面的一個查詢，確定自例項啟動以來分配給PGA的最大記憶體大小46M，先把PGA的值調下來。使

memory_target>=pga_aggregate_target+sga_target.即352M>=50M+300M

SQL> alter system set pga_aggregate_target=50M;

System altered.

SQL> alter system set memory_target=352M;

System altered.

SQL> alter system set pga_aggregate_target=0M;

System altered.

這是我們已經啟用oracle的記憶體自動化管理，並且設定SGA_target與pga_aggregate_target引數的值為0。oracle這時就不會為SGA與PGA分配最小記憶體大小了。oracle會根據工作負載自動的調整SGA與PGA的大小。

六：Oracle 11g設定記憶體相關引數原則：

1.如果Oracle11g要開啟自動記憶體管理，需要設定MEMORY_TARGET的值，如果該伺服器只跑Oracle，那麼可以按著下面的原則設定：

MEMORY_TARGET=實體記憶體 x 80%

MEMORY_MAX_SIZE=實體記憶體 x 80%

2.如果不打算開啟自動記憶體管理，也就是MEMORY_TARGET=0：

對於OLTP系統：

SGA_TARGET=(實體記憶體 x 80%) x 80%

SGA_MAX_SIZE=(實體記憶體 x 80%) x 80%

PGA_AGGREGATE_TARGET=(實體記憶體 x 80%) x 20%

對於DSS系統：

SGA_TARGET=(實體記憶體 x 80%) x 50%

SGA_MAX_SIZE=(實體記憶體 x 80%) x 50%

PGA_AGGREGATE_TARGET=(實體記憶體 x 80%) x 50%

小結：

然後至於調優的事情，是需要根據你實際生產系統的，具體和業務和環境有關係，沒有明確的建議值。sga和pga大小可以根據實際執行一段時間的系統的awr報告和記憶體推薦建議來調整，如果11g你開啟了自動化記憶體管理，你會發現，當白天大量的併發事務，會導致sga的值增加，而晚上如果進行統計，物化檢視重新整理等等需要大量order by，group by 等等，就會導致pga增加；

oracle 11g自動記憶體管理

相關文章