ITPub博客

首页 > 数据库 > Oracle > SHARED POOL ORA-04031错误分析

SHARED POOL ORA-04031错误分析

原创 Oracle 作者:renjixinchina 时间:2014-08-08 15:04:52 0 删除 编辑

一、        SHARED POOLORA-4031错误

SHARE POOL利用堆(HEAP)的内存管理方式管理,内存是预先分配的,当某个进程需要分配shared pool的内存的时候,Heap Manager就满足该请求,Heap Manager也和其他ORACLE模块一起工作来回收shared pool的空闲内存。

SHARE POOL在物理上由多个内存区组成,内存区又由多个不同大小的CHUNK组成。而CHUNK又有可重用和空闲之分,并且它们分别有LRU LISTFREE LISTRESERVED 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 共享池刷新

共享池刷新注意事项

刷新将导致所有没被使用的游标从共享池删除。这样,在共享池刷新之后,大多数SQLPL/SQL游标必须被硬解析。这将提高CPU的使用,也会加大Latch的活动。

当应用程序没有使用绑定变量并被许多用户进行类似的操作的时候(如在OLTP系统中) ,刷新之后很快还会出现碎片问题。所以共享池对设计糟糕的应用程序来说不是解决办法。

对一个大的共享池刷新可能会导致系统挂起,尤其是实例繁忙的时候,推荐在非高峰的时候刷新

4.  Oracle bug

已知bug

BUG

描述

Workaround

Fixed

ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles

_db_handles_cached = 0

901/ 8172

ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access

Not available

8171/901

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

Cursors may not be shared in 8.1
when they should be

Not available

8162/8170/ 901

ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS)

None-> This is known to affect the XML parser.

8174, 9013, 9201

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-01-30

  • 博文量
    373
  • 访问量
    2057235