SGA中的内存池包含不同大小的内存块。当数据库启动时，就有一个大的内存块分配并被hush buckets 里的空闲列表追踪。随着时间推移，随着内存的分配和释放，内存块被按照大小在不同的hush buckets间移动。当SGA里任何一个内存池里出现不能满足内部分配请求的情况时，ORA-04031就出现了。
shared pool共享池的管理方式不同于其它的内存池。。共享池存放与数据字典和library cache有关的信息。但是，这些内存区域根据空闲列表和最近使用算法(LRU)管理。当在共享池的所有搜索结束后，从LRU列表清除所有的可能清除的对象， 多次扫描空闲列表后，仍没有找到内存块，ORA-04031就出现了。这意味着ORA-04031很难预测。
对共享池的监测，可以看它是否包含许多类似的SQL,只有文字不同。 这种情况会占用更多的共享池内存并引共享池碎片，过多的共享池碎片(fragment)会导致虽然共享池中仍有大量的free memory，但都是尺寸较小的内存块(chunk)，当Oracle进程申请一些较大的连续内存空间(memory chunk)时，虽然共享池中的free memory大小远大于申请的连续空间大小，仍会引发ORA-4031错误。使用绑定变量可以使SQL 共享。使用本文所附的脚本可以查出内存中是否有许多类似SQL。
即使使用了绑定变量后，仍然可能存在高version count（子指针）的情况。为了使子指针共享，CURSOR_SHARING参数可能需要调整。metalink 文档Note 296377.1 和 261020.1可以提供详细信息。若造成4031的原因是由于未绑定变量或者游标无法共享导致的过度硬解析(Hard Parse)，则应当调整应用绑定变量或者调整初始化参数。
9i中开始引入shared pool subpool子池技术。设计多个共享池子池的目的是分散单个shared pool LRU Latch的并行压力。 ，每一个子池都包含自有的LRU LIST和保留区域等其他内存结构。 subpools子池的数量会在Oracle实例启动(startup nomount)时根据SGA_MAX_SIZE(或11g中的memory_max_target)以及服务器上的CPU数量而自动决定。子池数量最多为7个，在Card系统中为4个。
每一个subpool都是一个"迷你型"的共享池，其包括自有的Freelist、内存结构记录和LRU LIST。subpool子池技术是对shared pool并发扩张能力的增强，且每一个子池现在都使用独立的shared pool child latch来保护。这意味着不再像9i以前的版本那样因为只有一个shared pool latch而剧烈争用了。
但是在实际使用中发现版本9i中仍存在一些BUG，可能导致在子池之间的内存使用分布不平衡均匀，这可能导致虽然部分子池使用率不高，但是个别子池内存过度分配，从而导致ORA-4031错误。一般把这种现象称作"subpool imbalance"。
分析问题发生时段的ALERT.LOG内容如下：
Wed Nov 21 02:16:01 2012
ARC1: Evaluating archive log 3 thread 1 sequence 2373
ARC1: Beginning to archive log 3 thread 1 sequence 2373
Creating archive destination LOG_ARCHIVE_DEST_1: '/card_arch1/1_2373.arc'
ARC1: Completed archiving log 3 thread 1 sequence 2373
Wed Nov 21 16:11:46 2012
Errors in file /oracle/admin/card/udump/card1_ora_6782.trc:
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","select rowid, count(*) as TO...","sga heap(1,0)","library cache")
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","select rowid, count(*) as TO...","sga heap(1,0)","library cache")
Wed Nov 21 16:11:47 2012
Trace dumping is performing id=[cdmp_20121121161147]
Wed Nov 21 16:14:10 2012
Errors in file /oracle/admin/card/udump/card1_ora_6682.trc:
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","TD_REPTCASH","sga heap(1,0)","library cache")
ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","TD_REPTCASH","sga heap(1,0)","library cache")
Wed Nov 21 16:14:11 2012
Trace dumping is performing id=[cdmp_20121121161411
在16:11:46时SPID=6782的前台服务进程报ORA-4031错误，分析该进程的TRACE如下：
/oracle/admin/card/udump/card1_ora_6782.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle/product/9.2
System name: HP-UX
Node name: ecprod1
Release: B.11.31
Version: U
Machine: ia64
Instance name: card1
Redo thread mounted by this instance: 1
Oracle process number: 114
Unix process pid: 6782, image: oracle@ecprod1 (TNS V1-V3)
*** 2012-11-21 16:06:40.006
*** SESSION ID:(498.18090) 2012-11-21 16:06:40.005
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=11/21/2012 16:06:39
Allocation request for: kkslpkp - literal info.
Heap: c000000353cb25d0, size: 4200
******************************************************
HEAP DUMP heap name="sga heap" desc=c000000381602030
extent sz=0xfe0 alt=200 het=32767 rec=9 flg=-126 opc=0
parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x1
******************************************************
HEAP DUMP heap name="library cache" desc=c000000353cb25d0
extent sz=0x348 alt=32767 het=32 rec=0 flg=2 opc=2
parent=c000000381602030 owner=c00000032f85db08 nex=0000000000000000 xsz=0x1
Subheap has 4712 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: c0000002e93dac10, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=114, calls cur/top: c00000030275a888/c00000030275a888, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 111
last post received-location: kglpndl: post after freeing latch
last process to post me: c0000002e93dd1b0 137 0
last post sent: 1298803 0 16
last post sent-location: ksasnd
last process posted by me: c0000002e93bb2d0 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c0000002ea3a4ff0
O/S info: user: oracle, term: UNKNOWN, ospid: 6782
OSD pid info: Unix process pid: 6782, image: oracle@ecprod1 (TNS V1-V3)
=========================
User Session State Object
=========================
----------------------------------------
SO: c0000002ea3fc930, type: 4, owner: c0000002e93dac10, flag: INIT/-/-/0x00
(session) trans: 0000000000000000, creator: c0000002e93dac10, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0001-0072-00161539, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql: c000000347a44a50, psql: c0000003009a42e0, user: 26/OC_MES
O/S info: user: root, term: , ospid: 1234, machine: ecrsouterapp1
program:
last wait for 'latch free' blocking sess=0x0 seq=33786 wait_time=12162
address=c00000030245cdc8, number=9d, tries=0
temporary object counter: 0
=========================
Current Parent KGL Object
=========================
LIBRARY OBJECT HANDLE: handle=c000000347a44a50
name=select rowid, count(:"SYS_B_0") amount from te_enterpriseinfo_cur a where a.borrowerid = :"SYS_B_1" and a.regionalismcode like :"SYS_B_2"
hash=5740b5 timestamp=11-21-2012 16:06:39
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=X latch#=9
lwt=c000000347a44a80[c000000347a44a80,c000000347a44a80] ltm=c000000347a44a90[c000000347a44a90,c000000347a44a90]
pwt=c000000347a44ab0[c000000347a44ab0,c000000347a44ab0] ptm=c000000347a44b40[c000000347a44b40,c000000347a44b40]
ref=c000000347a44a60[c000000347a44a60, c000000347a44a60] lnd=c000000347a44b58[c000000347a44b58,c000000347a44b58]
LIBRARY OBJECT: object=c00000037760ea28
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 c00000037760ec88 c00000035b9ba6a8 c000000353207a90
C000000000000004 ?
C000000000032F90 ?
----- End of Call Stack Trace -----
===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
_________________________ __________
"free memory " 82400392
"miscellaneous " 43284312
"type object de " 0
"parameters " 0
"KCL name table " 3673416
"MTTR advisory " 98256
"Checkpoint queue " 9177280
"db_block_hash_buckets " 9699328
"PL/SQL MPCODE " 0
"trigger inform " 0
"sim memory hea " 1279200
"KCL extra lock elements " 864000
"PL/SQL PPCODE " 0
"ges reserved msg buffers " 2096008
"fixed allocation callback" 744
"pl/sql source " 0
"KQR M PO " 189272
"trigger source " 0
"ges enqueues " 3261024
"KSXR pending messages que" 853952
"gcs shadows " 12303112
"ges resource hash table " 1048576
"dictionary cache " 1068608
"ges resources " 1592696
"enqueue resources " 572504
"errors " 0
"PL/SQL DIANA " 0
"library cache " 439251408
"KQR L PO " 266936
"sql area " 64536
"sessions " 749008
"gcs resources " 20555384
"KQR S SO " 2048
"trigger defini " 0
"event statistics per sess" 3137568
"KGLS heap " 26608
"KQR X PO " 18032
"table definiti " 0
===============================
Memory Utilization of Subpool 2
===============================
Allocation Name Size
_________________________ __________
"free memory " 108187608
"miscellaneous " 37722824
"table definiti " 1984
"ges enqueues " 3395408
"db_block_hash_buckets " 9794640
"ksm_file2sga region " 370496
"PL/SQL DIANA " 175992
"KQR S SO " 512
"KQR L PO " 633360
"gcs shadows " 28408552
"sim memory hea " 1286432
"parameters " 206424
"1M buffer " 1049088
"errors " 40280
"type object de " 0
"KQR M PO " 804672
"trigger defini " 272
"trigger source " 88
"partitioning d " 22728
"channel handle " 260392
"KGK heap " 552
"db_files " 253952
"dictionary cache " 1068608
"KQR X PO " 23184
"gcs resources " 36658760
"transaction " 480384
"trigger inform " 64
"library cache " 159389832
"ges resources " 1482984
"sql area " 226277160
"sessions " 746304
"Checkpoint queue " 9177280
"event statistics per sess" 3148936
"fixed allocation callback" 720
"FileIdentificatonBlock " 3580496
"KGLS heap " 381736
"ktlbk state objects " 651240
"PL/SQL MPCODE " 1850264
===============================
Memory Utilization of Subpool 3
===============================
Allocation Name Size
_________________________ __________
"free memory " 108746832
"miscellaneous " 43190000
"KGLS heap " 340640
"gcs resource hash table " 16105280
"trigger source " 80
"joxs heap init " 4240
"PL/SQL PPCODE " 23624
"errors " 5592
"KQR L PO " 612576
"trigger inform " 64
"table definiti " 1560
"sim memory hea " 1279200
"MTTR advisory " 106368
"1M buffer " 1049088
"ges regular msg buffers " 1088248
"gcs resources " 20555384
"KQR M PO " 805872
"pl/sql source " 0
"KQR X PO " 59248
"KQR S SO " 2816
"ges enqueues " 3985496
"ges resources " 1917944
"dictionary cache " 1068608
"trigger defini " 9008
"type object de " 0
"PL/SQL DIANA " 410776
"parameters " 187768
"library cache " 197201256
"processes " 1376000
"sql area " 209882928
"sessions " 746304
"qmps connections " 486200
"event statistics per sess" 3137568
"Checkpoint queue " 9177280
"PLS non-lib hp " 2088
"gcs shadows " 12306208
"fixed allocation callback" 680
"PL/SQL MPCODE " 1661384
===============================
Memory Utilization of Subpool 4
===============================
Allocation Name Size
_________________________ __________
"free memory " 82782928
"miscellaneous " 40984472
"ges resources " 1783744
"db_handles " 1160000
"parameters " 323936
"sim memory hea " 1279200
"KGLS heap " 697784
"KQR M PO " 791440
"ges big msg buffers " 4674088
"temporary tabl " 0
"PL/SQL DIANA " 146680
"partitioning d " 67896
"KGK heap " 65584
"KQR L PO " 600312
"Checkpoint queue " 9177280
"PL/SQL MPCODE " 3006528
"enqueue " 2332272
"FileOpenBlock " 16104056
"KSXR receive buffers " 1034000
"table definiti " 3272
"KQR X PO " 46368
"ges enqueues " 4135704
"dictionary cache " 1068608
"gcs shadows " 12306208
"KQR S SO " 2304
"PX subheap " 54640
"trigger inform " 680
"library cache " 138469232
"gcs resources " 20555384
"sql area " 271924816
"sessions " 746304
"errors " 15304
"event statistics per sess" 3137568
"ges process array " 1273272
"pl/sql source " 0
"trigger defini " 4400
"type object de " 0
"fixed allocation callback" 728
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 1044847223 0.999 1198493483 0.551 507345521 90137
TABL/PRCD/TYPE 1190948651 1.000 2350380701 1.000 125865 0
BODY/TYBD 568579 1.000 568659 0.999 71 0
TRGR 12494087 1.000 12494089 1.000 774 0
INDX 2091941 0.999 2091942 0.999 34 0
CLST 68101 0.998 83171 0.996 0 0
OBJE 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIR 348 0.994 216 0.778 22 0
QUEU 0 0.000 0 0.000 0 0
OBJG 0 0.000 0 0.000 0 0
PROP 0 0.000 0 0.000 0 0
JVSC 502 0.578 502 0.578 0 0
JVRE 502 0.584 502 0.584 0 0
ROBJ 0 0.000 0 0.000 0 0
REIP 0 0.000 0 0.000 0 0
CPOB 0 0.000 0 0.000 0 0
EVNT 11919560 1.000 17875331 1.000 1680 0
SUMM 0 0.000 0 0.000 0 0
DIMN 0 0.000 0 0.000 0 0
CTX 0 0.000 0 0.000 0 0
OUTL 0 0.000 0 0.000 0 0
RULS 0 0.000 0 0.000 0 0
RMGR 0 0.000 0 0.000 0 0
IFSD 0 0.000 0 0.000 0 0
PPLN 0 0.000 0 0.000 0 0
PCLS 0 0.000 0 0.000 0 0
SUBS 0 0.000 0 0.000 0 0
LOCS 0 0.000 0 0.000 0 0
RMOB 0 0.000 0 0.000 0 0
RSMD 0 0.000 0 0.000 0 0
JVSD 0 0.000 0 0.000 0 0
ENPR 0 0.000 0 0.000 0 0
RELC 0 0.000 0 0.000 0 0
STREAM 0 0.000 0 0.000 0 0
APPLY 0 0.000 0 0.000 0 0
APPLY SOURCE 0 0.000 0 0.000 0 0
APPLY DESTN 0 0.000 0 0.000 0 0
TEST 0 0.000 0 0.000 0 0
CUMULATIVE 2262939494 0.999 3581988596 0.850 507473967 90137
当Oracle进程触发ORA-4031错误后会产生4031相关的TRACE，其中包括了当时简要的shared pool使用情况； 分析上述4031 trace中的信息可以发现存在4个subpool (Memory Utilization of Subpool 1、Memory Utilization of Subpool 2、Memory Utilization of Subpool 3、Memory Utilization of Subpool 4)。
以上4个subpool的主要使用情况如下：
|
Subpool1 |
Subpool2 |
Subpool3 |
Subpool4 |
Total Size |
| Free Memory |
82400392 |
108187608 |
108746832 |
82782928 |
364MB |
| library cache |
439251408 |
159389832 |
197201256 |
138469232 |
947MB |
| Sql area |
64536 |
226277160 |
209882928 |
271924816 |
675MB |
| Total size |
637531776 |
637531776 |
637531776 |
620754624 |
2415MB |
从以上表格可以看到library cache和SQL Area占用了1600MB的共享池空间，而实际free memory的总数也达到364MB，实际引发ORA-4031时所申请的chunk大小仅为4216 bytes。
进一步分析可以发现在subpool1中的sql area仅占用64536字节，而subpool 4中sql area占用271924816字节，说明subpool1与其他subpool之间在空间使用上存在不均衡。
为了进一步分析Card库的共享池使用情况，我们通过动态性能视图收集了11-23日 14时当前的shared pool使用状态，由于并非在ORA-4031当时所收集的信息，虽然不能作为诊断该问题的直接证据，但同样具有参考意义：
BUCKET KSMCHCLS subpool From Count Biggest AvgSize Total
-------------------------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
0 (<140) free 1 40 1443 48 43 62112
0 (<140) free 1 50 373 56 56 20888
0 (<140) free 1 60 537 64 64 34368
0 (<140) free 1 70 364 72 72 26208
0 (<140) free 1 80 2778 88 86 241024
0 (<140) free 1 90 206 96 96 19776
0 (<140) free 1 100 230 104 104 23920
0 (<140) free 1 110 602 112 112 67424
0 (<140) free 1 120 706 128 124 88112
0 (<140) free 1 130 199 136 136 27064
0 (<140) free 2 40 1031 48 43 45232
0 (<140) free 2 50 766 56 56 42896
0 (<140) free 2 60 616 64 64 39424
0 (<140) free 2 70 374 72 72 26928
0 (<140) free 2 80 3421 88 86 295744
0 (<140) free 2 90 338 96 96 32448
0 (<140) free 2 100 451 104 104 46904
0 (<140) free 2 110 325 112 112 36400
0 (<140) free 2 120 1458 128 121 176832
0 (<140) free 2 130 233 136 136 31688
0 (<140) free 3 40 1604 48 44 70896
0 (<140) free 3 50 640 56 56 35840
0 (<140) free 3 60 694 64 64 44416
0 (<140) free 3 70 680 72 72 48960
0 (<140) free 3 80 3006 88 85 258256
0 (<140) free 3 90 615 96 96 59040
0 (<140) free 3 100 602 104 104 62608
0 (<140) free 3 110 487 112 112 54544
0 (<140) free 3 120 1298 128 123 160360
0 (<140) free 3 130 340 136 136 46240
0 (<140) free 4 40 1110 48 43 48424
0 (<140) free 4 50 758 56 56 42448
0 (<140) free 4 60 557 64 64 35648
0 (<140) free 4 70 451 72 72 32472
0 (<140) free 4 80 2678 88 85 230072
0 (<140) free 4 90 305 96 96 29280
0 (<140) free 4 100 333 104 104 34632
0 (<140) free 4 110 303 112 112 33936
0 (<140) free 4 120 1353 128 121 164832
0 (<140) free 4 130 243 136 136 33048
1 (140-267) free 1 200 1001 216 204 205048
1 (140-267) free 1 140 378 152 148 55968
1 (140-267) free 1 160 568 176 164 93208
1 (140-267) free 1 180 178 192 188 33608
1 (140-267) free 1 220 1536 232 231 355320
1 (140-267) free 1 240 449 256 249 112048
1 (140-267) free 1 260 135 264 264 35640
1 (140-267) free 2 200 1028 216 206 212768
1 (140-267) free 2 140 774 152 148 114560
1 (140-267) free 2 160 1293 176 171 221352
1 (140-267) free 2 180 576 192 189 109144
1 (140-267) free 2 220 377 232 228 86184
1 (140-267) free 2 240 921 256 250 230856
1 (140-267) free 2 260 272 264 264 71808
1 (140-267) free 3 200 1078 216 205 221824
1 (140-267) free 3 140 874 152 148 129448
1 (140-267) free 3 160 1642 176 168 276560
1 (140-267) free 3 180 2612 192 185 483816
1 (140-267) free 3 220 443 232 227 100824
1 (140-267) free 3 240 858 256 248 213608
1 (140-267) free 3 260 193 264 264 50952
1 (140-267) free 4 200 741 216 206 152928
1 (140-267) free 4 140 546 152 148 80816
1 (140-267) free 4 160 1164 176 169 197160
1 (140-267) free 4 180 408 192 187 76632
1 (140-267) free 4 220 304 232 227 69216
1 (140-267) free 4 240 578 256 249 144200
1 (140-267) free 4 260 172 264 264 45408
2 (268-523) free 1 300 494 336 320 158368
2 (268-523) free 1 250 450 296 284 127960
2 (268-523) free 2 300 719 336 320 230744
2 (268-523) free 2 500 20 520 518 10360
2 (268-523) free 2 250 655 296 283 185744
2 (268-523) free 2 450 51 496 480 24488
2 (268-523) free 3 300 956 336 322 308272
2 (268-523) free 3 250 641 296 281 180688
2 (268-523) free 4 300 636 336 320 203920
2 (268-523) free 4 400 46 424 409 18840
2 (268-523) free 4 250 582 296 283 165232
2 (268-523) free 4 350 23 392 383 8824
3-5 (524-4107) free 1 500 1 776 776 776
3-5 (524-4107) free 1 3000 1 3160 3160 3160
3-5 (524-4107) free 2 500 306 984 700 214312
3-5 (524-4107) free 2 1000 123 1496 1201 147760
3-5 (524-4107) free 2 1500 1139 1992 1878 2139208
3-5 (524-4107) free 2 2000 1444 2496 2322 3353672
3-5 (524-4107) free 2 2500 4534 2992 2751 12476912
3-5 (524-4107) free 2 3000 7520 3496 3211 24151448
3-5 (524-4107) free 2 3500 6105 3992 3770 23018216
3-5 (524-4107) free 2 4000 2403 4104 4065 9769384
3-5 (524-4107) free 3 500 652 992 858 559536
3-5 (524-4107) free 3 1000 1897 1496 1162 2204544
3-5 (524-4107) free 3 1500 289 1992 1726 498840
3-5 (524-4107) free 3 2000 2276 2496 2260 5144800
3-5 (524-4107) free 3 2500 4021 2992 2789 11217152
3-5 (524-4107) free 3 3000 1822 3496 3279 5975424
3-5 (524-4107) free 3 3500 11485 3992 3785 43475232
3-5 (524-4107) free 3 4000 2710 4104 4048 10970720
3-5 (524-4107) free 4 500 44 992 966 42512
3-5 (524-4107) free 4 1000 238 1488 1177 280248
3-5 (524-4107) free 4 1500 152 1968 1765 268392
3-5 (524-4107) free 4 2000 2015 2496 2376 4789544
3-5 (524-4107) free 4 2500 3400 2992 2754 9364976
3-5 (524-4107) free 4 3000 2037 3496 3287 6696072
3-5 (524-4107) free 4 3500 4731 3992 3710 17552504
3-5 (524-4107) free 4 4000 2043 4104 4073 8321968
6+ (4108+) free 1 4000 8 4712 4250 34000
6+ (4108+) free 1 5000 1 5912 5912 5912
6+ (4108+) free 1 7000 1 7048 7048 7048
6+ (4108+) free 1 13000 1 13416 13416 13416
6+ (4108+) free 1 15000 1 15752 15752 15752
6+ (4108+) free 1 19000 1 19600 19600 19600
6+ (4108+) free 1 21000 1 21360 21360 21360
6+ (4108+) free 1 25000 1 25232 25232 25232
6+ (4108+) free 2 4000 100 4272 4146 414648
6+ (4108+) free 2 8000 4 8456 8338 33352
6+ (4108+) free 2 11000 1 11872 11872 11872
6+ (4108+) free 3 4000 145 4720 4132 599248
6+ (4108+) free 3 8000 4 8664 8432 33728
6+ (4108+) free 4 4000 90 4528 4152 373728
6+ (4108+) free 4 6000 1 6896 6896 6896
6+ (4108+) free 4 7000 3 7848 7626 22880
6+ (4108+) free 4 8000 3 8456 8360 25080
以上列出了各subpool freelist bucket的情况，6+ (4108+) 代表存放大于4108bytes的free chunk的hash bucket， 可以看到大于4108bytes的free chunk总大小越为1.6M， 而3-5 (524-4107) bucket中chunk数量最多、总大小也最大。 由此可知card库共享池存在大量碎片。过多的共享池碎片导致连续尺寸大约4108字节的free chunk越来越少，当16:11时需要分配4216字节连续chunk时，由于无法分配该连续空间而触发了ORA-4031错误。
综上所述是由于共享池碎片和subpool内存分配不均衡最终引发了此次ORA-4031 。
18:33时触发过ORA-4031的Oracle进程陆续报ORA-00600: [729], [8680], [space leak]错误，该错误的stack call 如下 ksudel=> ksuxds=> ksmugf=> cold_ksmuhe
*** 2012-11-21 18:33:35.807
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [729], [8680], [space leak], [], [], [], [], []
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+528 call _etext_f()+23058430 000000000 ?
09110304648 C000000000000996 ?
400000000285CD00 ?
ksfdmp()+64 call _etext_f()+23058430 000000003 ?
09110304648
kgeriv()+432 call _etext_f()+23058430 600000000004F280 ?
09110304648 000000003 ?
C000000000000716 ?
40000000052FF030 ?
00000863F ?
60000000004B5788 ?
000000000 ? 000000000 ?
kgesiv()+208 call _etext_f()+23058430 600000000004F280 ?
09110304648 60000000004B7A08 ?
60000000000502F8 ?
600000000001D800 ?
600000000001D790 ?
ksesic2()+208 call _etext_f()+23058430 600000000004F280 ?
09110304648 60000000005E5198 ?
0000002D9 ? 000000002 ?
9FFFFFFFFFFF9418 ?
$cold_ksmuhe()+432 call _etext_f()+23058430 0000002D9 ?
09110304648 60000000004C1680 ?
9FFFFFFFFFFF9418 ?
60000000004BFFD0 ?
00000000A ?
4000000000874260 ?
60000000005E1780 ?
000000000 ?
ksmugf()+688 call _etext_f()+23058430 0000021E8 ?
09110304648 600000000004F060 ?
60000000005E1778 ?
000000001 ?
C000000000000B9C ?
ksuxds()+4320 call _etext_f()+23058430 4000000000C9B4B8 ?
09110304648 C0000002EB3B47B8 ?
400000000086A4C0 ?
C000000000001736 ?
4000000001EC4B60 ?
000008E73 ?
9FFFFFFFFFFF9480 ?
60000000004ABC40 ?
ksudel()+144 call 9fffffffffff94f8 C0000002EB3B4320 ?
通过上述stack call可以定位到Bug 3931332 : ORA-600 [729] LEAKS WITH ORA-4031 TRACES：
Hdr: 3931332 9.2.0.5.0 RDBMS 9.2.0.5.0 DICTIONARY PRODID-5 PORTID-46 ORA-600
Abstract: ORA-600 [729] LEAKS WITH ORA-4031 TRACES
sedmp: internal or fatal error
ORA-600: internal error code, arguments: [729], [4380], [space leak], [],
[], [], [], []
----- Call Stack Trace -----
ksmuhe ksmugf ksuxds ksudel
以上ORA-600 [729] LEAKS内部错误常由ORA-4031引发，一般通过避免ORA-4031可以绕过ORA-600错误
问题总结：
- 由于共享池碎片和subpool内存分配不均衡最终引发了此次ORA-4031，解决方案见下文建议部分
- ORA-600 [729] LEAKS内部错误由ORA-4031触发BUG 3931332引起，通过避免ORA-4031错误可以绕过该问题
- 执行statspack长时间无响应并消耗100%的CPU的问题在11-23日实际操作中未出现，由于没有当时的性能记录无法进一步追查。
1.1 建议
- 定期监控shared pool free memory空闲内存量和碎片情况，具体可以使用如下脚本：
set pages 1000 lines 120
col name for a60
col value for a30
select * from v$sgastat where pool like 'shared%' and name='free memory';
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);
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
- 当shared pool 空闲内存少于80M或碎片过多时考虑alter system flush shared_pool强制冲刷共享池。若条件允许也可以考虑定期在业务空闲时段例如凌晨冲刷共享池，或者定期重启数据库实例
- 启用statspack自动收集性能快照，一般Interval为一个小时，statspack是久经考验的性能监控工具，对性能的损耗非常小。
- 考虑部署OSW性能监控工具：
OSWatcher(OS Watcher Black Box)是Oracle标准的Support工具，该工具通过一系列的SHELL脚本来收集多方面的OS信息，包括CPU、内存、网络等。
OSWatcher不会给系统带来较重的性能负载，OSWatcher本质上是调用最常见的OS命令如ps、vmstat、netstat。
通过大量广泛的OSW使用，已经证明了OSW是一套安全、可靠的资源监控工具。
更多OSW的信息可以参考以下Metalink文档：
OSW 用户手册：
OSWatcher Black Box User Guide.
What Is The OSWATCHER Black Box Effect On The Server Performance And What Are The Alternative Commands? [ID 946107.1]
中文：OSWatcher Black Box: How to improve performance and monitor your system automatically (Mandarin) [ID 1492202.1]
OSWatcher Black Box Analyzer User Guide [ID 461053.1]