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

bisal發表於2013-09-07
優化

CURSOR_SHARING 引數 (8.1.6 以上)
       這個引數需要小心使用。如果它被設為FORCE，那麼Oracle會盡可能用系統產生的繫結變數來替換原來SQL中的literals部分。對於很多僅僅是literal不一樣的相似的語句，這會讓它們共享cursor。這個引數可以在系統級別或者session級別動態設定：
ALTER SESSION SET cursor_sharing = FORCE;
或者
ALTER SYSTEM SET cursor_sharing = FORCE;
或者在init.ora中設定
注意：因為FORCE會導致系統產生的繫結變數替換literal，優化器(CBO)可能會選擇一個不同的執行計劃，因為能夠產生最好執行計劃的literal值已經不存在了。

注意: Similar在Oracle 12中不推薦使用。(譯者注：根據Note:1169017.1，Oracle12將會移除cursor_sharing = SIMILAR的設定，而且在11g中就已經不推薦使用了，因為有Adaptive Cursor Sharing(傳說可以根據資料量的實際大小來選擇合適的執行計劃，避免10g之前的繫結變數窺探)的新特性)，請參考: Document:1169017.1 ANNOUNCEMENT: Deprecating the cursor_sharing = SIMILAR setting。

SESSION_CACHED_CURSORS 引數
是一個可以在instance級別或者session級別設定的數值引數:
ALTER SESSION SET session_cached_cursors = NNN;
數值NNN決定在一個session中可以被'cached'的cursor的個數。
當一個語句被parse的時候，Oracle會首先檢查session的私有快取中指向的語句，如果有可被共享的語句版本的話，它就可以被使用。這為經常被parse的語句提供了一個捷徑，可以比soft或者hard parse使用更少的CPU和非常少的Latch get。
為了被緩衝在session快取中，同樣的語句必須在相同的cursor中被parse 3次，之後一個指向shared cursor的指標會被新增到你的session快取中。如果session快取cursor已達上限，則最近最少使用的那一個會被替換掉(LRU策略)。
如果你還沒有設定這個引數，建議先設定為50作為初始值。之後檢視bstat/estat報告的統計資訊章節的'session cursor cache hits'的值，從這個值可以判斷cursor快取是否有作用。如果有必要的話，可以增加或者減少cursor快取的值。SESSION_CACHED_CURSORS對於forms經常被開啟和關閉的Oracle Forms應用非常有用。 

CURSOR_SPACE_FOR_TIME 引數
       控制同一個語句不同執行之間一個cursor是否部分被保持（pin）住。如果設定其他引數都沒效果的話，就值得嘗試這個引數。這個引數在有不經常被使用的共享語句，或者有非常多的cursor被pinning / unpinning的時候是有幫助的。(檢視檢視：v$latch_misses – 如果大多數latch等待是因為cursor的pinning和 unpinning導致的"kglpnc: child"和"kglupc: child") .
你必須保證shared pool對於工作負載來說是足夠大的，否則效能會受到嚴重影響而且最終會產生ORA-4031錯誤。
如果你把這個引數設為TRUE，請留意：
如果SHARED_POOL對於工作負載來說太小的話更容易產生ORA-4031錯
如果你的應用有cursor洩漏，那麼洩漏的cursor會浪費大量記憶體並在一段時間的執行之後對效能產生負面影響。 
目前已知的設定為true可能會導致的問題：
Bug:770924 (Fixed 8061 and 8160) ORA-600 [17302] may occur 
Bug:897615 (Fixed 8061 and 8160) Garbage Explain Plan over DBLINK 
Bug:1279398 (Fixed 8162 and 8170) ORA-600 [17182] from ALTER SESSION SET NLS... 


CLOSE_CACHED_OPEN_CURSORS 引數
這個引數已經在Oracle8i被廢棄。
控制當一個事務提交時是否PL/SQL cursor被關閉。預設值是FALSE，該設定在不同commits之後保持PL/SQL cursor開啟以減少hard parse的次數。如果設成TRUE 的話可能會增加SQL在不用的時候被從shared pool 中清除出去的可能性。 

SHARED_POOL_RESERVED_SIZE 引數
       已經有相當多的文件解釋過引數。這個引數在Oracle 7.1.5被引進，它把shared pool 的一部分預留出來用於較大記憶體的分配。這個預留區域是從shared pool自身劃分出來的。
       從實踐角度來說我們應該把SHARED_POOL_RESERVED_SIZE設成SHARED_POOL_SIZE的10%，除非shared pool非常大或者 SHARED_POOL_RESERVED_MIN_ALLOC被設得小於預設值：
       如果shared pool 非常大的話，設成10%會浪費很多記憶體因為可能設成幾MB就夠用了。 
如果SHARED_POOL_RESERVED_MIN_ALLOC被設的較小，則很多的空間請求都會符合從保留空間中分配的條件，那麼10%也許就不夠了。 
檢視檢視v$shared_pool_reserved的FREE_SPACE列可以很容易監控保留區域的使用情況。

SHARED_POOL_RESERVED_MIN_ALLOC引數
在 Oracle8i這個引數是隱藏的.
儘管有些情況下SHARED_POOL_RESERVED_MIN_ALLOC設成4100或者4200可能對緩解較大壓力下的shared pool的衝突有幫助，但是在大多數情況下應保持預設值。

SHARED_POOL_SIZE引數
SHARED_POOL_SIZE控制shared pool自己的大小，它能對效能造成影響。如果太小，則共享的資訊會被從共享池中交換出去，過一陣子有需要被重新裝載(重建)。如果literal SQL使用較多而且shared pool又很大，長時間使用後內部記憶體freelist上會產生大量小的記憶體碎片，使得shared pool latch被持有的時間變長，進而導致效能問題。在這種情況下，較小的shared pool也許比較大的shared pool好。因為 Bug:986149的改進，這個問題在8.0.6和8.1.6以上版本被大大減少了。
注意: 一定要避免由於shared pool設定過大進而導致的swap的發生的情況，因為當swap發生的時候效能會急劇下降。
參考 Note:1012046.6 來根據工作量計算SHARED_POOL_SIZE 需要的大小。

預編譯器的HOLD_CURSOR和RELEASE_CURSOR選項
      當使用Oracle 預編譯器預編譯程式的時候，shared pool的行為可以通過引數RELEASE_CURSOR和HOLD_CURSOR來控制。這些引數可以決定當cursor執行完畢之後library cache和session cache中cursor的狀態。
關於這個引數的更多資訊，請參考 Note:73922.1
將cursor固定(pinning)在shared pool中
另外一種減少library cache latch使用的方法是將cursor固定在shared pool中，詳見以下文件：
Note:130699.1  How to Reduce 'LIBRARY CACHE LATCH' Contention Using a Procedure to KEEP Cursors Executed> 10 times

DBMS_SHARED_POOL.KEEP
這個儲存過程 (RDBMS/ADMIN 目錄下的DBMSPOOL.SQL指令碼中有定義) 可以用來將物件KEEP到shared pool中， DBMS_SHARED_POOL.KEEP可以 'KEEP' packages, procedures, functions, triggers (7.3+) 和 sequences (7.3.3.1+) ，在 Note:61760.1中有完整的描述。
通常情況下，建議將那些需要經常使用的package一直keep在shared pool中。KEEP操作在資料庫啟動後需要儘快實施，因為在shutdown之後Oracle不會自動重新keep這些物件。
注意:在Oracle 7.2之前DBMS_SHARED_POOL.KEEP實際上不會把需要KEEP的物件完整的放到shared pool中。所以建議在每一個要被KEEP的package中放一個空的儲存過程，在執行完DBMS_SHARED_POOL.KEEP之後再呼叫一下這個空儲存過程來保證物件被完全裝載。這在7.2之後已經修復了。 

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

相關文章