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

bisal發表於2013-09-07
優化

減輕Shared Pool負載

Parse一次並執行多次
       在OLTP型別的應用中，最好的方法是隻讓一個語句被解析一次，然後保持這個cursor的開啟狀態，在需要的時候重複執行它。這樣做的結果是每個語句只被Parse了一次(不管是soft parse還是hard parse)。顯然，總會有些語句很少被執行，所以作為一個開啟的cursor維護它們是一種浪費。
       請注意一個session最多隻能使用引數:open_cursors定義的cursor數，保持cursor開啟會增加總體open cursors的數量。
       OCI中開發者能直接控制cursor，在預編譯器中，HOLD_CURSOR引數控制cursor是否被保持開啟。 

消除 Literal SQL
       如果你有一個現有的應用程式，你可能沒法消除所有的literal SQL，但是你還是得設法消除其中一部分會產生問題的語句。從V$SQLAREA檢視可能找到適合轉為使用繫結變數的語句。下面的查詢列出SGA中有大量相似語句的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
;

在10g以上的版本可以用下面的語句:

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC;

注意：如果系統中有library cache latch爭用的問題，上面的語句會導致爭用加劇。

避免Invalidations
       有些命令會將cursor的狀態變成成INVALIDATE。這些命令直接修改cursor相關物件的上下文環境。它包括TRUNCATE, 表或索引上的ANALYZE或DBMS_STATS.GATHER_XXX，關聯物件的許可權變更。相對應的cursor會留在SQLAREA中，但是下次被引用時會被完全reload並重新parse，所以會對資料庫的整體效能造成影響。

下面的查詢可以幫我們找到Invalidation較多的cursor:

SELECT SUBSTR(sql_text, 1, 40) "SQL",
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;

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

相關文章