Oracle9i的動態SGA,PGA特性探索

參考:[@more@]

“In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) is limited to:

for P_A_T <= 500MB the parameter _smm_max_size = 20% of P_A_T
for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
for P_A_T betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of P_A_T

I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4GB.

The maximum value for parallel operations changed from 30% to 50% PGA_AGGREGATE_TARGET/DOP. Also the DOP changed. When DOP <=5 then _smm_max_size is used, otherwise _smm_px_max_size/DOP limits the maximum memory usage. . .

轉載:

oracle中SGA的設定
關鍵詞： sga
關於SGA設定的一點總結
本總結不針對特例，僅對伺服器只存在OS + ORACLE 為例，如果存在其他應用請酌情考慮
寫這個也是因為近來這種重複性的問題發生的太多所導致的
首先不要迷信STS,SG,OCP,EXPERT 等給出的任何建議、記憶體百分比的說法
基本掌握的原則是， data buffer 通常可以儘可能的大，shared_pool_size 要適度，log_buffer 通常大到幾百K到1M就差不多了
設定之前，首先要明確2個問題
1： 除去OS和一些其他開銷，能給ORACLE使用的記憶體有多大
2：oracle是64bit or 32 bit,32bit 通常 SGA有 1.7G 的限制(某些OS的處理或者WINDOWS上有特定設定可以支援到2G以上甚至達到3.7G，本人無這方面經驗)
下面是我的windows2000下的oracle :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL>
windows上存在32bit的限制，如AIX、HP UNIX 等有明確的64BIT OS and ORACLE的版本，32bit oracle可以裝在64bit os 上，64 bit oracle不能裝在32 bit OS上
不管oracle是32 bit ORACLE還是 64 bit 的,假定應用存在沒有很好的使用bind var 的情況，也不能設定 shared_pool_size 過大，通常應該控制在200M--300M,如果是 ORACLE ERP 一類的使用了很多儲存過程函式、包 ，或者很大的系統，可以考慮增大shared_pool_size ,但是如果超過500M可能是危險的，達到1G可能會造成CPU的嚴重負擔，系統甚至癱瘓。所以shared_pool_size 如果超過300M還命中率不高，那麼應該從應用上找原因而不是一味的增加記憶體，shared_pool_size 過大主要增加了管理負擔和latch 的開銷。
log_buffer : 128K ---- 1M 之間通常問題不大，不應該太大
large_pool_size :如果不設定MTS，通常在 RMAN 、OPQ 會使用到，但是在10M --- 50M 應該差不多了。假如設定 MTS,則由於 UGA 放到large_pool_size 的緣故，這個時候依據 session最大數量和 sort_ares_size 等引數設定，必須增大large_pool_size 的設定，可以考慮為 session * (sort_area_size + 2M)。這裡要提醒一點，不是必須使用MTS，我們都不主張使用MTS，尤其同時線上使用者數小於500的情況下。
java_pool_size : 若不使用java，給30M通常就夠了
data buffer ,在做了前面的設定後，凡可以提供給oracle的記憶體，都應該給data buffer = (db_block_size * db_block_buffers)
在9i 中可以是 db_cache_size
還有2個重要引數我們需要注意
sort_area_size and hash_area_size
這兩個引數在非MTS下都是屬於PGA ，不屬於SGA,是為每個session單獨分配的，在我們的伺服器上除了OS + SGA,一定要考慮這兩部分
(****) : OS 使用記憶體+ SGA + session*(sort_area_size + hash_area_size + 2M) < 總物理RAM 為好

這樣歸結過來，假定oracle是 32 bit ,伺服器RAM大於2G ，注意你的PGA的情況，,則建議
shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具體化，注意滿足上面(****) 的原則的基礎上可以參考如下設定
如果512M RAM
建議 shared_pool_size = 50M, data buffer = 200M
如果1G RAM
shared_pool_size = 100M , data buffer = 500M
如果2G
shared_pool_size = 150M ,data buffer = 1.2G
實體記憶體再大已經跟引數沒有關係了

假定64 bit ORACLE
記憶體4G
shared_pool_size = 200M , data buffer = 2.5G
記憶體8G
shared_pool_size = 300M , data buffer = 5G
記憶體 12G
shared_pool_size = 300M-----800M , data buffer = 8G

以上僅為參考值，不同系統可能差異比較大，需要根據具體情況調整。建議在設定引數的同時，init中使用 lock_sga ，在不同的平臺上可能有不同的方式，使得SGA鎖定在實體記憶體中而不被放入 SWAP 中，這樣對效率有好處關於記憶體的設定，要再進行細緻的調整，起的作用不大，但可根據statspack資訊和v$system_event,v$sysstat,v$sesstat,v$latch 等view資訊來考慮微調


pga設定

hash_area_size用於在記憶體中進行排序的區域
hash_value不會影響PGA，他是系統自動計算HASH運算，以定位記憶體地址。
增大hash_area_size,可以減少物理磁碟的排序，所以提高排序的速度。
9I管理PGA有兩種方式，同時影響到hash_area_size
自動配置PGA
手動配置PGA
如果設定了auto，那麼sort area, hash area自動分配大小，pga_aggregate_target引數被使用。
如果設定為 manual，那麼引數sort area, hash area引數被使用，對於某些特別耗資源的可以指定manual，然後設定合理的sort area,hash area。

設定10104事件來判斷hash_area_size是否要增大，如果Number of rows left to be
iterated over的值為非0，表示讀了臨時表，要兩階段不能在記憶體中一次完成

在Oracle9i之前，PGA的計算和控制都是比較複雜的事情，從Oracle9i開始，Oracle提供了一種SQL記憶體管理的新方法：自動化SQL執行記憶體管理（Automated SQL Execution Memory Management），使用這個新特性，Oracle可以自動調整S Q L記憶體區，而不用關閉資料庫，這一改進大大簡化了DBA的工作，同時也提高了Oracle資料庫的效能。
為實現自動的PGA管理，Oracle引入了幾個新的初始化引數：
1．PGA_AGGREGATE_TARGET-此引數用來指定所有session總計可以使用最大PGA記憶體。這個引數可以被動態的更改,取值範圍從10M -- （4096G-1 ）bytes。
2。WORKAREA_SIZE_POLICY-此引數用於開關PGA記憶體自動管理功能，該引數有兩個選項：AUTO 和 MANUAL，當設定為AUTO時，資料庫使用Oracle9i提供的自動PGA管理功能，當設定為MANUAL時，則仍然使用Oracle9i前手工管理的方式。
預設的，Oracle9i中WORKAREA_SIZE_POLICY被設定為AUTO。
需要注意的是，在Oracle9i中，PGA_AGGREGATE_TARGET引數僅對專用伺服器模式下（Dedicated Server）的專屬連線有效，但是對共享伺服器（Shared Server）連線無效；從Oracle10g開始PGA_AGGREGATE_TARGET對專用伺服器連線和共享伺服器連線同時生效。
PGA_AGGREGATE_TARGET 引數同時限制全域性PGA分配和私有工作區記憶體分配：
1．對於序列操作，單個SQL操作能夠使用的PGA記憶體按照以下原則分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
2．對於並行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 並行度）
要理解PGA的自動調整，還需要區分可調整記憶體（TUNABLE MEMORY SIZE）與不可調整記憶體（UNTUNABLE MEMORY SIZE）。可調整記憶體是由SQL工作區使用的，其餘部分是不可調整記憶體。
啟用了自動PGA調整之後， Oracle仍然需要遵循以下原則：
UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET
資料庫系統只能控制可調整部分的記憶體分配，如果可調整的部分過小，則Oracle永遠也不會強制啟用這個等式。
另外，PGA_AGGREGATE_TARGET引數在CBO最佳化器模式下，對於SQL的執行計劃會產生影響。Oracle在評估執行計劃時會根據PGA_AGGREGATE_TARGET引數評估在Sort，HASH-JOIN或Bitmap操作時能夠使用的最大或最小記憶體，從而選擇最優的執行計劃。
對於PGA_AGGREGATE_TARGET引數的設定，Oracle提供這樣一個建議方案
1．對於OLTP系統
PGA_AGGREGATE_TARGET = ( * 80%) * 20%
2．對於DSS系統
PGA_AGGREGATE_TARGET = ( * 80%) * 50%
也就是說，對於一個單純的資料庫伺服器，通常我們需要保留20%的實體記憶體給作業系統使用，剩餘80%可以分配給Oracle使用。Oracle使用的記憶體分為兩部分SGA和PGA，那麼PGA可以佔用Oracle消耗總記憶體的20%（OLTP系統）至50%（DSS系統）。
這只是一個建議設定，更進一步的我們應該根據資料庫的具體效能指標來調整和最佳化PGA的使用。

對於PGA_AGGREGATE_TARGET引數的設定，Oracle提供這樣一個建議方案
1．對於OLTP系統
PGA_AGGREGATE_TARGET = ( * 80%) * 20%
2．對於DSS系統
PGA_AGGREGATE_TARGET = ( * 80%) * 50%
也就是說，對於一個單純的資料庫伺服器，通常我們需要保留20%的實體記憶體給作業系統使用，剩餘80%可以分配給Oracle使用。Oracle使用的記憶體分為兩部分SGA和PGA，那麼PGA可以佔用Oracle消耗總記憶體的20%（OLTP系統）至50%（DSS系統）。

Oracle9i資料庫在內部特性方面有著非常大的增強，其中一個最令Oracle DBA興奮的莫過於可以動態設定全部的Oracle SGA控制引數。與8i不同的是，原來都將初始化引數放到一個文字檔案中，並且在資料庫啟動的時候讀取，Oracle9i卻可以透過ALTER DATABASE和ALTER SYSTEM命令復位全部的Oracle引數。

　　在9i前，如果想對Oracle資料庫的處理模式作一些改變的話，Oracle管理員必須關閉資料庫並且重新設定INIT.ORA檔案中的引數，然後重新啟動資料庫。對於白天使用OLTP模式運作，晚上切換到資料倉儲模式的Oracle資料庫來說，這種重新設定是經常做的。

　　對於需要停止和重新啟動Oracle資料庫來修改引數來說，Oracle9i在這方面有明顯的加強，它令實現資料庫連續可用的目標變得更加簡單。

　　這種可以在Oracle SGA中動態增加和縮小不同區域的能力為Oracle資料庫管理員提供了一些令人激動的新特性。SGA每個區域的資料庫活動都可以獨立地被監視，而且也可以在Oracle資料庫中，根據使用的模式分配和取回資源。

　　我們首先來看以下Oracle9i資料庫和Oracle8i資料庫的一些區別。Oracle9i的一個最重要的加強是對於連線到Oracle資料庫的全部專用連線，都無需要擁有一個獨立的PGA空間。在Oracle8i中，對於專用的Oracle連線，我們都需要在記憶體中分配一個獨立的區域，稱為Program Global Area或者PGA。PGA空間中包含有SORT_AREA_SIZE和額外的RAM控制結構以用來維護連線任務的狀態。在Oracle9i中，PGA空間已經被Oracle SGA中的一個新記憶體空間代替，它是透過PGA_AGGREGATE_TARGET引數來設定的

　　由於全部的記憶體使用都在Oracle SGA中分配，所以Oracle資料庫管理員可以將分配給Oracle伺服器的記憶體加大，可以分配至直到Orace伺服器全部記憶體的80%。Oracle建議將伺服器其餘的20%記憶體保留給作業系統的任務。

　　當使用者連線到Oracle9i資料庫時，排序工作所需要的記憶體將會在Oracle9i的PGA_AGGREGATE_TARGET區域中分配。這可以令Oracle9i比Oracle8i跑得更快，這是由於記憶體只在需要的期間才分配，並且在完成後就可以馬上釋放給其它連線的Oracle任務使用。

　　動態修改SGA區域

　　由於Oracle管理員現在可以增加和減少SGA的全部區域，因此我們可以快速地檢視一下SGA區域是怎樣的，這樣我們就可以知道Oracle DBA如何監視這些區域的使用並且為Oracle資料庫更有效地重新分配記憶體。SGA的區域可以分為以下的部分。

　　資料緩衝（Data buffers）--Oracle9i擁有多達7個獨立的資料緩衝來儲存磁碟送來的資料塊。這些包含有傳統的KEEP pool，RECYCLE pool和DEFAULT pool，還有為每個Oracle資料庫支援的塊大小(2K, 4K, 8K, 16K和32K) 而建立的獨立資料緩衝池

　　我們可以監視這7個資料緩衝區域的命中率，如果緩衝的命中率保持在百分之九十以上，我們可以減少分配給這些資料緩衝的記憶體，並且將它們重新分配給其它Oracle例項中需要額外記憶體的地方。

　　當資料緩衝的命中率（DBHR）下降時，我們可以將記憶體由一個資料緩衝中分離出來，並且將它重新分配給其它的資料緩衝

　　共享池（Shared pool）--Oracle9i的共享池有一個很重要的作用是分析和執行Oracle SQL語句。低的library cache命中率表示分配給library cache的記憶體不足，當shared pool需要對SQL語句進行大量的分析和執行時，Oracle9i的資料庫管理員可以使用ALTER SYSTEM來為shared pool加入額外的記憶體。

　　PGA區域--分配給PGA_AGGREGATE_TARGET的記憶體是用來讓Oracle連線維護與連線相關的資訊（例如遊標的狀態），並且對SQL的結果集進行排序。

　　Log buffer--對於Oracle redo log緩衝是否有大量活動，我們可以在log switch（日誌轉換）的頻率上看出來。Oracle管理員可以監視redo log區域的活動，並且在Oracle資料庫需要額外的記憶體為原始的緩衝區域服務時，動態地增加記憶體。

　　現在就讓我們來仔細看以下這些記憶體區域之間是如何作用的。

改變PGA的記憶體分配
　　當以下的其中一個條件是真時，我們將需要動態地修改PGA_AGGREGATE_TARGET引數。

　　。當V$SYSSTAT中對"estimated PGA memory for one-pass" 的統計值超出PGA_AGGREGATE_TARGET時，我們就需要增加PGA_AGGREGATE_TARGET的值。

　　。當V$SYSSTAT中對"workarea executions - multipass" 的統計值超過百分之一時，資料庫將會由更多的記憶體中得到好處

　　。你可能過高地估計了PGA記憶體的空間，當V$SYSSTAT中"workarea executions - optimal"的值一直是100%時，可以考慮減少PGA_AGGREGATE_TARGET的值。

　　我們可以透過一個簡單的指令碼來檢視shared pool是否需要更多的記憶體。

　　量度Library Cache的丟失率

set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = new.snap_id-1
and
old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
Cache Misses
Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10 10,338 3 .00029
2001-12-12 10 182,477 134 .00073
2001-12-14 10 190,707 202 .00106
2001-12-16 10 2,803 11 .00392
　　由上面的例子看到，在每天的9：00AM到10：AM之間，shared pool明顯缺少記憶體。我們就可以在這段期間動態地重新設定shared_pool引數以由db_cache_size中分配額外的記憶體。

　　SGA的閥值摘要

　　由下面的表一可以看到，在監視SGA的記憶體使用時，有幾個明顯的閥值可以利用。我們可以寫一些指令碼並在其中整合一些智慧，這樣就可以在處理的需求變化時重新設定SGA。

RAM Area Too-small Condition Too-Large Condition
Shared pool Library cache misses No misses
Data buffer cache Hit ratio < 90% Hit ratio > 95%
PGA aggregate high multi-pass executions 100% optimal executions
　　表1：SGA中的異常條件指示

　　檢視SGA記憶體區域的負載

　　Oracle9i使用了一些新的內部檢視或者在現有的檢視中加入新的列來幫助檢視Oracle9i中的內部記憶體分配情況。以下的新V$檢視可以幫助監視Oracle9i連線的記憶體使用。

　　V$PROCESS -在Oracle9i中加入了三個新的列以監視PGA記憶體的使用，新列的名字是pga_used_mem, pga_alloc_mem 和pga_max_mem。

　　V$SYSSTAT -加入了很多新的統計行，包括有area statistics for optimal, one-pass和multi-pass。

　　V$PGASTAT -該新檢視展示了全部後臺程式和專用連線的PGA記憶體使用

　　V$SQL_PLAN--這個新檢視包含了全部當前執行的SQL的執行計劃資訊。對於需要最最佳化的SQL語句的效能調整專家來說這是非常吸引人的。

　　V$WORKAREA -這個新檢視提供了Oracle9i連線的累積記憶體統計的詳細資訊。

　　V$WORKAREA_ACTIVE - 這個新的檢視提供了當前全部正在執行的SQL語句的內部記憶體使用資訊。

　　它們的目的是透過這些V$檢視來監視SGA中的記憶體使用，然後根據Oracle例項的處理要求，透過ALTER SYSTEM命令重新分配記憶體。我們以下來看一些這些新的Oracle9i特性和指令碼，它可以幫助我們檢視詳細的記憶體使用。

　　當然我們不可能在這裡詳述所有的技術，以下就讓我們看一個簡單的例子，它透過使用V$SYSSTAT檢視來決定何時重新設定PGA_AGGREGATE_TARGET引數。

　　以下的查詢可以得到自資料庫例項啟動後work areas被執行的全部數目和百分比。

work_area.sql
select
name profile,
cnt,
decode(total, 0, 0, round(cnt*100/total)) percentage
from
(
select
name,
value cnt,
(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);
　　這個查詢的輸出可能如下：

PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0
　　這個查詢的輸出是用來告訴DBA何時動態調整PGA_AGGREGATE_TARGET引數。在通常的情況下，如果multi-pass的執行大於0，就需要增加PGA_AGGREGATE_TARGET的值，並且在optimal executions是100%時減少它的值。

　　我們還可以使用V$PGASTAT檢視來決定我們的Oracle例項的記憶體使用。V$PGASTAT檢視提供了PGA使用和自動記憶體管理的例項級摘要統計資訊。以下的指令碼提供了全部Oracle9i連線的整體記憶體使用的統計資訊。

　　以下是一個用來檢測Oracle9i中PGA記憶體使用的簡單指令碼。

check_pga.sql
column name format a30
column value format 999,999,999
select
name,
value
from
v$pgastat
;
The output of this query might look like the following:
NAME VALUE
------------------------------------------------------ ----------
aggregate PGA auto target 736,052,224
global memory bound 21,200
total expected memory 141,144
total PGA inuse 22,234,736
total PGA allocated 55,327,872
maximum PGA allocated 23,970,624
total PGA used for auto workareas 262,144
maximum PGA used for auto workareas 7,333,032
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0
estimated PGA memory for optimal 141,395
maximum PGA memory for optimal 500,123,520
estimated PGA memory for one-pass 534,144
maximum PGA memory for one-pass
　　在上面的v$pgastat顯示中我們可以看到以下的統計。

　　Aggregate PGA auto target -該列給出了可用於Oracle9i連線的全部記憶體。我們已經提過，這個值是由PGA_AGGREGATE_TARGET設定的。

　　Global memory bound -該統計表示work area的最大值，Oracle建議在該統計值下降到1M時，你應該增加PGA_AGGREGATE_TARGET的值。

　　Total PGA allocated - 這個統計顯示了資料庫中全部PGA記憶體使用的高水位線。當使用增加時，你應該看到這個值接近PGA_AGGREGATE_TARGET的值。

　　Total PGA used for auto workareas - 這個統計監視記憶體的使用或者全部執行在自動記憶體模式中的全部連線。要記住的是，並不是全部的內部程式使用自動記憶體特性。例如，Java和PL/SQL 將分配記憶體，但是這部分將不會統計到這個值中。因此我們可使用整體PGA的值來減去該值，以得到連線和Java and PL/SQL使用的記憶體。

　　Estimated PGA memory for optimal/one-pass - 該統計估計optimal模式下執行全部的連線任務所需要的記憶體。要記住的是，如果Oracle9i遇到記憶體不足時，它就會呼叫multi-pass操作。這個統計對於監視Oracle9i中的記憶體使用是非常重要的，大多數的Oracle DBA將會增加PGA_AGGREGATE_TARGET到這個值。

　　現在我們已經瞭解了這個概念，以下就讓我們來看一下自動重新配置SGA有哪些方法。

　　總述

　　在一個UNIX環境中，在處理需求改變時透過定時任務來修改記憶體配置是非常簡單的。例如，許多Oracle資料庫在一般的工作時間以OLTP模式運作，在晚上的時候則執行對記憶體需求很大的批次報告。

　　我們知道在一個OLTP資料庫中應該將DB_CACHE_SIZE設定為一個較大的值，而在需求記憶體很大的批次任務中則需要給PGA_AGGREGATE_TARGET分配額外的記憶體。

　　以下的UNIX指令碼可以用來重新設定OLTP和DSS的SGA值而無需將例項停下來。在這個例子中，我們假定有一個孤立的帶有8GB記憶體的的Oracle伺服器。我們還假定保留20%的記憶體供UNIX使用，而剩下的6GB記憶體則用作Oracle和Oracle連線。這些指令碼是在HP/UX或者Solaris中使用的，並且接受$ORACLE_SID作為一個引數。

　　DSS_CONFIG.KSH指令碼將在每晚的6：00 p.m執行，以重新設定Oracle在晚上執行對記憶體需求很大的批次任務。

dss_config.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus -s /nologin<
connect system/manager as sysdba;
ALTER SYSTEM set db_cache_size=1500m;
ALTER SYSTEM set shared_pool_size=500m;
ALTER SYSTEM set pga_aggregate_target=400m;
exit
!
　　現在我們已經知道了一個常見的方式來修改Oracle的配置，我們也很容易地看到可以很簡單地開發一個技術來連續地監控Oracle的處理需求，並且根據現有的資料庫需求來使用ALTER SYSTEM作修改。

　　結論

　　雖然Oracle9i中的記憶體管理仍然需要很多的手工操作，不過大部分的Oracle管理員可以使用工具來連續地監控Oracle SGA中的記憶體使用，並且可以根據Oracle instance中現在的使用情況來自動地重新分配記憶體。這樣就可以令Oracle 管理員根據系統的變化來靈活地重新設定他們的系統。