Oracle Shared Pool Memory Management

Oracle在管理共享池記憶體方面面臨著難以置信的挑戰。多年來的所有改變、bug、補丁和各種效能問題都說明了這一點。雖然這可能會引起一些同情，但當面對與記憶體管理相關的棘手問題時，同情很快就會轉化為憤怒。在本節中，我將解釋如何管理共享池記憶體、多年來的管理進展、如何分配和釋放記憶體、如何處理可能出現的4031錯誤，以及最後如何解決共享池鎖存器爭用。

From Hashing to Subpools
在Oracle 7和Oracle 8i中，共享池管理是在一種有趣的雜湊結構幫助下執行的。如果還記得我們關於cache buffer 雜湊鏈與library cache雜湊鏈，那麼這將非常有意義，但這裡存在一種苦惱。當一個程式需要共享池中的記憶體時，它產生的雜湊和鏈與所請求的記憶體大小相關。鏈也通常被稱作heap，它是可用記憶體塊連結串列。因此，從概念上講，前幾個鏈與大約1KB的記憶體塊相關，後幾個鏈與大約2KB的記憶體塊相關，以此類推。雖然這確實很巧妙，但是經過一段時間對大小不一致的記憶體進行分配和釋放之後，鏈實際上可以變成幾千個節點長。請記住，雜湊緩衝區鏈的大小平均在0到1之間。所以一個由幾千個節點組成的鏈是巨大的。更糟的是，只有一個共享池latch鎖來覆蓋所有雜湊鏈!清洗共享池幫助很大，因為鏈將減少到一個可觀的規模。但這無法操作大型生產資料庫，因此Oracle不得不進行更改。

Oracle9i引入了子池，這自然會導致多個共享池鎖存器。基於雜湊的策略被多個子池替換，每個子池包含一個在標準LRU策略上操作的堆。Oracle也開始標準化記憶體需求大小，這增加了找到可接受記憶體大小塊的可能性。子池、多個共享池鎖latch和LRU策略極大地減少了共享池記憶體管理問題。如果您同時管理過Oracle8i和Oracle9i系統，您可能會經歷這種變化，並注意到有很大的不同。

資料庫系統中,共享池子池的數量可以透過檢視例項引數_kghdsidx_count或透過計算x$kghlu檢視中的行數來判斷。

下面的查詢顯示了與共享池子池相關的一系列SQL語句。在這個例子中，一個大小為800MB的共享池存在三個子池。x$ksmss查詢對於每個子池返回一行記錄並且如果存在java pool還會另外加一行記錄。設定子池數量的例項引數_kghdsidx_count不能被動態修改。如果你想影響Oracle呼叫一個子池號發生改變，你必須設定例項引數並回收例項。

SQL> @spspinfo
SQL> select sum(bytes/1024/1024) sp_size
2 from v$sgastat
3 where pool='shared pool';
SP Size (MB)
------------
         800
SQL> select count(*) no_sp from x$kghlu;
Num of SPs
----------
         4
SQL> select INST_ID, KSMDSIDX, KSMSSLEN
2 from x$ksmss
3 where ksmssnam='free memory';
INST_ID    KSMDSIDX   KSMSSLEN
---------- ---------- -----------
1          0          301989888
1          1          18818468
1          2          12659340
1          3          7697300
1          4          20482152
SQL> select i.ksppinm param,v.ksppstvl value
2 from x$ksppi i, x$ksppcv v
3 where v.indx=i.indx
4 and v.inst_id=i.inst_id
5 and i.ksppinm='_kghdsidx_count';
PARAM                VALUE
-------------------- -----
_kghdsidx_count      4

Oracle對子池的數量設定了嚴格的限制。在Oracle 11g中，可以使用7個共享池子池來啟動例項，但有8個子池，該例項沒有啟動——實際上，在重新啟動之前需要關閉例項。

有趣的是，Oracle不必遵從子池號的意願。實際上，在一個類似於上面查詢結果的Oracle資料庫11.1g的示例中，例項引數被設定為2，例項重新啟動，但是Oracle建立了三個子池。在Oracle資料庫11.2g中，例項引數再次被設定為2，例項重新啟動，並且按照指定的Oracle建立了兩個子池。在沒有手動設定例項引數的情況下執行Oracle資料庫11.1g和11.2g, Oracle只建立了一個子池。因此，儘管你可以影響甲骨文，它仍然保留做出改變的權利。

記憶體分配與回收
記憶體分配是相當簡單的。它遵循標準的LRU演算法並與pinning與locking一起使用。當一個Oracle程式(伺服器或後臺程式)請求記憶體時，Oracle核心中的一部分稱作為heap manager(堆管理器)的會被執行。雖然細節不斷變化，但概念演算法基本相同。

Oracle程式需要特定數量的記憶體，這些記憶體被轉換為多個特定大小記憶體塊的請求。堆管理器搜尋與每個請求匹配的單個大小的記憶體塊。多個記憶體塊(認為是非連續的)是不行的。如果程式請求4KB記憶體，堆管理器必須從共享池記憶體中返回4KB記憶體塊的地址。

在Oracle9i中，Oracle程式獲得子池latch，並將在放棄之前搜尋子池至多5次。因為記憶體的情況可能會發生急劇且快速的變化,允許多次傳遞會增加找到記憶體的可能性。然而在五次搜尋之後，當持有各自共享池latch時，如果合適的大小的記憶體塊沒有找到，Oracle將會放棄，並posts錯誤程式碼4031，“out of mmemory”資訊，並且會話將會停止處理。對於每個Oracle DBA來說，這在生產系統中將是不可接受的。

在Oracle Database 10g中，Oracle程式對記憶體的要求更加強烈。如果在五次搜尋之後在當前共享池中沒有找到合適的記憶體，程式將移動到另一個子池。這一過程將繼續進行到所有定義的子池被搜尋完為止。如果在這時，沒有找到合適的記憶體，就像以前一樣，Oracle將會放棄並posts 4031錯誤並且停止處理。Oracle在這個版本中所做的是消耗更多CPU和更長時間地持有共享池latch來減少返回錯誤訊息的機會。從資料庫操作的角度來看，效能較慢總比沒有效能好。在我們解決效能問題時，至少可以執行工作。

當記憶體不足時，Oracle將回收不被頻繁訪問的記憶體塊。可能在嘗試檢索SQL語句的文字時遇到過這種情況，並且它不再快取在共享池中。幸運地是，Oracle不會回收記憶體供其它物件使用。例如，如果一個遊標被pinned(固定)了，Oracle將不會回收相關的記憶體，不管該記憶體是不是被頻繁訪問。事實上，即使清除共享池也不會刪除被固定的遊標。如果真的想清空共享池並且想從頭開始，可以重啟例項。

共享池latch競爭識別與解決
共享池latch被用來序列化共享池記憶體的管理。這意味著像搜尋記憶體，LRU活動，分配記憶體與回收記憶體請求共享池latch這樣的操作。因為從Oracle 9i開始存在多個子池，並且每個子池有它自己的共享池latch，只要使用這個版本或之後的版本就可以大大減少共享池lat這個特別的解決方案非常簡潔，因為它只需要很少的工作，而且我們不是在玩弄Oracle的共享池LRU演算法。但是，請記住，更多的子池可能需要更多的共享池記憶體，需要重新啟動例項才能使例項引數更改生效，Oracle保留不尊重您的建議的權利ch競爭的可能性。但有些時候這些仍然不夠。下面有些方案可能減少latch獲取時間latch持有時間或者兩者都減少。

固定大且頻繁使用的物件
此策略用來確保物件成功進入快取，不用管記憶體活動或物件大小。任何包第一次被呼叫時，整個包被載入到記憶體中。操作中在啟用共享池後如果需要觸發，將強制執行大量的記憶體管理活動，這將導致物件不能被載入而觸發4031錯誤。即使如果物件被成功載入，使用者可能會注意到應用程式的延遲。

有些時候可能想要固定小物件。例如，假設一個物件有一種高強度活動模式，長時間的暫停導致物件的記憶體被釋放，然後是另一段高強度活動。為了確保沒有應用程式延遲且為了減少記憶體管理，我們可以簡單固定物件。

大多數大型Oracle應用程式都提供一個指令碼，其中包含要固定在共享池中的物件，並且它們將建議在例項啟動後立即執行該指令碼。重要的是要知道，即使您的應用程式供應商提供了這樣一個列表，您也可以透過了解您的組織實際使用物件的方式來細化這個列表。供應商應用程式開發人員通常會建立固定列表。然而大多數應用程式開發商認為他們的物件是最重要的並且應該總是被固定。但實際上，很多時候，在應用程式在生產環境中執行之前，沒有人真正知道您的組織將如何使用它。因此如果出現4031錯誤，這對於修改固定列表來說是一個好訊息。

想要確保物件總是固定在共享池中有四個簡單步驟要操作。關鍵詞pin常被使用，dbms_shared_pool包的keep函式被用來確保物件保留在共享池中。預設情況下當建立資料庫時這個包不會被載入，因此第一步就是要載入它。下面的程式碼就是用來建立這個過程。

[oracle@jytest1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 14:30:28 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
Session altered.
Package created.
Grant succeeded.
Session altered.

下一步驟是找到大的或頻繁的物件。Oracle保持對共享池物件使用進行跟蹤並且可以透過v$db_object_cache檢視來檢視這些資訊。下面是使用OSM指令碼dboc.sql來識別潛在的物件。您可能會看到一組比其他包大得多的包，以及執行得比其他包頻繁得多的包。還可能有一些物件，您個人知道它們具有不同尋常的執行配置檔案，而您希望快取它們。

一旦有了要儲存的物件列表，下一步就是確定如何將它們放入快取中。keep函式用於固定物件，或者更好地說，用於將物件儲存在共享池中。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10
DB/Inst: jy/jy2                                                   07-May 08:26am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary
                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
SYS          STANDARD                            PBDY     0     3    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0     1   122 NO
SYS          DBMS_SQLDIAG                        PBDY     0     1    40 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_AUTO_TASK                      PBDY     0     0    24 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0    28 NO
SYS          DBMS_STANDARD                       PKG      0     0    44 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_STATS_ADVISOR                  PBDY     0     0   167 NO
SYS          DBMS_SYS_ERROR                      PBDY     0     0     8 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_PDB                            PBDY     0     0    12 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO
22 rows selected.
SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name

為了將一個遊標儲存在共享池中，從v$sql,v$sqlarea或者v$open_cursor中收集它的地址與雜湊值。下面的程式碼顯示地址(6877c238)和雜湊值(1356456286)在它們之間使用逗號進行連線作為一個引數輸入，第二個引數是C，因為我們要儲存一個遊標。對於儲存觸發器引數為T，對於序列，使用Q，對於包，過程與函式，引數為P。

SQL> exec dbms_shared_pool.keep('6877C238,1356456286','C');
PL/SQL procedure successfully completed.

上面的程式碼片段可以用於程式設計結構，但是大多數人發現下面的選項最容易使用。下面的程式碼用來儲存jy方案中的TuoMi過程。

SQL> exec dbms_shared_pool.keep('jy.TuoMi');
PL/SQL procedure successfully completed.

最後，在發出上述程式碼片段之後，您可以輕鬆地進行檢查，以確保確實儲存了物件。從下面的輸出結果可以看到jy.TUOMI過程物件的Kept列被設定為YES。

SQL> @dboc 0 0
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 0
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 0
DB/Inst: jy/jy2                                                   07-May 08:48am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary
                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
..............
SYS          DBMS_SQLTUNE_INTERNAL               PKG      0     0    71 NO
JY           TUOMI                               PRC      0     0    36 YES
SYS          DBMS_SMB_INTERNAL                   PBDY     0     0    32 NO
SYS          DBMS_SQLTUNE                        PKG      0     0    32 NO
..............
99 rows selected.

經常有人問我，多長時間修改一次固定列表。就我個人而言，除非有很好的理由，否則我不喜歡呼叫任何資料庫更改。改進固定列表的一個很好的理由是，如果系統突然開始出現共享池latch爭用，或者遇到4031個錯誤。這一點非常重要:如果新增了應用程式功能、發生了應用程式升級或應用程式使用發生了顯著變化，則從更主動的角度細化固定列表。

清空共享池
雖然不在任何列表的最上面，但是隻要重新整理共享池就可以立即緩解共享池latch爭用。對於oracle9i之前的系統尤其如此，那時還不存在子池。這很明顯不是一個最優解決方案，因為每個物件都沒有固定在共享池中將被刪除並且它們的記憶體會被回收。初始結果可能會適得其反，因為它可能會導致立即進行大量的硬解析，正如我們所知，這會消耗大量CPU資源，並強制執行非自然數量的鎖。然而，這種不幸的情況很快就會平息下來。

有時，共享池大小的組合，資料庫版本(Oracle 9i)與應用程式的使用將使DBA別無選擇，只能計劃定期共享池重新整理。這就是現實情況。

如下面的程式碼片段所示，重新整理共享池非常簡單，但效果確實顯著

SQL> alter system flush shared_pool;
System altered.

增加子池數量
最簡單、最強大和最合適的共享池latch解決方案之一是簡單地新增子池，增加子池也將增加共享池latch。前面的“從雜湊到子池”小節詳細介紹了這個過程。這個特別的解決方案非常簡潔，因為它只需要很少的工作，而且我們不是在玩弄Oracle的共享池LRU演算法。但是，請記住，更多的子池可能需要更多的共享池記憶體，需要重新啟動例項才能使例項引數更改生效，Oracle保留不尊重您的建議的權利。

減少共享池大小
這聽起來可能很奇怪，在子池存在之前，增加共享池大小最終可能導致共享池latch爭用。每一種演算法的效能都是有限的，都是針對特定情況而設計的。當情況發生變化時，演算法可能無法按預期執行。不要忘記，增加快取來支援更多的活動幾乎總是需要更多的CPU資源來管理。因此，可能會有一個收益遞減點。Oracle最初的共享池記憶體管理演算法在大約600MB的共享池中執行得相當好，但是當它達到750MB左右時，dba開始看到大量的共享池latch爭用是非常常見的。一旦引入了子池，特別是與我概述的其他解決方案相結合，共享池latch爭用就可以成功地解決。

4031錯誤解決方案
Oracle在決定什麼時候放棄，什麼時候繼續使用CPU和保持latches之間有一個微妙的平衡。多年來，Oracle耗盡共享池記憶體的可能性已經降低，但是4031錯誤的機率仍然高度依賴於Oracle共享池記憶體的數量和應用程式。下面是一個實際的4031錯誤訊息

ORA-04031: unable to allocate 4192 bytes of shared memory ("shared
pool","SELECT * F...","sql area (6,0)","kafco :
qkacol"):4031:375:2008:ocicon.c

上面的資訊顯示，4KB記憶體正嘗試在子池6中進行分配，但是由於某些原因，不能完成分配。幸運地是有一些方法來減少收到4031錯誤的機會。

清空共享池
與解決共享池latch爭用一樣，4031錯誤的一個解決方案也是清空共享池。雖然沒有DBA願意承認定期清空共享池，但這仍然有效。根據Oracle版本、分配的共享池記憶體的數量以及應用程式獨特的記憶體使用模式，這可能是您的最佳選擇。對於oracle 9i之前的系統尤其如此。

增加共享池大小
從概念上講，增加共享池記憶體為Oracle提供了更大的靈活性來滿足記憶體請求。然而，除了好處之外，在轉移計算資源時也總是有成本的。在大多數情況下，收益實際上大於成本，因此，如果作業系統有可用記憶體，或者可以將記憶體從其他Oracle快取轉移到共享池，增加共享池記憶體很可能減少4031個錯誤。

請記住，每當您要求Oracle管理更多記憶體時，都需要更多的CPU來管理這些記憶體。在oracle9i之前的系統中尤其如此，因為可能存在非常長的記憶體鏈堆。如果鏈有數千個塊長，而4031個錯誤可能會消失，那麼在試圖獲取共享池latch和掃描長鏈時，這種情況可能會表現為嚴重的共享池latch爭用和大量CPU消耗——所以要小心。

如Oracle文件所述，如果您透過自動記憶體管理獲得解放，您可能需要設定最小的共享池大小。在增加緩衝區快取的過程中，Oracle會自動減少共享池的大小，以至於開始出現4031個錯誤。

增加共享池保留大小
當一個較大的包最初被載入到一個已經非常活躍的共享池中時，就會出現一個常見的記憶體分配挑戰。共享池越活躍，特別是當它很小並且物件大小非常不同時，就越有可能找不到所需的記憶體。

假設我們的伺服器程式需要記憶體來儲存一個大遊標。當Oracle搜尋共享池記憶體時，如果物件大小大於閾值，Oracle首先搜尋保留區域。如果在保留區域中沒有找到記憶體，Oracle將到非保留區域搜尋。這種策略有助於將較小的物件排除在保留區域之外，從而將其保留給較大的物件。

有三個例項引數可以組合使用:
.shared_pool_reserved_size被用來直接設定共享池保留大小以位元組為單位
.隱藏引數_shared_pool_reserved_pct，它的預設值為5(5%)，可以被用來代替shared_pool_reserved_size。
.一個相對大的物件是由例項引數_shared_pool_reserved_min_alloc來定義的，它的預設值為4400位元組。有趣的是預設值4400位元組僅僅比常見的單個塊請求4096位元組大。因此，在預設情況下，Oracle表示任何大於一個典型大小的記憶體塊請求都被認為是大的，因此應該從保留的大小中獲得記憶體。

前兩個引數中的任何一個都可以用來為相對較大的物件設定共享池保留記憶體大小。如果您設定其中一個引數，Oracle將計算另一個引數。透過仔細調整這些引數，效能分析人員可以增加程式找到大量記憶體的可能性，同時仍然維護大量記憶體給相對較小物件使用。雖然這些引數通常不會調整，但如果發生4031錯誤，它們的小心調整可能會修復問題。

最小化遊標固定時間
當執行遊標時，遊標也被固定。畢竟，您不希望SQL語句在執行期間突然消失!這是好訊息。潛在的壞訊息是，當執行完成時，固定遊標被釋放。如果沒有其他程式固定遊標，Oracle可以隨意銷燬，即釋放關聯的記憶體。現在假設有人想重新執行遊標。如果它已被釋放，將執行硬解析，因為整個遊標將被重建!每個應用程式使用模式都是獨特的;因此，當與更小的共享池或許多獨特的SQL語句(或者兩者都有)結合使用時，記憶體管理和庫快取活動可能會變得異常緊張。減少硬解析的一種方法是固定遊標，使它們不能被釋放。

Oracle提供了一個特殊的例項引數，該引數將保持所有會話的所有遊標固定到關閉遊標為止。但是，這種好處是以增加共享池記憶體消耗為代價的，因此，增加了接收4031錯誤的可能性。Oracle非常清楚這一點，所以為了鼓勵回收釋放記憶體並降低發生4031個錯誤的可能性，cursor_space_for_time例項引數預設設定為false。

如果系統正經歷4031錯誤，你應該要檢查cursor_space_for_time引數值。如果你的系統在過去某個時間點已經經歷了嚴重的共享池latch急用，那麼可以理解有人將cursor_space_for_time設定為true了。雖然你可能不會決定設定cursor_space_for_time引數為false。但這是一個有效選項應該被考慮。

減小保留物件的記憶體消耗
如果有太多物件透過執行dbms_shared_pool.keep過程被強制保留在共享池中，它們可能會消耗大量的記憶體Oracle可能無法成功地管理剩下的記憶體。此外，如果沒有將大型物件儲存在共享池中，則例項已經執行了一段時間，然後引用該物件，當強制載入該物件時，記憶體可能不可用。關鍵是不要隨意地將物件儲存在共享池中。

升級資料庫版本到10gr2
當然，4031個錯誤不是升級的惟一原因，而是從Oracle資料庫10gr2開始將記憶體標準化為4KB塊。雖然我永遠不會僅僅因為這個改進就建議升級到這個版本，但是這可能是升級的一部分原因。

就像對段區大小進行標準化一樣，擁有標準的記憶體塊大小可以提高快速找到合適記憶體的可能性。可以找到的記憶體越快，消耗的CPU週期就越少，必須持有共享池latch的時間就越短，存在大量浪費的小記憶體塊的可能性就越小(增加4031錯誤的可能性)。