SHARED POOL ORA-04031錯誤分析
一、 SHARED POOL及ORA-4031錯誤
SHARE POOL利用堆（HEAP)的記憶體管理方式管理，記憶體是預先分配的，當某個程式需要分配shared pool的記憶體的時候，Heap Manager就滿足該請求，Heap Manager也和其他ORACLE模組一起工作來回收shared pool的空閒記憶體。
SHARE POOL在物理上由多個記憶體區組成，記憶體區又由多個不同大小的CHUNK組成。而CHUNK又有可重用和空閒之分，並且它們分別有LRU LIST、FREE LIST、RESERVED
LIST串聯起來。，oracle使用一些free list
bucket。它們的free list的結構和每個free
list對應於一個特定的大小。Oracle使用二進位制在free
list搜尋大小，以找到合適的空閒列表。第一bucket是大於或等於所請求的大小將被返回。在資料庫的啟動時，有各種大小的chunk被建立在bucket。我們將繼續掃描bucket，直到我們找到一個bucket，符合要求。共享池將利用最近最少使用（LRU）演算法，該演算法並沒有得到重用的記憶體移除share pool。
Chunk簡單申請過程如下：
如果大於_shared_pool_reserved_min_alloc則在保留池中查詢
二、 ORA-4031 原因診斷及解決
1. shared pool過小
select * from v$sgastat a where a.pool='shared pool'
order by bytes desc
檢視shared_pool 佔用情況及free memory大小
如果free memory很小，則需要調大shared_pool
2. SHARED_POOL_RESERVED_SIZE 太小
SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,
last_failure_size
FROM v$shared_pool_reserved;
如果REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC表明SHARED_POOL_RESERVED不足
解決辦法:
調大SHARED_POOL_RESERVED_SIZE 和增大SHARED_POOL_SIZE
3. Shared Pool Fragmentation:
如果REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC.
而free memory足夠多，表明shared pool碎片嚴重
碎片分析
a 彙總分析
SQL> SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
2 TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY ksmchcls; 3
CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-freea 1164 5210568 4.37k
freeabl 422188 1582668648 3.66k
recr 133486 268457192 1.96k
R-free 182 129025344 692.31k
R-perm 4 66367088 16,202.90k
free 27360 328226088 11.72k
perm 65 388271192 5,833.40k
free
Free chunks--不包含任何物件的chunk,可以不受限制的被分配.
recr
Recreatable chunks--包含可以被臨時移出記憶體的物件,在需要的時候，這個物件可以
被重新建立.例如,許多儲存共享sql程式碼的記憶體都是可以重建的.
freeabl
Freeable chunks--包含session週期或呼叫的物件,隨後可以被釋放.這部分記憶體有時候
可以全部或部分提前釋放.但是注意，由於某些物件是中間過程產生的，這些物件不能
臨時被移出記憶體(因為不可重建).
perm
Permanent memory chunks--包含永久物件.通常不能獨立釋放.
R-開頭的代表shared_pool_reserved_size
b 查詢空閒空間分佈情況
SELECT '0
(<140)' bucket, ksmchcls, ksmchidx, 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, ksmchidx, 10*TRUNC(ksmchsiz/10)
UNION ALL
SELECT '1 (140-267)' bucket, ksmchcls, ksmchidx,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, ksmchidx, 20*TRUNC(ksmchsiz/20)
UNION ALL
SELECT '2 (268-523)' bucket, ksmchcls, ksmchidx, 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, ksmchidx, 50*TRUNC(ksmchsiz/50)
UNION ALL
SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx,
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, ksmchidx, 500*TRUNC(ksmchsiz/500)
UNION ALL
SELECT '6+ (4108+)' bucket, ksmchcls, ksmchidx,
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, ksmchidx, 1000*TRUNC(ksmchsiz/1000);
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
0 (<140) free 1 60 502 64 64 32128
0 (<140) free 2 100 53 104 104 5512
0 (<140) free 2 50 533 56 56 29848
0 (<140) free 1 50 82 56 56 4592
0 (<140) free 2 80 181 88 83 15088
0 (<140) free 1 120 948 128 126 119848
0 (<140) free 1 70 152 72 72 10944
0 (<140) free 1 100 255 104 104 26520
0 (<140) free 1 130 191 136 136 25976
0 (<140) free 2 120 455 128 126 57776
0 (<140) free 2 130 39 136 136 5304
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
0 (<140) free 2 110 123 112 112 13776
0 (<140) free 2 70 61 72 72 4392
0 (<140) free 2 40 238 48 45 10848
0 (<140) free 1 30 1 32 32 32
0 (<140) free 2 90 119 96 96 11424
0 (<140) free 2 60 101 64 64 6464
0 (<140) free 1 90 770 96 96 73920
0 (<140) free 1 40 1813 48 46 84136
0 (<140) free 1 80 669 88 82 55072
0 (<140) free 1 110 611 112 112 68432
1 (140-267) free 1 220 87 232 225 19600
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
1 (140-267) free 2 200 44 216 206 9096
1 (140-267) free 2 260 17 264 264 4488
1 (140-267) free 1 240 714 256 254 181400
1 (140-267) free 1 160 53 176 163 8656
1 (140-267) free 1 200 279 216 207 57880
1 (140-267) free 2 240 373 256 254 94864
1 (140-267) free 2 140 144 152 146 21024
1 (140-267) free 1 180 37 192 191 7072
1 (140-267) free 1 260 24 264 264 6336
1 (140-267) free 2 160 32 176 167 5352
1 (140-267) free 2 180 30 192 190 5712
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
1 (140-267) free 2 220 30 232 228 6840
1 (140-267) free 1 140 535 152 146 78232
2 (268-523) free 1 400 157 448 422 66336
2 (268-523) free 2 400 39 448 420 16392
2 (268-523) free 1 450 109 496 473 51640
2 (268-523) free 1 350 183 392 367 67256
2 (268-523) free 2 350 107 392 363 38888
2 (268-523) free 2 300 60 344 314 18888
2 (268-523) free 1 300 329 344 316 104160
2 (268-523) free 2 450 21 496 476 10000
2 (268-523) free 2 500 13 520 512 6656
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
2 (268-523) free 2 250 18 296 277 4992
2 (268-523) free 1 500 187 520 512 95800
2 (268-523) free 1 250 238 296 282 67232
3-5 (524-4107) free 2 2000 1588 2496 2151 3416552
3-5 (524-4107) free 2 2500 195 2992 2749 536144
3-5 (524-4107) free 2 4000 743 4104 4041 3003008
3-5 (524-4107) free 1 2000 406 2496 2225 903472
3-5 (524-4107) free 1 2500 250 2992 2775 693992
3-5 (524-4107) free 1 1000 1169 1496 1167 1364992
3-5 (524-4107) free 2 1500 2233 1992 1857 4147304
3-5 (524-4107) free 1 4000 312 4104 4044 1261896
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
3-5 (524-4107) free 2 1000 389 1496 1177 458008
3-5 (524-4107) free 2 3500 889 3992 3838 3412568
3-5 (524-4107) free 2 3000 253 3496 3246 821440
3-5 (524-4107) free 1 3000 444 3496 3258 1446632
3-5 (524-4107) free 1 1500 862 1984 1856 1600232
3-5 (524-4107) free 2 500 175 992 796 139416
3-5 (524-4107) free 1 3500 1498 3992 3845 5761024
3-5 (524-4107) free 1 500 1120 976 652 730560
6+ (4108+) free 2 225000 2 225280 225248 450496
6+ (4108+) free 2 176000 7 176384 176164 1233152
6+ (4108+) free 2 237000 11 237888 237829 2616128
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
6+ (4108+) free 2 338000 1 338432 338432 338432
6+ (4108+) free 2 131000 8 131072 131072 1048576
解決辦法
1 調整應用使用繫結變數(最佳途徑)
2 CURSOR_SHARING引數共享遊標
3 共享池重新整理
共享池重新整理注意事項
重新整理將導致所有沒被使用的遊標從共享池刪除。這樣，在共享池重新整理之後，大多數SQL和PL/SQL遊標必須被硬解析。這將提高CPU的使用，也會加大Latch的活動。
當應用程式沒有使用繫結變數並被許多使用者進行類似的操作的時候(如在OLTP系統中) ，重新整理之後很快還會出現碎片問題。所以共享池對設計糟糕的應用程式來說不是解決辦法。
對一個大的共享池重新整理可能會導致系統掛起，尤其是例項繁忙的時候，推薦在非高峰的時候重新整理
4. Oracle bug
已知bug
|
BUG |
描述 |
Workaround |
Fixed |
|
<1397603> |
ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles |
_db_handles_cached = 0 |
901/ 8172 |
|
<1640583> |
ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access |
Not available |
8171/901 |
|
<1318267> |
INSERT AS SELECT statements may |
_SQLEXEC_PROGRESSION_COST=0 |
8171/8200 |
|
<1193003> |
Cursors may not be shared in 8.1 |
Not available |
8162/8170/ 901 |
|
<2104071> |
ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS) |
None-> This is known to affect the XML parser. |
8174, 9013, 9201 |
|
<263791.1> |
Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset |
Not available |
9205 |
三、 高階分析
如果前述的這些技術內容都不能解決ORA-04031 錯誤，可能需要額外的跟蹤資訊來得到問題發生的共享池的快照。
調整init.ora引數新增如下的事件得到該問題的跟蹤資訊：
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"
如果問題可重現，該事件可設定在會話層，在執行問題語句之前使用如下的語句：
SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 3';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-1247889/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- buffer cache 和shared pool詳解 診斷和解決ORA-04031 錯誤
- ORA-04031錯誤分析
- ORA-04031錯誤分析和解決
- shared_pool——解決ORA-4031錯誤
- ORA-12853和ORA-04031 large pool 不足錯誤解決
- 簡單分析shared pool(一)
- 簡單分析shared pool(二)
- 簡單分析shared pool(三)
- ORA-04031錯誤導致當機案例分析
- Shared Pool 的轉儲與分析
- interval partition自動新增分割槽引起的shared pool 4031錯誤
- ORA-04031錯誤詳解
- 【Shared Pool】使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared PoolSQL物件
- Oracle shared poolOracle
- 案例分析：ora-04031與ora-04030錯誤分析與解決
- ORA-04031錯誤的處理
- _shared_pool_reserved_pct or shared_pool_reserved_size with ASMMASM
- Shared pool深入分析及效能調整
- EXPDP Fails With ORA-04031 ("streams pool", ...)AI
- zt_Oracle shared pool internals_共享池_shared_poolOracle
- SHARED POOL總結
- SHARED_POOL解析
- 理解Oracle Shared PoolOracle
- 如何解決ORA-04031 錯誤(轉)
- 如何解決ORA-04031 錯誤(zt)
- Shared pool深入分析及效能調整(一)
- Shared pool深入分析及效能調整(二)
- oracle實驗記錄 (oracle 分析shared pool(1))Oracle
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- 診斷並解決ORA-04031 錯誤
- AWR分析。（shared_pool，sga_size大小設定）
- 關於weblogic pool 一錯誤除錯Web除錯
- Oracle Shared Pool Memory ManagementOracle
- ORACLE SGA之shared poolOracle
- Oracle imp/impdp報ORA-04031:streams pool,..fixed allocation callback解決/分析Oracle
- 使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared PoolSQL物件
- dbms_shared_pool keep物件到share pool中物件
- SHARED POOL 基礎知識