【每日一摩斯】-Shared Pool優化和Library Cache Latch衝突優化 (1523934.1)-系列6

bisal發表於2013-09-07
優化

使用SQL 檢視Shared Pool問題

       這一章節展示了一些可以用來幫助找到shared pool中的潛在問題的SQL語句。這些語句的輸出最好spool到一個檔案中。

注意:這些語句可能會使latch競爭加劇，我們在上面的"使用 V$ 檢視 (V$SQL 和 V$SQLAREA)" above.


查詢literal SQL

SELECT substr(sql_text,1,40) "SQL", 
               count(*) , 
               sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;
這個語句有助於找到那些經常被使用的literal SQL

檢索Library Cache hit ratio

SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
如果misses/executions高於1%的話，則需要嘗試減少library cache miss的發生。
改造：
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;

檢查hash chain的長度
SELECT hash_value, count(*)
FROM v$sqlarea 
GROUP BY hash_value
HAVING count(*) > 5;
這個語句正常應該返回0行。如果有任何HASH_VALUES存在高的count（兩位數的）的話，你需要檢視是否是bug的影響或者是literal SQL使用了不正常的形式。建議進一步列出所有有相同HASH_VALUE的語句。例如：
SELECT sql_text FROM v$sqlarea WHERE hash_value= ;
如果這些語句看起來一樣，則查詢V$SQLTEXT去找完整的語句。有可能不同的SQL文字會對映到相同的hash值，比如：在7.3中，如果一個值在語句中出現2次而且中間正好間隔32個位元組的話，這兩個語句會對映出相同的hash值。

檢查高版本
SELECT address, hash_value,
               version_count ,
               users_opening ,
               users_executing,
               substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10;
在上面的"Sharable SQL"章節中，我們已經描述了，一個語句的不同"版本"是當語句的字元完全一致但是需要訪問的物件或者繫結變數不一致等等造成的。在Oracle8i的不同版本中因為進度監控的問題也會產生高版本。在這篇文件的前面描述過了，我們可以把_SQLEXEC_PROGRESSION_COST 設成'0'來禁止進度監控產生高版本。

找到佔用shared pool 記憶體多的語句
SELECT substr(sql_text,1,40) "Stmt", count(*),
               sum(sharable_mem) "Mem",
               sum(users_opening) "Open",
               sum(executions)       "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > &MEMSIZE;
這裡MEMSIZE取值為shared pool大小的10%，單位是byte。這個語句可以查出佔用shared pool很大記憶體的那些SQL，這些SQL可以是相似的literal語句或者是一個語句的不同版本。

導致shared pool 記憶體'aged' out的記憶體分配
SELECT * 
FROM x$ksmlru
WHERE ksmlrnum>0;
注意: 因為這個查詢在返回不超過10行記錄後就會消除X$KSMLRU的內容，所以請用SPOOL儲存輸出的內容。X$KSMLRU表顯示從上一次查詢該表開始，哪些記憶體分配操作導致了最多的記憶體塊被清除出shared pool 。有些時候，這會有助於找到那些持續的請求分配空間的session或者語句。如果一個系統表現很好而且共享SQL 使用得也不錯，但是偶爾會變慢，這個語句可以幫助找到原因。關於X$KSMLRU 的更多資訊請檢視Note:43600.1。

在不同Oracle Releases中的都會遇到的問題

在不同的release中有一些通用的會影響shared pool效能的問題：

>增加每個CPU的處理能力可以減少latch被持有的時間從而有助於在Oracle的各個release上減少shared pool競爭。換一個更快的CPU一般來說會比增加一個慢的CPU效果要好。

>如果你設定了一個EVENT，不管基於什麼原因，請讓Oracle support檢查這個event是否會對shared pool的效能造成影響。

>確保Oracle例項有足夠的記憶體，避免SGA記憶體被作業系統swap交換出去的風險。

例如: 在AIX上作業系統的不正確設定可能會導致 shared pool問題- 參考Note:316533.1

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

相關文章