故障排除：Shared Pool優化和Library Cache Latch衝突優化

文件內容

適用於:

用途

簡介

本文件旨在介紹從Oracle 7到Oracle 11g shared pool調優的關鍵問題。特別對於存在下列問題的系統非常重要：

• library cache latch/es或者latch:library cache之類的 Latch爭用
• shared pool latch 或者 latch:shared pool 之類的Latch爭用
• 高CPU解析時間
• V$LIBRARYCACHE 中的高reloads
• 多版本的cursors
• 大量的parse call
• 經常發生ORA-04031 錯誤

提出問題，得到幫助並分享您的心得

排錯步驟

什麼是shared pool？

Oracle 在SGA的一個特定區域中保留SQL語句, packages, 物件資訊以及其它一些內容，這就是大家熟悉的shared pool。這個共享記憶體區域是由一個複雜的cache和heap manager 構成的。它需要解決三個基本問題：

1. 每次分配的記憶體大小是不一致的，從幾個位元組到上千個位元組；
2. 因為shared pool的目的是為了最大化共享資訊，所以不是每次一個使用者用完之後就可以釋放這段記憶體（在傳統的heap manager方式會遇到這個問題）。記憶體中的資訊可能對於其他session來說是有用的——Oracle並不能事先知道這些內容是否會被再次用到；
3. Shared pool中的內容不能被寫入到硬碟區域中，這一點和傳統cache是不一樣的。只有“可重建”的資訊可以被覆蓋，因為他們可以在下次需要時重建。

基於這些背景，我們就可以理解shared pool的管理是一件非常複雜的事情。下面的章節列出了一些影響shared pool效能和它相關的latch的關鍵問題，包括：

專用術語

Literal SQL

一個Literal SQL語句是指在predicate中使用具體值，而不是使用繫結變數,即不同的執行語句使用的具體值可能是不一樣的。
例1：應用程式使用了：

而不是:

例2: 以下語句不用繫結變數但是也不會被認為是literal SQL，因為這個語句可以被多次執行共享。

例 3: 如果整個應用都是用相同的值'2.0'來檢查'version'的話，那麼這個語句可以被認為是可以共享的。

Hard Parse（硬解析）

如果一個新的SQL被髮起，但是又不在shared pool裡面的話，它將被完整的解析一次。例如：Oracle必須在shared pool中分配記憶體，檢查句法和語義等等……這被稱為hard parse，它在CPU使用和latch獲取上的都是非常消耗資源的。

Soft Parse（軟解析）

如果一個session發起一個已經在shared pool中的SQL語句並且它可以使用一個當前存在的版本，那麼這個過程被稱為一個'soft parse'。對於應用來說，它只需請求解析這個語句。

完全相同的語句?

如果兩個SQL語句的含義相同但是沒有使用相同的字元，那麼Oracle認為它們是不同的語句。比如SCOTT在一個Session中提交的這兩個語句：

儘管它們實際上是相同的，但是因為大寫字母‘E’和小寫字母'e'的區別，他們不會被認為是完全相同的語句。 

Sharable SQL

如果是兩個不同的session發起了完全相同的SQL語句，這也不意味著這個語句是可以共享的。比如說：使用者SCOTT下有一個表EMP，發起了下面的語句：

使用者FRED 有一個自己的表也叫EMP並且發起相同的語句:

儘管語句完全一樣但是由於需要訪問的EMP表是不同的物件，所以需要對這條語句產生不同的版本。有很多條件來判斷兩個完全一致的SQL文字是不是真的是完全相同（以至於他們可以被共享），包括：

• 語句中引用的所有的物件名必須都被解析成實際相同的物件
• 發起語句的session中的optimizer相關的引數應該一致
• 繫結變數的型別和長度應該是"相似的"
  (這裡不做詳細討論，但是型別和長度的不同確實會導致語句被分為不同的版本)
• 發起語句的NLS (National Language Support)設定必須相同

語句的版本

正如之前在'Sharable SQL'中描述的，如果兩個語句字面上完全相同但是又不能被共享，則會對相同的語句產生不同的'version'，即版本。如果Oracle要匹配一個包含多個版本的語句，它將不得不檢查每一個版本來看它們是不是和當前被解析的語句完全相同。所以最好用以下方法來避免高版本數（high version count）：

• 客戶端使用的繫結變數最大長度需標準化
• 如果有大量的schema會包含相同名字的物件，那麼避免使用一個相同的SQL語句。比如： SELECT xx FROM MYTABLE; 並且每個使用者都有一個自己的 MYTABLE 的情況
• 在Oracle 8.1可以將 _SQLEXEC_PROGRESSION_COST 設定成'0'

Library Cache和Shared Pool latches

shared pool latch是用來保護從shared pool中分配和釋放記憶體的關鍵性操作。

Library cache latche(以及Oracle 7.1中的library cache pin latch)是用來保護library cache 中的操作。

所有的這些Latch都是潛在的資源爭用的物件，latch gets發生的次數直接受到shared pool中活動（activity）個數的影響，特別是parse操作。任何減少latch gets或者shared pool中活動（activity）個數的嘗試都有助於提高效能和可擴充套件性。 

Literal SQL和Shared SQL的比較

這一個小章節中描述了literal SQL和sharable SQL各自的優點：

Literal SQL

在有完整的統計資訊並且SQL語句在predicate（限定條件）中使用具體值時，基於成本的優化器 (CBO)能工作的最好。比較下面的語句：

和

對於第一個語句，CBO可以使用已經收集的histogram來判斷是否使用全表掃描比使用TOTAL_COST列上索引掃描快（假設有索引的話）。第二個語句CBO並不知道繫結變數":bindA"對應行數的比例，因為該繫結變數沒有一個具體的值以確定執行計劃。例：":bindA" 可以是 0.0或者99999999999999999.9。

Orders表上兩個不同的執行路徑的響應時間可能會不同，所以當你需要CBO為你選出最好的執行計劃的時候，選用使用literal語句會更好。在一個典型的Decision Support Systems（決策支援系統）中，重複執行'標準'語句的時候非常少，所以共享一個語句的機率很小，而且花在Parse上的CPU時間只佔每個語句執行時間的非常小一部分，所以更重要的是給optimizer儘可能詳細的資訊，而不是縮短解析時間。 

Sharable SQL

如果應用使用了literal (無共享) SQL，則會嚴重限制可擴充套件性和生產能力。在對CPU的需求、library cache 和 shared pool latch的獲取和釋放次數方面，新SQL語句的parse成本很高。

比如：僅僅parse一個簡單的語句就可能需要獲取和釋放library cache latch 20或者30次。

除非它是一個臨時的或者不常用的SQL，並且需要讓CBO得到儘可能多的資訊來生成一個好的執行計劃，否則最好讓所有的SQL是共享的。 

減輕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：

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

值40，5和30只是示例，這個查詢查詢前40個字元相同的，只被執行過很少次數，而又至少在shared pool裡出現30次的語句。通常來說，literal語句以下面的形式開始，並且每個語句的前面部分字元是相同的：

請檢視你的應用中使用的工具的文件來決定如何在語句中使用繫結變數。 

避免 Invalidations

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

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

更多的詳細資訊，請參考 Note:115656.1 和 Note:123214.1

CURSOR_SHARING 引數 (8.1.6 以上)

<Parameter:cursor_sharing> (在Oracle8.1.6引入).
這個引數需要小心使用。如果它被設為FORCE，那麼Oracle會盡可能用系統產生的繫結變數來替換原來SQL中的literals部分。對於很多僅僅是literal不一樣的相似的語句，這會讓它們共享cursor。這個引數可以在系統級別或者session級別動態設定：

或者

或者在init.ora中設定

在Oracle9i（以上），可以設定CURSOR_SHARING=SIMILAR。如果這些語句只是literal部分不同，並且這些literal不會對SQL的含義有影響，或者可能會導致使用不同的執行計劃，那麼SIMILAR會共享這些語句。此增強功能適用於當FORCE會產生一個不同並且不是想要的執行計劃時，從而提高了引數CURSOR_SHARING的可用性。設定CURSOR_SHARING=SIMILAR， Oracle會決定哪些literals可以被"安全"的替換成繫結變數，這樣做的結果是有些SQL在可能產生更好執行計劃的時候也不會被共享。

關於這個引數的更多詳細資訊，請參考 Note:94036.1。

SESSION_CACHED_CURSORS 引數

<Parameter:session_cached_cursors> 是一個可以在instance級別或者session級別設定的數值引數:

數值NNN 決定在一個session中可以被'cached'的cursor的個數。

當一個語句被parse的時候，Oracle會首先檢查session的私有快取中指向的語句，如果有可被共享的語句版本的話，它就可以被使用。這為經常被parse的語句提供了一個捷徑，可以比soft或者hard parse使用更少的CPU和非常少的Latch get。

為了被緩衝在session快取中，同樣的語句必須在相同的cursor中被parse 3次，之後一個指向shared cursor的指標會被新增到你的session快取中。如果session快取cursor已達上限，則最近最少使用的那一個會被替換掉。

如果你還沒有設定這個引數，建議先設定為50作為初始值。之後檢視bstat/estat報告的統計資訊章節的'session cursor cache hits'的值，從這個值可以判斷cursor快取是否有作用。如果有必要的話，可以增加或者減少cursor快取的值。SESSION_CACHED_CURSORS對於forms經常被開啟和關閉的Oracle Forms應用非常有用。 

CURSOR_SPACE_FOR_TIME 引數

<Parameter: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被廢棄。

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

SHARED_POOL_RESERVED_SIZE 引數

已經有相當多的文件解釋過引數 <Parameter: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%也許就不夠了。

檢視檢視 <View:v$shared_pool_reserved> 的FREE_SPACE列可以很容易監控保留區域的使用情況。

SHARED_POOL_RESERVED_MIN_ALLOC 引數

在 Oracle8i 這個引數是隱藏的.

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

SHARED_POOL_SIZE 引數

<Parameter: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以上版本被大大減少了。.

參考 Document 1012046.6 來根據工作量計算SHARED_POOL_SIZE 需要的大小。

_SQLEXEC_PROGRESSION_COST parameter 引數 (8.1.5 以上)

這是一個Oracle 8.1.5引入的隱含引數。這裡提到它是因為預設設定可能導致SQL共享方面的一些問題。設定成0會避免在shared pool 中產生語句高版本的問題。

例: 在init.ora 檔案中增加這個引數

參考 Document 68955.1 獲取關於這個引數的更多資訊。

預編譯器的 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這些物件。

Flushing（清空） SHARED POOL

在使用大量literal SQL的系統中，shared pool隨時間推移會產生大量碎片進而導致併發能力的下降。Flushing shared pool能夠使得很多小塊碎片合併，所以經常能夠在一段時間內恢復系統的效能。清空之後可能也會產生短暫的效能下降，因為這個操作同時也會把沒造成shared pool碎片的共享SQL也清除了。清空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，而只清空其中的單個物件。下面的文件說明了10g和11g中如何清空library cache heap。

使用 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"。對Large Pool的描述請參考Note:62140.1.

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

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

使用SQL 檢視Shared Pool問題

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

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

• 查詢literal SQL

這個語句有助於找到那些經常被使用的literal SQL – 請檢視上面的"消除 Literal SQL"

另一種方式是按照"plan_hash_value"進行分組：

• 檢索Library Cache hit ratio

如果misses/executions高於1%的話，則需要嘗試減少library cache miss的發生。

• 檢查 hash chain 的長度:

這個語句正常應該返回0行。如果有任何HASH_VALUES存在高的count（兩位數的）的話，你需要檢視是否是bug的影響或者是literal SQL使用了不正常的形式。建議進一步列出所有有相同HASH_VALUE的語句。例如：

如果這些語句看起來一樣，則查詢V$SQLTEXT去找完整的語句。有可能不同的SQL文字會對映到相同的hash值，比如：在7.3中，如果一個值在語句中出現2次而且中間正好間隔32個位元組的話，這兩個語句會對映出相同的hash值。

• 檢查高版本:

在上面的"Sharable SQL"章節中，我們已經描述了，一個語句的不同"版本"是當語句的字元完全一致但是需要訪問的物件或者繫結變數不一致等等造成的。在Oracle8i的不同版本中因為進度監控的問題也會產生高版本。在這篇文件的前面描述過了，我們可以把_SQLEXEC_PROGRESSION_COST 設成'0'來禁止進度監控產生高版本。

• 找到佔用shared pool 記憶體多的語句:

 這裡MEMSIZE取值為shared pool大小的10%，單位是byte。這個語句可以查出佔用shared pool很大記憶體的那些SQL，這些SQL可以是相似的literal語句或者是一個語句的不同版本。

• 導致shared pool 記憶體'aged' out的記憶體分配

注意: 因為這個查詢在返回不超過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問題- 參考 Document 316533.1 .

Bug 修復和增強功能

參見以下文件：

參考