一、 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 |
|
|
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 |
_SQLEXEC_PROGRESSION_COST=0 |
8171/8200 |
|
|
Cursors may not be shared in 8.1 |
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';