老熊：http://www.laoxiong.net/an-ora-04031-case.html

https://blog.csdn.net/h354541060/article/details/48530303

一：ORA-04031 的原因分析解決方法

下面分析了幾個可能及解決方法

基本上，ORA-04031出現的問題有幾個可能性
A. 沒有綁定編量造成shared_pool碎片過多，同時shared_pool_size太小.

--這個應該是比較常見的，也是Oracle提的最多的。 
--這個通常會建議使用綁定變量，或者簡單的加大shared_pool.或者臨時解決方法就是alter system flush shared_pool.

http://www.dbanotes.net/Oracle/Ora-04031.htm

B. Large_pool,Java_pool太小造成的

--這個通過錯誤信息的提示很容易判斷(Ora-04031 cannot allocate .. memeory in [large_pool])
--解決方法就是簡單的加大 Large_pool or Java_pool

C. 過度的開CURSOR而不關閉。

--這個問題發生的越來越多，特別是在JAVA運行環境中，頻頻出現。加大Shared_pool或者flush shared_pool往往只能延遲問題出現的時間，而沒法避免。
--判斷方法：
select count(*) from v$open_cursor ;
select * from v$sysstat 
where name = 'opened cursors current'; 
如果出來的值特大（以萬為單位）時，基本就可以確定是這個原因了 
--解決這個問題的方法就是檢查程序，看是否沒有正常的關閉cursor(對于JAVA來說，就是沒有關閉Statement)。或者select sql_text from v$open_cursor,看看都是哪些cursor沒關閉，再去檢查車程序。 
--也有的程序使用了保持一定量的cursor一直open,從而避免cursor過多次的開啟，來提高性能。對于這種情況，則應該選擇適當的shared_pool_size和控制keep_opening的cursor的量。
--也有可能Oracle參數session_cached_cursors太大，解決方法就是把它降低到適當的值


二、診斷并解決ORA-04031 錯誤

當我們在共享池中試圖分配大片的連續內存失敗的時候，Oracle首先清除池中當前沒使用的所有對象，使空閑內存塊合并。如果仍然沒有足夠大單個的大塊內存滿足請求,就會產生ORA-04031 錯誤。

        當這個錯誤出現的時候你得到的錯誤解釋信息類似如下：

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"   
// *Cause: More shared memory is needed than was allocated in the shared   
// pool.   
// *Action: If the shared pool is out of memory, either use the   
// dbms_shared_pool package to pin large packages,   
// reduce your use of shared memory, or increase the amount of   
// available shared memory by increasing the value of the   
// INIT.ORA parameters "shared_pool_reserved_size" and   
// "shared_pool_size".   
// If the large pool is out of memory, increase the INIT.ORA   
// parameter "large_pool_size".  

1.共享池相關的實例參數
        在繼續之前，有必要理解下面的實例參數:

        a.SHARED_POOL_SIZE 
        這個參數指定了共享池的大小，單位是字節。可以接受數字值或者數字后面跟上后綴"K" 或 "M" 。"K"代表千字節, "M"代表兆字節。
 
        b.SHARED_POOL_RESERVED_SIZE 
        指定了為共享池內存保留的用于大的連續請求的共享池空間。當共享池碎片強制使 Oracle 查找并釋放大塊未使用的池來滿足當前的請求的時候，這個參數和SHARED_POOL_RESERVED_MIN_ALLOC 參數一起可以用來避免性能下降。 

        c.這個參數理想的值應該大到足以滿足任何對保留列表中內存的請求掃描而無需從共享池中刷新對象。既然操作系統內存可以限制共享池的大小，一般來說，你應該設定這個參數為 SHARED_POOL_SIZE 參數的 10% 大小。 

        d.SHARED_POOL_RESERVED_MIN_ALLOC 這個參數的值控制保留內存的分配。如果一個足夠尺寸的大塊內存在共享池空閑列表中沒能找到，內存就從保留列表中分配一塊比這個值大的空間。默認的值對于大多數系統來說都足夠了。如果你加大這個值，那么Oracle 服務器將允許從這個保留列表中更少的分配并且將從共享池列表中請求更多的內存。這個參數在Oracle 8i 和更高的版本中是隱藏的。提交如下的語句查找這個參數值：
     SELECT   nam.ksppinm NAME, val.ksppstvl VALUE  
        FROM x$ksppi nam, x$ksppsv val  
       WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'  
    ORDER BY 1; 


10g 注釋：Oracle 10g 的一個新特性叫做 "自動內存管理" 允許DBA保留一個共享內存池來分shared pool,buffer cache, java pool 和large pool。一般來說，當數據庫需要分配一個大的對象到共享池中并且不能找到連續的可用空間，將自動使用其他SGA結構的空閑空間來增加共享池的大小 。既然空間分配是Oracle自動管理的，ora-4031出錯的可能性將大大降低。自動內存管理在初始化參數SGA_TARGET大于0的時候被激活。當前設定可以通過查詢v$sga_dynamic_components 視圖獲得。請參考10g管理手冊以得到更多內容。

 倫理電影 http://www.dotdy.com/

2.診斷ORA-04031 錯誤 
        注：大多數的常見的 ORA-4031 的產生都和 SHARED POOL SIZE 有關，這篇文章中的診斷步驟大多都是關于共享池的。　對于其它方面如Large_pool或是Java_pool，內存分配算法都是相似的，一般來說都是因為結構不夠大造成。

        ORA-04031 可能是因為 SHARED POOL 不夠大，或是因為碎片問題導致數據庫不能找到足夠大的內存塊。

        ORA-04031 錯誤通常是因為庫高速緩沖中或共享池保留空間中的碎片。 在加大共享池大小的時 候考慮調整應用,使用共享的SQL 并且調整如下的參數： 
        SHARED_POOL_SIZE,
        SHARED_POOL_RESERVED_SIZE,
        SHARED_POOL_RESERVED_MIN_ALLOC
首先判定是否ORA-04031 錯誤是由共享池保留空間中的庫高速緩沖的碎片產生的。提交下的查詢：

     SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,  
           last_failure_size  
      FROM v$shared_pool_reserved; 

        如果：
        REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC 
那么ORA-04031 錯誤就是因為共享池保留空間缺少連續空間所致。要解決這個問題,可以考慮加大SHARED_POOL_RESERVED_MIN_ALLOC 來降低緩沖進共 享池保留空間的對象數目，并增大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 來加大共享池保留空間的可用內存。

        如果： 
        REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 
        或者 
        REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 
        那么是因為在庫高速緩沖缺少連續空間導致ORA-04031 錯誤。

        第一步應該考慮降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的對象到共享池保留空間中并且加大SHARED_POOL_SIZE。

 

3.解決ORA-04031 錯誤
1).ORACLE BUG 
        Oracle推薦對你的系統打上最新的PatchSet。大多數的ORA-04031錯誤都和BUG 相關，可以通過使用這些補丁來避免。

        下面表中總結和和這個錯誤相關的最常見的BUG、可能的環境和修補這個問題的補丁。 


2).編譯Java代碼時出現的ORA-4031 
        在你編譯Java代碼的時候如果內存溢出，你會看到錯誤：
     A SQL exception occurred while compiling: :   
    ORA-04031: unable to allocate bytes of shared memory   
    ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")  

解決辦法是關閉數據庫然后把參數 JAVA_POOL_SIZE 設定為一個較大的值。這里錯誤信息中提到的 "shared pool" 其實共享全局區(SGA)溢出的誤導，并不表示你需要增加SHARED_POOL_SIZE，相反，你必須加大 JAVA_POOL_SIZE 參數的值，然后重啟動系統，再試一下。參考: <Bug:2736601> 。

3).小的共享池尺寸 
        很多情況下，共享池過小能夠導致ORA-04031錯誤。下面信息有助于你調整共享池大小：

        a.庫高速緩沖命中率 
        命中率有助于你衡量共享池的使用，有多少語句需要被解析而不是重用。下面的SQL語句有助于你計算庫高速緩沖的命中率：
     SELECT SUM(PINS) "EXECUTIONS",   
                SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"   
                FROM V$LIBRARYCACHE;  

        b.共享池大小計算 
        要計算最適合你工作負載的共享池大小，請參考：

4).共享池碎片 
        每一次，需要被執行的SQL 或者PL/SQL 語句的解析形式載入共享池中都需要一塊特定的連續的空間。數據庫要掃描的第一個資源就是共享池中的空閑可用內存。一旦空閑內存耗盡，數據庫要查找一塊已經分配但還沒使用的內存準備重用。如果這樣的確切尺寸的大塊內存不可用，就繼續按照如下標準尋找：

        i.大塊(chunk)大小比請求的大小大
        ii.空間是連續的
        iii.大塊內存是可用的(而不是正在使用的)
        這樣大塊的內存被分開，剩余的添加到相應的空閑空間列表中。當數據庫以這種方式操作一段時間之后，共享池結構就會出現碎片。

        當共享池存在碎片的問題,分配一片空閑的空間就會花費更多的時間,數據庫性能也會下降(整個操作的過程中，"chunk allocation"被一個叫做"shared pool latch" 的閂所控制) 或者是出現 ORA-04031 錯誤errors (在數據庫不能找到一個連續的空閑內存塊的時候)。

        如果SHARED_POOL_SIZE 足夠大，大多數的 ORA-04031 錯誤都是由共享池中的動態SQL 碎片導致的。可能的原因如下： 
        i.非共享的SQL 
        ii.生成不必要的解析調用 (軟解析)
        iii.沒有使用綁定變量
        要減少碎片的產生你需要確定是前面描敘的幾種可能的因素。可以采取如下的一些方法，當然不只局限于這幾種: 應用調整、數據庫調整或者實例參數調整。

        下面的視圖有助于你標明共享池中非共享的SQL/PLSQL： 
        a.V$SQLAREA 視圖 
        這個視圖保存了在數據庫中執行的SQL 語句和PL/SQL 塊的信息。下面的SQL 語句可以顯示給你帶有literal 的語句或者是帶有綁定變量的語句：
     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; 

注: Having 后的數值 "30" 可以根據需要調整以得到更為詳細的信息。

        b.X$KSMLRU 視圖 
        這個固定表x$ksmlru 跟蹤共享池中導致其它對象換出(age out)的應用。這個固定表可以用來標記是什么導致了大的應用。

        如果很多對象在共享池中都被階段性的刷新可能導致響應時間問題并且有可能在對象重載入共享池中的時候導致庫高速緩沖閂競爭問題。

        關于這個x$ksmlru 表的一個不尋常的地方就是如果有人從表中選取內容這個表的內容就會被擦除。這樣這個固定表只存儲曾經發生的最大的分配。這個值在選擇后被重新設定這樣接下來的大的分配可以被標記，即使它們不如先前的分配過的大。因為這樣的重置，在查詢提交后的結果不可以再次得到，從表中的輸出的結果應該小心的保存。監視這個固定表運行如下操作：
    SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

這個表只可以用SYS用戶登錄進行查詢。

        c.X$KSMSP 視圖 (類似堆Heapdump信息) 
        使用這個視圖能找出當前分配的空閑空間，有助于理解共享池碎片的程度。如我們在前面的描述，查找為游標分配的足夠的大塊內存的第一個地方是空閑列表( free list)。 下面的語句顯示了空閑列表中的大塊內存：
     SELECT   '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",  
             COUNT (*) "Count", MAX (ksmchsiz) "Biggest",  
             TRUNC (AVG (ksmchsiz)) "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"  
        FROM x$ksmsp  
       WHERE ksmchsiz < 140 AND ksmchcls = 'free'  
    GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)  
    UNION ALL  
    SELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz / 20),  
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
             TRUNC (SUM (ksmchsiz)) "Total"  
        FROM x$ksmsp  
       WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'  
    GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)  
    UNION ALL  
    SELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz / 50),  
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
             TRUNC (SUM (ksmchsiz)) "Total"  
        FROM x$ksmsp  
       WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'  
    GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)  
    UNION ALL  
    SELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz / 500),  
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
             TRUNC (SUM (ksmchsiz)) "Total"  
        FROM x$ksmsp  
       WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'  
    GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)  
    UNION ALL  
    SELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz / 1000),  
             COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) "AvgSize",  
             TRUNC (SUM (ksmchsiz)) "Total"  
        FROM x$ksmsp  
       WHERE ksmchsiz >= 4108 AND ksmchcls = 'free'  
    GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000); 

4.ORA-04031 錯誤與 Large Pool 
        大池是個可選的內存區，為以下的操作提供大內存分配：

        i.MTS會話內存和 Oracle XA 接口 
        ii.Oracle 備份與恢復操作和I/O服務器進程用的內存(緩沖)
        iii.并行執行消息緩沖 
        大池沒有LRU列表。這和共享池中的保留空間不同，保留空間和共享池中其他分配的內存使用同樣的LRU列表。大塊內存從不會換出大池中，內存必須是顯式的被每個會話分配并釋放。一個請求如果沒有足夠的內存，就會產生類似這樣的一個ORA-4031錯誤：
     ORA-04031: unable to allocate XXXX bytes of shared memory   
    ("large pool","unknown object","session heap","frame")  

這個錯誤發生時候可以檢查幾件事情： 
        a.使用如下語句檢查 V$SGASTAT ，得知使用和空閑的內存：
SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';  

b.你還可以采用 heapdump level 32 來 dump 大池的堆并檢查空閑的大塊內存的大小
        從大池分配的內存如果是LARGE_POOL_MIN_ALLOC 子節的整塊數有助于避免碎片。任何請求分配小于LARGE_POOL_MIN_ALLOC 大塊尺寸都將分配LARGE_POOL_MIN_ALLOC的大小。一般來說，你會看到使用大池的時候相對共享池來說要用到更多的內存。通常要解決大池中的ORA-4031錯誤必須增加 LARGE_POOL_SIZE 的大小。

5. ORA-04031 和共享池刷新
        有一些技巧會提高游標的共享能力，從而共享池碎片和ORA-4031都會減少。最佳途徑是調整應用使用綁定變量。另外在應用不能調整的時候考慮使用CURSOR_SHARING參數和FORCE不同的值來做到 (要注意那會導致執行計劃改變，所以建議先對應用進行測試)。當上述技巧都不可以用的時候，并且碎片問題在系統中比較嚴重，刷新共享持可能有助于減輕碎片問題。但是，必須加以如下考慮： 
        a.刷新將導致所有沒被使用的游標從共享池刪除。這樣，在共享池刷新之后，大多數SQL和PL/SQL游標必須被硬解析。這將提高CPU的使用，也會加大Latch的活動。 
        b.當應用程序沒有使用綁定變量并被許多用戶進行類似的操作的時候(如在OLTP系統中) ，刷新之后很快還會出現碎片問題。所以共享池對設計糟糕的應用程序來說不是解決辦法。 
        c.對一個大的共享池刷新可能會導致系統掛起，尤其是實例繁忙的時候，推薦在非高峰的時候刷新

 

6. ORA-04031錯誤的高級分析
        如果前述的這些技術內容都不能解決ORA-04031 錯誤，可能需要額外的跟蹤信息來得到問題發生的共享池的快照。 
        調整init.ora參數添加如下的事件得到該問題的跟蹤信息：

event = "4031 trace name errorstack level 3"   
event = "4031 trace name HEAPDUMP level 3"

如果問題可重現，該事件可設定在會話層，在執行問題語句之前使用如下的語句：
alter session set events '4031 trace name errorstack level 3';   
 alter session set events '4031 trace name HEAPDUMP level 3'; 

把這個跟蹤文件發給Oracle支持人員進行排錯。 
        重要標注: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在發生ORA-4031 錯誤的時候會自動創建一個跟蹤文件，可以在user_dump_dest 目錄中找到。如果你的系統是上述的版本，你不需要再進行前面描述中的步驟。