|
一：錯誤總述 1． ORA-04031 基本上,，ORA-04031出現(xiàn)的問題有幾個可能性 A. 沒有綁定編量造成shared_pool碎片過多，同時shared_pool_size太小. --這個應該是比較常見的,，也是Oracle提的最多的,。 --這個通常會建議使用綁定變量，或者簡單的加大shared_pool.或者臨時解決方法就是alter system flush shared_pool. B. Large_pool,Java_pool太小造成的 --這個通過錯誤信息的提示很容易判斷(Ora-04031 cannot allocate .. memeory in [large_pool]) --解決方法就是簡單的加大 Large_pool or Java_pool C. 過度的開CURSOR而不關(guān)閉,。 --這個問題發(fā)生的越來越多,，特別是在JAVA運行環(huán)境中，頻頻出現(xiàn),。加大Shared_pool或者flush shared_pool往往只能延遲問題出現(xiàn)的時間,，而沒法避免。 --判斷方法： select count(*) from v$open_cursor ; select * from v$sysstat where name = 'opened cursors current'; 如果出來的值特大（以萬為單位）時,，基本就可以確定是這個原因了 --解決這個問題的方法就是檢查程序,，看是否沒有正常的關(guān)閉cursor(對于JAVA來說，就是沒有關(guān)閉Statement),?；蛘遱elect sql_text from v$open_cursor,看看都是哪些cursor沒關(guān)閉，再去檢查車程序,。 --也有的程序使用了保持一定量的cursor一直open,從而避免cursor過多次的開啟,，來提高性能,。對于這種情況，則應該選擇適當?shù)膕hared_pool_size和控制keep_opening的cursor的量,。 --也有可能Oracle參數(shù)session_cached_cursors太大,，解決方法就是把它降低到適當?shù)闹?nbsp; 樓主的問題似乎有點象 session_cached_cursors 的問題，但是根據(jù) opened cursors current判斷,，每個session開的cursor超過1000,，已經(jīng)超過session_cached_cursors，應該檢查程序看看,。 D. 當然,，有時候一些BUG也可能引發(fā)ORA-04031,但是在高版本中已經(jīng)很少出現(xiàn)(>=8174) 2。ORA-04030 ORA-04030出現(xiàn)的基本都是過多的使用memory造成的 ,。 ORA-04030的問題一般是PGA過度分配造成的（對應的操作是sort/hash_join）,。在Oracle9i中pga_aggregate_target指定了所有session總共使用的最大PGA上限，如果該值被設定了則默認的workarea_size_policy=auto, sort_area_size/sort_area_retained_size將被忽略,。那么直接減小pga_aggregate_target就能解決一部分ORA-04030問題,。 A. 對于32 BIT系統(tǒng)，有SGA 1.7G限制 B. 某些OS系統(tǒng)本身也有一些內(nèi)存參數(shù)限制 --運行 ulimit 看看 C. OS系統(tǒng)本身物理內(nèi)存+Swap的限制 我們應該檢查DB使用的 SGA + PGA 是否超過 上面的限制 SGA 包括 db_cache,shared_pool,large_pool,java_pool session的PGA包括 sort_area_size/Hash_area_size/*_area_size 或者 pga_aggregate_target 還有執(zhí)行的CODE以及一些data也會占用空間,。 然后再根據(jù)情況降低里面的某些值了,，比如 db_cache， sort_area_size 等 對于樓主來說,，應該是sort_area_size（200M）/Hash_area_size(400M) 太大造成的,，降成幾十M或者幾M 就可以了。 二：診斷并解決ORA-04031 錯誤 當我們在共享池中試圖分配大片的連續(xù)內(nèi)存失敗的時候,，Oracle首先清除池中當前沒使用的所有對象,，使空閑內(nèi)存塊合并。如果仍然沒有足夠大單個的大塊內(nèi)存滿足請求,就會產(chǎn)生ORA-04031 錯誤,。 當這個錯誤出現(xiàn)的時候你得到的錯誤解釋信息類似如下: 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.共享池相關(guān)的實例參數(shù) 在繼續(xù)之前,，有必要理解下面的實例參數(shù): SHARED_POOL_SIZE 這個參數(shù)指定了共享池的大小，單位是字節(jié),?？梢越邮軘?shù)字值或者數(shù)字后面跟上后綴"K" 或 "M" 。"K"代表千字節(jié), "M"代表兆字節(jié),。 SHARED_POOL_RESERVED_SIZE 指定了為共享池內(nèi)存保留的用于大的連續(xù)請求的共享池空間,。當共享池碎片強制使 Oracle 查找并釋放大塊未使用的池來滿足當前的請求的時候，這個參數(shù)和SHARED_POOL_RESERVED_MIN_ALLOC 參數(shù)一起可以用來避免性能下降,。 這個參數(shù)理想的值應該大到足以滿足任何對保留列表中內(nèi)存的請求掃描而無需從共享池中刷新對象。既然操作系統(tǒng)內(nèi)存可以限制共享池的大小,，一般來說,，你應該設定這個參數(shù)為 SHARED_POOL_SIZE 參數(shù)的 10% 大小,。 SHARED_POOL_RESERVED_MIN_ALLOC 這個參數(shù)的值控制保留內(nèi)存的分配。如果一個足夠尺寸的大塊內(nèi)存在共享池空閑列表中沒能找到,，內(nèi)存就從保留列表中分配一塊比這個值大的空間,。默認的值對于大多數(shù)系統(tǒng)來說都足夠了。如果你加大這個值,，那么Oracle 服務器將允許從這個保留列表中更少的分配并且將從共享池列表中請求更多的內(nèi)存,。這個參數(shù)在Oracle 8i 和更高的版本中是隱藏的。提交如下的語句查找這個參數(shù)值: 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 的一個新特性叫做 "自動內(nèi)存管理" 允許DBA保留一個共享內(nèi)存池來分shared pool,buffer cache, java pool 和large pool,。一般來說,，當數(shù)據(jù)庫需要分配一個大的對象到共享池中并且不能找到連續(xù)的可用空間，將自動使用其他SGA結(jié)構(gòu)的空閑空間來增加共享池的大小 ,。既然空間分配是Oracle自動管理的,，ora-4031出錯的可能性將大大降低。自動內(nèi)存管理在初始化參數(shù)SGA_TARGET大于0的時候被激活,。當前設定可以通過查詢v$sga_dynamic_components 視圖獲得,。請參考10g管理手冊以得到更多內(nèi)容 。 2.診斷ORA-04031 錯誤 注：大多數(shù)的常見的 ORA-4031 的產(chǎn)生都和 SHARED POOL SIZE 有關(guān),，這篇文章中的診斷步驟大多都是關(guān)于共享池的,。 對于其它方面如Large_pool或是Java_pool,，內(nèi)存分配算法都是相似的,，一般來說都是因為結(jié)構(gòu)不夠大造成。 ORA-04031 可能是因為 SHARED POOL 不夠大,，或是因為碎片問題導致數(shù)據(jù)庫不能找到足夠大的內(nèi)存塊,。 ORA-04031 錯誤通常是因為庫高速緩沖中或共享池保留空間中的碎片。 在加大共享池大小的時 候考慮調(diào)整應用,使用共享的SQL 并且調(diào)整如下的參數(shù)： SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, SHARED_POOL_RESERVED_MIN_ALLOC. 首先判定是否ORA-04031 錯誤是由共享池保留空間中的庫高速緩沖的碎片產(chǎn)生的,。提交下的查詢： 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 錯誤就是因為共享池保留空間缺少連續(xù)空間所致,。要解決這個問題,可以考慮加大SHARED_POOL_RESERVED_MIN_ALLOC 來降低緩沖進共 享池保留空間的對象數(shù)目，并增大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 來加大共享池保留空間的可用內(nèi)存,。 如果： REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 或者 REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 那么是因為在庫高速緩沖缺少連續(xù)空間導致ORA-04031 錯誤,。 第一步應該考慮降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的對象到共享池保留空間中并且加大SHARED_POOL_SIZE。 3.解決ORA-04031 錯誤 ORACLE BUG Oracle推薦對你的系統(tǒng)打上最新的PatchSet,。大多數(shù)的ORA-04031錯誤都和BUG 相關(guān),，可以通過使用這些補丁來避免。 下面表中總結(jié)和和這個錯誤相關(guān)的最常見的BUG,、可能的環(huán)境和修補這個問題的補丁,。 BUG 描述 Workaround Fixed <Bug:1397603> ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/ 8172 <Bug:1640583> ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171/901 <Bug:1318267> INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0 8171/8200 <Bug:1193003> Cursors may not be shared in 8.1 when they should be Not available 8162/8170/ 901 <Bug:2104071> ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS) None-> This is known to affect the XML parser. 8174, 9013, 9201 <Note:263791.1> Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset Not available 9205 · 編譯Java代碼時出現(xiàn)的ORA-4031 在你編譯Java代碼的時候如果內(nèi)存溢出，你會看到錯誤： 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") 解決辦法是關(guān)閉數(shù)據(jù)庫然后把參數(shù) JAVA_POOL_SIZE 設定為一個較大的值。這里錯誤信息中提到的 "shared pool" 其實共享全局區(qū)(SGA)溢出的誤導,，并不表示你需要增加SHARED_POOL_SIZE,，相反，你必須加大 JAVA_POOL_SIZE 參數(shù)的值,，然后重啟動系統(tǒng),，再試一下。參考: <Bug:2736601> ,。 · 小的共享池尺寸 很多情況下,，共享池過小能夠?qū)е翺RA-04031錯誤。下面信息有助于你調(diào)整共享池大?。?/div> 庫高速緩沖命中率 命中率有助于你衡量共享池的使用,，有多少語句需要被解析而不是重用。下面的SQL語句有助于你計算庫高速緩沖的命中率： SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 如果丟失超過1%,那么嘗試通過加大共享池的大小來減少庫高速緩沖丟失,。 共享池大小計算 要計算最適合你工作負載的共享池大小,，請參考： <Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE. · 共享池碎片 每一次，需要被執(zhí)行的SQL 或者PL/SQL 語句的解析形式載入共享池中都需要一塊特定的連續(xù)的空間,。數(shù)據(jù)庫要掃描的第一個資源就是共享池中的空閑可用內(nèi)存,。一旦空閑內(nèi)存耗盡，數(shù)據(jù)庫要查找一塊已經(jīng)分配但還沒使用的內(nèi)存準備重用,。如果這樣的確切尺寸的大塊內(nèi)存不可用,，就繼續(xù)按照如下標準尋找： IXDBA.NET社區(qū)論壇 § 大塊(chunk)大小比請求的大小大 § 空間是連續(xù)的 § 大塊內(nèi)存是可用的(而不是正在使用的) 這樣大塊的內(nèi)存被分開，剩余的添加到相應的空閑空間列表中,。當數(shù)據(jù)庫以這種方式操作一段時間之后,，共享池結(jié)構(gòu)就會出現(xiàn)碎片。 當共享池存在碎片的問題,分配一片空閑的空間就會花費更多的時間,數(shù)據(jù)庫性能也會下降(整個操作的過程中,，"chunk allocation"被一個叫做"shared pool latch" 的閂所控制) 或者是出現(xiàn) ORA-04031 錯誤errors (在數(shù)據(jù)庫不能找到一個連續(xù)的空閑內(nèi)存塊的時候),。 參考 <Note:61623.1>: 可以得到關(guān)于共享池碎片的詳細討論。 如果SHARED_POOL_SIZE 足夠大,，大多數(shù)的 ORA-04031 錯誤都是由共享池中的動態(tài)SQL 碎片導致的,。可能的原因如下： § 非共享的SQL § 生成不必要的解析調(diào)用 (軟解析) § 沒有使用綁定變量 要減少碎片的產(chǎn)生你需要確定是前面描敘的幾種可能的因素,?？梢圆扇∪缦碌囊恍┓椒ǎ斎徊恢痪窒抻谶@幾種: 應用調(diào)整,、數(shù)據(jù)庫調(diào)整或者實例參數(shù)調(diào)整,。 請參考 <Note:62143.1>，描述了所有的這些細節(jié)內(nèi)容,。這個注釋還包括了共享池如何工作的細節(jié),。 下面的視圖有助于你標明共享池中非共享的SQL/PLSQL： § V$SQLAREA 視圖 這個視圖保存了在數(shù)據(jù)庫中執(zhí)行的SQL 語句和PL/SQL 塊的信息,。下面的SQL 語句可以顯示給你帶有l(wèi)iteral 的語句或者是帶有綁定變量的語句： 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 后的數(shù)值 "30" 可以根據(jù)需要調(diào)整以得到更為詳細的信息。 § X$KSMLRU 視圖 這個固定表x$ksmlru 跟蹤共享池中導致其它對象換出(age out)的應用,。這個固定表可以用來標記是什么導致了大的應用,。 如果很多對象在共享池中都被階段性的刷新可能導致響應時間問題并且有可能在對象重載入共享池中的時候?qū)е聨旄咚倬彌_閂競爭問題。 關(guān)于這個x$ksmlru 表的一個不尋常的地方就是如果有人從表中選取內(nèi)容這個表的內(nèi)容就會被擦除,。這樣這個固定表只存儲曾經(jīng)發(fā)生的最大的分配。這個值在選擇后被重新設定這樣接下來的大的分配可以被標記,，即使它們不如先前的分配過的大,。因為這樣的重置，在查詢提交后的結(jié)果不可以再次得到,，從表中的輸出的結(jié)果應該小心的保存,。監(jiān)視這個固定表運行如下操作： SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0; 這個表只可以用SYS用戶登錄進行查詢。 § X$KSMSP 視圖 (類似堆Heapdump信息) 使用這個視圖能找出當前分配的空閑空間,，有助于理解共享池碎片的程度,。如我們在前面的描述，查找為游標分配的足夠的大塊內(nèi)存的第一個地方是空閑列表( free list),。 下面的語句顯示了空閑列表中的大塊內(nèi)存: 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 大池是個可選的內(nèi)存區(qū),，為以下的操作提供大內(nèi)存分配： · MTS會話內(nèi)存和 Oracle XA 接口 · Oracle 備份與恢復操作和I/O服務器進程用的內(nèi)存(緩沖) · 并行執(zhí)行消息緩沖 大池沒有LRU列表。這和共享池中的保留空間不同,，保留空間和共享池中其他分配的內(nèi)存使用同樣的LRU列表,。大塊內(nèi)存從不會換出大池中，內(nèi)存必須是顯式的被每個會話分配并釋放,。一個請求如果沒有足夠的內(nèi)存,，就會產(chǎn)生類似這樣的一個ORA-4031錯誤： ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame") 這個錯誤發(fā)生時候可以檢查幾件事情： · 1- 使用如下語句檢查 V$SGASTAT ，得知使用和空閑的內(nèi)存： · SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool'; · 2- 你還可以采用 heapdump level 32 來 dump 大池的堆并檢查空閑的大塊內(nèi)存的大小 從大池分配的內(nèi)存如果是LARGE_POOL_MIN_ALLOC 子節(jié)的整塊數(shù)有助于避免碎片,。任何請求分配小于LARGE_POOL_MIN_ALLOC 大塊尺寸都將分配LARGE_POOL_MIN_ALLOC的大小,。一般來說，你會看到使用大池的時候相對共享池來說要用到更多的內(nèi)存,。通常要解決大池中的ORA-4031錯誤必須增加 LARGE_POOL_SIZE 的大小,。 5. ORA-04031 和共享池刷新 有一些技巧會提高游標的共享能力，從而共享池碎片和ORA-4031都會減少,。最佳途徑是調(diào)整應用使用綁定變量,。另外在應用不能調(diào)整的時候考慮使用CURSOR_SHARING參數(shù)和FORCE不同的值來做到 (要注意那會導致執(zhí)行計劃改變，所以建議先對應用進行測試),。當上述技巧都不可以用的時候,，并且碎片問題在系統(tǒng)中比較嚴重，刷新共享持可能有助于減輕碎片問題,。但是,，必須加以如下考慮： · 刷新將導致所有沒被使用的游標從共享池刪除。這樣，在共享池刷新之后,，大多數(shù)SQL和PL/SQL游標必須被硬解析,。這將提高CPU的使用，也會加大Latch的活動,。 · 當應用程序沒有使用綁定變量并被許多用戶進行類似的操作的時候(如在OLTP系統(tǒng)中) ,，刷新之后很快還會出現(xiàn)碎片問題。所以共享池對設計糟糕的應用程序來說不是解決辦法,。 · 對一個大的共享池刷新可能會導致系統(tǒng)掛起,，尤其是實例繁忙的時候，推薦在非高峰的時候刷新 6. ORA-04031錯誤的高級分析 如果前述的這些技術(shù)內(nèi)容都不能解決ORA-04031 錯誤,，可能需要額外的跟蹤信息來得到問題發(fā)生的共享池的快照,。 調(diào)整init.ora參數(shù)添加如下的事件得到該問題的跟蹤信息： event = "4031 trace name errorstack level 3" event = "4031 trace name HEAPDUMP level 3" 如果問題可重現(xiàn)，該事件可設定在會話層,，在執(zhí)行問題語句之前使用如下的語句： SQL> alter session set events '4031 trace name errorstack level 3'; SQL> alter session set events '4031 trace name HEAPDUMP level 3'; 把這個跟蹤文件發(fā)給Oracle支持人員進行排錯,。 重要標注: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在發(fā)生ORA-4031 錯誤的時候會自動創(chuàng)建一個跟蹤文件,，可以在user_dump_dest 目錄中找到,。如果你的系統(tǒng)是上述的版本，你不需要再進行前面描述中的步驟,。 |
|
|
