對SGA和PGA的優化建議

1. SGA
    1.1 Buffer Cache
    1.2 Shared Pool

2. PGA

---

基準SGA和PGA的設定

對OLTP系統：

SGA = 實體記憶體 * 80% * 80%
PGA = 實體記憶體 * 80% * 20%

對OLAP系統：

SGA = 實體記憶體 * 80% * 50%
PGA = 實體記憶體 * 80% * 50%

---

1. 1 Buffer Cache

Buffer Cache如果想手工設定，建議設定為SGA的80%

與Buffer Cache可能有關的等待事件有：

1) Latch:cache buffer chains
2) Latch:cache buffer LRU chains
3) Buffer busy waits
4) Read waits
5) Free buffer waits
對1)和2)，可能是由於存在熱塊資料。
查詢熱塊：



對於熱塊爭用，最好能從應用層面解決。資料庫方面，可以考慮反向鍵索引和雜湊分割槽。

---

對於3) Buffer busy waits

表示多個程式訪問同一個Buffer Cache。以下是詳細解釋：
當一個session想要訪問/修改buffer cache的block,首先需要檢查該block是否存在於buffer cache，檢查該block是否存在於buffer cache中是通過HASH演算法來實現的，另外檢查相同的SQL語句是否存在於library cache中也是通過HASH演算法實現的。要判斷block是否存在於buffer cache中，就需要掃描一條chain，該chain中具有block的資訊。而掃描這個chain必須獲得一個latch，防止併發對chain照成破壞，如果未能獲得該latch，就說明此block是hot block，同時在資料庫中標記一個latch: cache buffers chains這個等待事件。如果該block存在於buffer cache中就不需要物理讀，如果不存在，就需要物理讀取該block到buffer cache中。為了能夠讀取，並修改該block，我們就需要pin住該block,防止併發對於該block造成破壞，所以如果別的session不能獲得pin，Oracle就會標記一個buffer busy waits等待事件。

引起該事件可能的原因包括全表掃描、索引過多、使用了序列來做索引等。

當出現了比較嚴重的cache buffers chains競爭時，我們可以找出該熱點塊的具體物件，找出引起該競爭的SQL語句，修改不良的SQL。如果不能更改應用，可以嘗試將資料分散，設定高的pctfree，使用較小的block size，檢視SQL執行計劃是否正確，減少邏輯讀。

---

對於4）read waits，包括db file sequential read, db file scattered read，
前者表示掃描索引，可以檢查索引是否有大量碎片；
後者表示存在全表掃描。
詳細見以下博文：
http://blog.itpub.net/22621861/viewspace-1309661/

對於5) Free Buffer waits

表示將Buffer Cache寫入到磁碟的速度，趕不上其他應用申請空閒Buffer Cache的速度。解決辦法：
1）是否有全表掃描
2）檢查db_cache_size設定
3）檢查DB_WRITER_PROCESSES，建議不超過CPU個數。

---

共享池問題

可參考：
語句共享性問題
http://blog.itpub.net/22621861/viewspace-1307202/

---

PGA

對於PGA，應重點關注PGA命中率（PGA Cache Hit%）
與PGA有關的等待事件有direct path read temp和direct path write temp。
這些等待事件表示PGA空間有限，導致了臨時表空間操作。

查詢在PGA和臨時表空間之間多次往返操作的SQL

點選(此處)摺疊或開啟

1. SELECT sql_text,
   
2.     sum(onepass_executions) onepass_cnt,
   
3.     sum(multipasses_executions) mpass_cnt
   
4. FROM V$SQL s, V$SQL_WORKAREA wa
   
5. WHERE s.address = wa.address
   
6. GROUP BY sql_text
   
7. HAVING sum(onepass_executions+multipasses_executions) > 0;

---

分析PGA使用狀況

點選(此處)摺疊或開啟

1. COL NAME FORMAT A30
2. SELECT * FROM V$PGASTAT

NAME                                VALUE UNIT
------------------------------ ---------- ------------------------
aggregate PGA target parameter  199229440 bytes
aggregate PGA auto target       131300352 bytes
global memory bound              39845888 bytes
total PGA inuse                  53477376 bytes
total PGA allocated             107995136 bytes
maximum PGA allocated           176616448 bytes
total freeable PGA memory        12451840 bytes
process count                          27
max processes count                    36
PGA memory freed back to OS     527171584 bytes
total PGA used for auto workar     137216 bytes


NAME                                VALUE UNIT
------------------------------ ---------- ------------------------
eas


maximum PGA used for auto work    4290560 bytes
areas


total PGA used for manual work          0 bytes
areas


maximum PGA used for manual wo     268288 bytes
rkareas




NAME                                VALUE UNIT
------------------------------ ---------- ------------------------
over allocation count                   0
bytes processed                 923526144 bytes
extra bytes read/written                0 bytes
cache hit percentage                  100 percent
recompute count (total)              1969

重點關注over allocation count條目，該條目表示超出PGA_AGGREGATE_TARGET引數設定的PGA目標值。此時，應考慮增大PGA_AGGREGATE_TARGET

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1308909/，如需轉載，請註明出處，否則將追究法律責任。