SHARED POOL ORA-04031錯誤分析

renjixinchina發表於2014-08-08
原文網址 : http://blog.itpub.net/15747463/viewspace-1247889/

一、        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
not be shared when they should be
if TIMED_STATISTICS. It can lead to ORA-4031

_SQLEXEC_PROGRESSION_COST=0

8171/8200

<1193003>

Cursors may not be shared in 8.1
when they should be

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章