優化Shared Pool Latch與Library Cache Latch競爭

這本文章的目的是介紹解決oracle7到oracle11的共享池問題.如果你的系統出現以下任何問題:
對於library cache latch或latch:library cache的閂鎖競爭
對於shared pool latch或latch:shared pool的閂鎖競爭
高cpu解析時間
v$librarycache的高reloads次數
高版本遊標
大量的解析呼叫
頻繁的ora-04031錯誤


解決問題的步驟
什麼是共享池
oracle在sga中的一個區域保留sql語句,包,物件資訊和許多其它資訊這個區域就叫作共享池.共享池由於一個複雜的快取和堆管理器組合而成的,它有三個基本的問題要克服:
1.記憶體分配單元不是一個常量---共享池中的記憶體分配可以是幾個位元組到幾千位元組
2.當使用者使用完後不是所有的記憶體都能釋放(這種情況出現在傳統的堆管理).共享池的目的是最大化的共享資訊.在記憶體中的資訊可能對另外的會話有用---oracle事先並不知道這些資訊將來能不能被使用
3.這裡沒有磁碟page out,所以不象傳統的快取有一個檔案備份儲存.只會當資訊從快取中消失後當下次需要時進行重建.
基於上面的三點就可以知道管理共享池是一個複雜的工作.下面將介紹影響共享池效能的關鍵問題和與它相關的閂鎖競爭.


Literal SQL
一個literal sql是在謂詞中使用了literal值而沒有使用繫結變數的sql語句.不同的literal值對於語句來說可能會有不同的執行計劃.
例如:
SELECT * FROM emp WHERE ename='CLARK';
使用應用程式來呼叫可能是:
SELECT * FROM emp WHERE ename=:bind1;


例如:
select sysdate from dual;
雖然沒有使用繫結變數但不會被認為是一個literal語句,這個語句是能被共享的.


例如:
SELECT version  FROM app_version WHERE version>2.0;
如果相同的語句被用來檢查應用程式的版本且literal值'2.0'總是相同的那麼這個語句會被認為可以被共享.


硬解析
如果一個新呼叫的sql語句在共享池中不存在那麼就要進行全面的解析.oracle會對這個語句從共享池中分配記憶體,檢查語法和語義等等這稱為硬解析對於cpu的消耗和latch獲取的執行次數來說都是很能昂貴的.


軟解析
如果一個會話發出的sql語句它已經在共享池中存在那麼對於這個語句能使用一個已經存的版本這稱為軟解析.對於應用程式來說它已經要求解析這個語句了.


相同的語句
如果兩個sql語句的意思相同但有些字元的格式不同oracle會認為這是不同的語句.例如下面是在單個會話中scott使用者發出的語句:
SELECT ENAME from EMP;


SELECT ename from emp;
雖然兩個語句實際上是相同的但是由於大小寫的原因會被認為是不同的語句.例如E與e是不同的.




共享sql
如果兩個會話發出相同的語句但是不一定能共享.例如scott使用者有一個叫EMP的表並執行以下語句:
SELECT ENAME from EMP;
使用者fred也有一個叫EMP的表並執行以下語句:
SELECT ENAME from EMP;
雖然語句的文字相同的但是EMP是來自不同使用者的物件.因此對於相同的語句會有不同的遊標版本.有許多資訊要檢查來判斷兩個語句是否是真的相同包括:
所有的物件名必需是相同的真實物件
發出語句的會話的optimizer goal要相同
任何繫結變數的型別和長度應該是相似的
每個語句的的國示語句支援環境必需相同


語句的版本
在共享sql中如果兩個語句的語句文字相同但不能共享那麼這些語句就被稱作相同語句的版本.在解析期間如果oracle使用多個版本來匹配一個語句那麼不得不檢查每一個版本來看是否與某個特定的版本語句相同.因此高版本語句最好要通過以下方式來避免:
由客戶來指定標準化的繫結變數長度
避免不同使用者使用相同的語句
在oracle8.1中將_SQLEXEC_PROGRESSION_COST設定為0


library cache和shared pool latches
共享池閂鎖(shared pool latches)是在共享池中分配和釋放記憶體時來保護關鍵操作的
庫快取閂鎖(library cache或oracle7.1中的library cache pin latch)是用來保護庫快取自身的操作


所有的閂鎖都是潛在的競爭點.請求閂鎖的次數會直接影響共享池中活動的數量,特別是解析操作.任何能夠減少共享池中的閂鎖請求和真實的活動數量的操作對於效能和可擴充套件性來說都是有好處的


literal sql與shared sql
literal sql
當語句引用的物件用完全的統計資訊和在語句謂詞中使用literal值時基於成本的優化器會工作的最好,例如:
SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;
與
SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;
對於第一個語句如果已經收集了直方圖資訊那麼基於成本的優化器會使用直方圖資訊來判斷是對orders表使用全表掃描還是使用total_cost列上的索引進行掃描.對於第二個語句基於成本的優化器不知道小於":bindA"的記錄佔整個記錄的百分比因為在判斷 一個執行計劃時繫結變數是沒有值的例如":bindA"可能是0.0或者99999.9


在這兩個語句的兩種執行計劃的響應時間之間會有數量級的差別.所以你如果想基於成本優化器選擇最佳的執行計劃最好使用literal sql語句.這是典型的決策支援系統它沒有任何標準的語句(發出重複的語句)所以能共享的語句就很少.在解析時消耗的 cpu數量通常佔執行語句所消耗cpu數量很小的百分比所以相比減少解析時間來說更重要的是給優化器更多的資訊.


shared sql
如果一個應用程式使用literal(unshared) sql那麼這是非常限制可擴充套件性和吞吐量的.解析一個新語句在cpu請求和庫快取閂鎖 和共享池閂鎖方面都是很昂貴的.即使解析一個簡單的sql語句可能也需要請求庫快取閂鎖20或30次.


最好的方法是使用所有的sql語句被共享除非是很少或不頻繁使用的sql語句,給基於成本的優化器更多的住處讓其生成一個最佳的執行計劃也是很重要的.


減少共享池的載入次數
解析一次/執行多次
到目前為止在OLTP系統中讓應用程式對sql語句只解析一次並將遊標開啟當請求它時就執行.這樣做的結果是對於每一個語句只在最初進行解析(可能是軟解析也可能是硬解析).很明顯有些語句是很少執行的因此對於這些語句保持開啟遊標會浪費資源.


注意一個會話只有(引數open_cursors)遊標可用且保持遊標為開啟狀態時才有可能增加併發開啟遊標的數量


在預編譯程式中hold_cursor引數控制著遊標是否保持持開狀態而OCI開發者可以直接控制遊標.




消除literal sql
如果一個程式你想消除所有的literal sql是不可能的但是在literal sql造成問題時還是要消除造成問題的這些literal sql語 句.通過檢視v$sqlarea檢視可以看到哪些literal語句是可以轉換使用繫結變數.下面的語句查詢在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
  ;


對於oracle10g使用以下查詢語句:
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


如果上面的查詢出來的sql造成了library cache latches的競爭那麼這些語句可能會更進一步的產生更嚴重的競爭問題.




避免無效遊標
有一些特定的操作會將遊標的狀態改變為invalidate.這些操作會直接修改與遊標相關物件的上下文.這些操作比如對錶或索引進行truncate,analyze或dbms_stats.gather_xxx操作,或者改變基礎物件的授權.這些相關的遊標仍然會保留在sqlarea中但是當它們下次被引用時,它們會被重新載入且重新完全解析,所以會影響整個效能.


下面的查詢能夠幫我們識別這些無效的遊標:
SELECT SUBSTR(sql_text, 1, 40) "SQL",
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;


cursor_sharing引數(8.1.6及以後版本)
引數cursor_sharing是在oracle8.1.6中引入的.
在這個版本中使用它要謹慎.如果這個引數被設定為force那麼literal值將會可能由系統生成的繫結變數來替換.對於多個相似的 且只有literal值不同的語句將會允許語句共享盡管應用程式提供的sql是使用的literal值.這個引數是動態引數可以在例項或會 話級別進行修改.
ALTER SESSION SET cursor_sharing = FORCE;
或
ALTER SYSTEM SET cursor_sharing = FORCE;
或者在init.ora檔案中進行設定


注意:當這個以數設定為force會用系統生成的繫結變數來替換literal值,這時基於成本的優化器可能會選擇與原先不同的執行計劃因為在優化器計算最佳執行計劃時沒有了literal值.


在oralce9i中,cursor_sharing可以設定為similar.similar用於語句可能在某些literal值不同的情況下,這會讓這些語句允許被 共享除非literal值影響了語句的意思或者影響了被優化的執行計劃的並行度.這增強了這個引數的可用性不象設定為force時通 常會造一個不同的不好的執行計劃.當cursor_sharing設定為similar時,oracle會判斷哪個literal使用繫結變數來替換是安全的這也會造成一些語句因為為了提供一個更好的執行時而不被共享.


cursor_sharing引數在oracle12c中會被丟棄.


session_cached_cursor引數
引數session_cached_cursor是一個數字引數它能在例項或會話級別使用下面的語句來進行修改:
ALTER SYSTEM SET session_cached_cursors = NNN;
或
ALTER SESSION SET session_cached_cursors = NNN;
這個NNN決定在你的會話中能快取多少個遊標
每當一個語句被解析時oracle首先會檢查你的私有會話快取中有沒有這個語句,如果對於這個語句存在一個共享的版本能被使用,
對於頻繁解析的語句與軟體解析或硬解析相比會使用更少的cpu和更少的閂鎖請求次數從而提供了一個快捷訪問.


為了能將相同的語句快取在會話快取中這個語句必須要使用相同的遊標解析3次然後這個共享遊標的一個指標會被增加到你的會話快取中.如要所有的會話快取遊標都在被使用那麼最近最少使用的遊標會被丟棄.


如果你沒有設定這個引數那麼建議將給它設定一個初始值50.在bstat/estat報告中的統計部分有一個'session cursor cache  hits'資訊顯示了會話快取遊標帶來的好處.這個會話快取遊標的大小可以根據需要增加或減少.


cursor_space_for_time引數
cursor_space_for_time引數在10.2.0.5和11.1.0.7中被丟棄
引數cursor_space_for_time控制著部分遊標是否在一個語句的不同執行計劃之間保持pinned.如果所有的失敗了它能在這些共享 語句被頻繁使用時或者在有顯著的pinning/unpinning遊標時(檢視v$latch_misses檢視如果大部分的latch等待是由於"kglpnc:child"和"kglupc:child",這是由於對遊標進行pinning/unpinning產生的)能帶來一些好處.


必須確保共享池對於工作負載來說是足夠大的否則效能會受到影響且會觸發ora-4031錯誤.
如果你設定此引數要注意:
如果shared_pool對於工作負載來說設定的太小那麼可能會經常觸發ora-4031錯誤.
如果你的程式有任何的遊標洩漏那麼洩漏的遊標在經過一段時間的操作後會浪費大量的記憶體對效能產生影響.
將這個引數設定為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 SESSIONSET NLS...


CLOSE_CACHED_OPEN_CURSORS引數
這個引數在oracle8i中已經廢棄了.
引數close_cached_open_cursors控制著當一個事務提交時plsql遊標是否關閉.預設值是false這意味著當事務提交時plsql遊標 保持開啟這能減少硬解析.如果這個引數設定為true那麼這將增加當sql不使用時從共享池中被清除的機會.


SHARED_POOL_RESERVED_SIZE引數
這個引數是在oracle7.1.5引入的對保留共享池大記憶體分配提供了一種方法.這個共享池保留區來自共享池本身.


從實用的角度shared_pool_reserved_size的大小一般設定為shared_pool_size的10%除非共享池很大或shared_pool_reserved_min_alloc相比於預設值設定的太小:
如果共享池非常大那麼10%可能會浪費大量的記憶體而實際上只有幾MB就夠了
如果shared_pool_reserved_min_alloc已經很小那麼許多空間請求可能從共享池部分能得到滿足那麼10%的大小就小了.


可以很容易的監控共享池保留區的使用情況查詢v$shared_pool_reserved檢視中的free_sapce列.


shared_pool_reserved_min_alloc引數
在oracle8i中這個引數是隱含引數
shared_pool_reserved_min_alloc引數一般使用其預設值,儘管在特定情況下4100或4200位元組可能會幫助解決共享池高負載時的一些競爭.


shared_pool_size引數
引數shared_pool_size控制著共享池本身的大小.共享池的大小會影響效能.如果共享池太小那麼它會將一些共享資訊從共享池中 清除而後續的請求就要重新載入.如果有大量的literal sql且共享池太大那麼長時間的操作會在內部記憶體的可用列表中建立一些 小的記憶體塊這會導致共享池閂鎖會被持有很長時間進而影響效能.在這種情況下小的共享池比大的共享池可能會執行的更好.
注意:共享池它本身不是很大因此會有大量的分頁或交換髮生那麼效能會呈數量級的降低.




_SQLEXEC_PROGRESSION_COST引數
這是一個隱含引數在oracle8.1.5中引入.這個引數的預設設定會造成一些sql共享的問題,將這個引數設定為0可以避免這個問題 但是又會在共享池中產生多版本語句.


注意如果將這個引數設定為0的另一個問題是在v$session_longops檢視中將不會記錄長時間執行的查詢.


預編譯程式的hold_cursor和release_cursor選項
當使用oracle預編譯程式共享池的行為可以通過使用引數release_cursor和hold_cursor來進行改變.這些引數將會判斷庫快取中游標的狀態和會話快取中一旦執行完成後遊標的狀態.


在共享池中pinning cursors
dbms_shared_pool.keep
這個過程(它的定義在rdbms/admin目錄下的dbmspool.sql指令碼中)能被用來將保留物件共享池中.dbms_shared_pool.keep允許保留包,過程,函式,觸發器和序列.


一般來說它通常需要標記哪些頻繁使用的包這樣讓它們總是被保留在共享池中.對應該應該在例項啟動後不久被保留在共享池中因為資料庫在執行重啟之後不會自動執行這個操作.


清空共享池
在使用大量literal SQL的系統中,shared pool隨時間推移會產生大量碎片進而導致併發能力的下降.Flushing shared pool能 夠使得很多小塊碎片合併,所以經常能夠在一段時間內恢復系統的效能.清空之後可能也會產生短暫的效能下降,因為這個操作同時也會把沒造成shared pool碎片的共享SQL也清除了.清空shared pool的命令是:
        ALTER SYSTEM FLUSH SHARED_POOL;
注意:如果顯式的使用以上命令,即使是用 DBMS_SHARED_POOL.KEEP而被保留的那些物件可能也會被釋放掉,包括它們佔用的記憶體.如果是隱式的flush(由於shared pool上的記憶體壓力)這個時候kept"的物件不會被釋放.


注意:如果sequence使用了cache選項,沖刷shared pool有可能會使sequence在其範圍內產生不連續的記錄.使用 DBMS_SHARED_POOL.KEEP('sequence_name','Q')來保持sequence會防止這種不連續的情況發生.


DBMS_SHARED_POOL.PURGE


也可以不重新整理整個shared pool,而只清空其中的單個物件.


使用 V$ 檢視 (V$SQL 和 V$SQLAREA)
注意有一些V$檢視需要獲取相關的latch來返回查詢的資料.用來展示library cache和SQL area的檢視就是值得注意的.所以我們建議有選擇性的執行那些需要訪問這種型別檢視的語句.特別需要指出的是,查詢V$SQLAREA會在library cache latch上產生大量的負載,所以一般可以使用對latch訪問比較少的v$sql做替代——這是因為V$SQLAREA的輸出是基於shared pool中所有語句的GROUP BY操作,而V$SQL沒有用GROUP BY操作.


MTS, Shared Server 和 XA


由於多執行緒伺服器(MTS)的User Global Area (UGA)是存放在shared pool中的,所以會增加shared pool的負載.在Oracle7上的 XA session也會產生同樣的問題,因為他們的UGA也是在shared pool裡面(在Oracle8/8i開始XA session不再把UGA放到shared  pool中).在Oracle8中Large Pool可以被用來減少MTS對shared pool活動的影響——但是,Large Pool中的記憶體分配仍然會使 用"shared pool latch".


使用dedicate connections(專有連線)替代MTS可以使UGA在程式私有記憶體中分配而不是shared pool.私有記憶體分配不會使用"shared pool latch",所以在有些情況下從MTS切換到專有連線可以幫助減少競爭.


在Oracle9i中,MTS被改名為"Shared Server".但是對於shared pool產生影響的行為從根本上說還是一樣的.


使用SQL檢視Shared Pool問題
這裡展示了一些可以用來幫助找到shared pool中的潛在問題的SQL語句.這些語句的輸出最好spool到一個檔案中
注意:這些語句可能會使latch競爭加劇
查詢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",
        SUM(RELOADS)/ SUM(PINS) "MISSES/EXECUTIONS"
        FROM V$LIBRARYCACHE;
如果misses/executions高於1%的話,則需要嘗試減少library cache miss的發生.


檢查 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
        ;
一個語句的不同"版本"是當語句的字元完全一致但是需要訪問的物件或者繫結變數不一致等等造成的.在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使用得也不錯,但是偶爾會變慢,這個語句可以幫助找到原因

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