本文為墨天輪數據庫管理服務團隊第130期技術分享，內容原創，作者為技術顧問肖傑，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

問題概述

某客户核心數據庫報大量的ORA-4031錯誤，AWR快照也無法生成。

問題分析

alert日誌分析

Errors in file /u01/app/oracle/diag/rdbms/xxx/DB2/trace/DB2_j000_227342.trc  (incident=522065):
ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(6,0)","modification ")
Incident details in: /u01/app/oracle/diag/rdbms/xxx/DB2/incident/incdir_522065/DB2_j000_227342_i522065.trc
Use ADRCI or Support Workbench to package the incident.

從錯誤日誌可以看出，需要在heap(6,0)申請4160 bytes的空間，之後連續報了大量同樣的錯誤，接下來對trc進行分析：

TRC分析

HEAP DUMP heap name="sga heap(6,0)"  desc=0x6008bf00
 extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x10000000 heap=(nil)
 fl2=0x20, nex=(nil), dsxvers=1, dsxflg=0x0
 dsx first ext=0xc0000000
 latch set 6 of 7
 durations enabled for this heap
 reserved granules for root 0 (granule size 268435456)
EXTENT 0 addr=0x12d0000000
  Chunk       12d0000058 sz=       48  R-stopper   "reserved stoppe"
  Chunk       12d0000088 sz= 13422408  R-free      "               "
  Chunk       12d0cccfd0 sz=       48  R-stopper   "reserved stoppe"
  Chunk       12d0ccd000 sz=255012864    perm      "perm           "  alo=255012856
EXTENT 1 addr=0x1340000000
  Chunk       1340000058 sz=       48  R-stopper   "reserved stoppe"
  Chunk       1340000088 sz= 13422408  R-free      "               "
  Chunk       1340cccfd0 sz=       48  R-stopper   "reserved stoppe"
  Chunk       1340ccd000 sz=254025224    perm      "perm           "  alo=254025224
  Chunk       134ff0ee08 sz=   987344    perm      "perm           "  alo=987344
  Chunk       134ffffed8 sz=      296    free      "               "
EXTENT 2 addr=0x13c0000000
  Chunk       13c0000058 sz=       80    perm      "perm           "  alo=80
  Chunk       13c00000a8 sz=       48  R-stopper   "reserved stoppe"
  Chunk       13c00000d8 sz= 13422328  R-free      "               "
  Chunk       13c0cccfd0 sz=       48  R-stopper   "reserved stoppe"
  Chunk       13c0ccd000 sz=254058736    perm      "perm           "  alo=254058736
  Chunk       13cff170f0 sz=   954064    perm      "perm           "  alo=954064
  Chunk       13cfffffc0 sz=       64    free      "               "
Total heap size    =805306104
FREE LISTS:
 Bucket 0 size=32
  Chunk       13c0000078 sz=        0    kghdsx
 Bucket 1 size=40
 Bucket 2 size=48
 Bucket 3 size=56
 Bucket 4 size=64
  Chunk       13cfffffc0 sz=       64    free      "               "
 Bucket 5 size=72
 Bucket 6 size=80
 Bucket 7 size=88
 Bucket 8 size=96
 Bucket 9 size=104
 Bucket 10 size=112
 Bucket 11 size=120
 Bucket 12 size=128
 Bucket 13 size=136
 Bucket 14 size=144
 Bucket 15 size=152
 Bucket 16 size=160
 Bucket 17 size=168
 Bucket 18 size=176
 Bucket 19 size=184
 Bucket 20 size=192
 Bucket 21 size=200
 Bucket 22 size=208
 Bucket 23 size=216
 Bucket 24 size=224
 Bucket 25 size=232
 Bucket 26 size=240
 Bucket 27 size=248
 Bucket 28 size=256
 Bucket 29 size=264
 Bucket 30 size=272
 Bucket 31 size=280
 Bucket 32 size=288
 Bucket 33 size=296
  Chunk       134ffffed8 sz=      296    free      "               "
 Bucket 34 size=304
 Bucket 35 size=312
 Bucket 36 size=320
 Bucket 37 size=328
 Bucket 38 size=336
 Bucket 39 size=344
 Bucket 40 size=352
 Bucket 41 size=360
 Bucket 42 size=368
 Bucket 43 size=376
 Bucket 44 size=384
 Bucket 45 size=392
 Bucket 46 size=400
 Bucket 47 size=408
 Bucket 48 size=416
 Bucket 49 size=424
 Bucket 50 size=432
 Bucket 51 size=440
 Bucket 52 size=448
 Bucket 53 size=456
 Bucket 54 size=464
 Bucket 55 size=472
 Bucket 56 size=480
 Bucket 57 size=488
 Bucket 58 size=496
 Bucket 59 size=504
 Bucket 60 size=512
 Bucket 61 size=520
 Bucket 62 size=528
 Bucket 63 size=536
 Bucket 64 size=544
 Bucket 65 size=552
 Bucket 66 size=560
 Bucket 67 size=568
 Bucket 68 size=576
 Bucket 69 size=584
 Bucket 70 size=592
 Bucket 71 size=600
 Bucket 72 size=608
 Bucket 73 size=616
 Bucket 74 size=624
 Bucket 75 size=632
 Bucket 76 size=640
 Bucket 77 size=648
 Bucket 78 size=656
 Bucket 79 size=664
 Bucket 80 size=672
 Bucket 81 size=680
 Bucket 82 size=688
 Bucket 83 size=696
 Bucket 84 size=704
 Bucket 85 size=712
 Bucket 86 size=720
 Bucket 87 size=728
 Bucket 88 size=736
 Bucket 89 size=744
 Bucket 90 size=752
 Bucket 91 size=760
 Bucket 92 size=768
 Bucket 93 size=776
 Bucket 94 size=784
 Bucket 95 size=792
 Bucket 96 size=800
 Bucket 97 size=808
 Bucket 98 size=816
 Bucket 99 size=824
 Bucket 100 size=832
 Bucket 101 size=840
 Bucket 102 size=848
 Bucket 103 size=856
 Bucket 104 size=864
 Bucket 105 size=872
 Bucket 106 size=880
 Bucket 107 size=888
 Bucket 108 size=896
 Bucket 109 size=904
 Bucket 110 size=912
 Bucket 111 size=920
 Bucket 112 size=928
 Bucket 113 size=936
 Bucket 114 size=944
 Bucket 115 size=952
 Bucket 116 size=960
 Bucket 117 size=968
 Bucket 118 size=976
 Bucket 119 size=984
 Bucket 120 size=992
 Bucket 121 size=1000
 Bucket 122 size=1008
 Bucket 123 size=1016
 Bucket 124 size=1024
 Bucket 125 size=1032
 Bucket 126 size=1040
 Bucket 127 size=1048
 Bucket 128 size=1056
 Bucket 129 size=1064
 Bucket 130 size=1072
 Bucket 131 size=1080
 Bucket 132 size=1088
 Bucket 133 size=1096
 Bucket 134 size=1104
 Bucket 135 size=1112
 Bucket 136 size=1120
 Bucket 137 size=1128
 Bucket 138 size=1136
 Bucket 139 size=1144
 Bucket 140 size=1152
 Bucket 141 size=1160
 Bucket 142 size=1168
 Bucket 143 size=1176
 Bucket 144 size=1184
 Bucket 145 size=1192
 Bucket 146 size=1200
 Bucket 147 size=1208
 Bucket 148 size=1216
 Bucket 149 size=1224
 Bucket 150 size=1232
 Bucket 151 size=1240
 Bucket 152 size=1248
 Bucket 153 size=1256
 Bucket 154 size=1264
 Bucket 155 size=1272
 Bucket 156 size=1280
 Bucket 157 size=1288
 Bucket 158 size=1296
 Bucket 159 size=1304
 Bucket 160 size=1312
 Bucket 161 size=1320
 Bucket 162 size=1328
 Bucket 163 size=1336
 Bucket 164 size=1344
 Bucket 165 size=1352
 Bucket 166 size=1360
 Bucket 167 size=1368
 Bucket 168 size=1376
 Bucket 169 size=1384
 Bucket 170 size=1392
 Bucket 171 size=1400
 Bucket 172 size=1408
 Bucket 173 size=1416
 Bucket 174 size=1424
 Bucket 175 size=1432
 Bucket 176 size=1440
 Bucket 177 size=1448
 Bucket 178 size=1456
 Bucket 179 size=1464
 Bucket 180 size=1480
 Bucket 181 size=1496
 Bucket 182 size=1512
 Bucket 183 size=1528
 Bucket 184 size=1544
 Bucket 185 size=1560
 Bucket 186 size=1576
 Bucket 187 size=1592
 Bucket 188 size=1608
 Bucket 189 size=1624
 Bucket 190 size=1672
 Bucket 191 size=1720
 Bucket 192 size=1768
 Bucket 193 size=1816
 Bucket 194 size=1864
 Bucket 195 size=1912
 Bucket 196 size=1960
 Bucket 197 size=2008
 Bucket 198 size=2056
 Bucket 199 size=2104
 Bucket 200 size=2152
 Bucket 201 size=2200
 Bucket 202 size=2248
 Bucket 203 size=2296
 Bucket 204 size=2344
 Bucket 205 size=2392
 Bucket 206 size=2440
 Bucket 207 size=2488
 Bucket 208 size=2536
 Bucket 209 size=2584
 Bucket 210 size=2632
 Bucket 211 size=2680
 Bucket 212 size=2728
 Bucket 213 size=2776
 Bucket 214 size=2824
 Bucket 215 size=2872
 Bucket 216 size=2920
 Bucket 217 size=2968
 Bucket 218 size=3016
 Bucket 219 size=3064
 Bucket 220 size=3112
 Bucket 221 size=3160
 Bucket 222 size=3208
 Bucket 223 size=3256
 Bucket 224 size=3304
 Bucket 225 size=3352
 Bucket 226 size=3400
 Bucket 227 size=3448
 Bucket 228 size=3496
 Bucket 229 size=3544
 Bucket 230 size=3592
 Bucket 231 size=3640
 Bucket 232 size=3688
 Bucket 233 size=3736
 Bucket 234 size=3784
 Bucket 235 size=3832
 Bucket 236 size=3880
 Bucket 237 size=3928
 Bucket 238 size=3976
 Bucket 239 size=4024
 Bucket 240 size=4096
 Bucket 241 size=4104
 Bucket 242 size=4120
 Bucket 243 size=8216
 Bucket 244 size=8696
 Bucket 245 size=8704
 Bucket 246 size=8712
 Bucket 247 size=8720
 Bucket 248 size=9368
 Bucket 249 size=9376
 Bucket 250 size=12352
 Bucket 251 size=12360
 Bucket 252 size=16408
 Bucket 253 size=32792
 Bucket 254 size=65560
Total free space   =      360
RESERVED FREE LISTS:
 Reserved bucket 0 size=32
 Reserved bucket 1 size=4400
 Reserved bucket 2 size=8216
 Reserved bucket 3 size=8696
 Reserved bucket 4 size=8704
 Reserved bucket 5 size=8712
 Reserved bucket 6 size=8720
 Reserved bucket 7 size=9368
 Reserved bucket 8 size=9376
 Reserved bucket 9 size=12352
 Reserved bucket 10 size=12360
 Reserved bucket 11 size=16408
 Reserved bucket 12 size=32792
 Reserved bucket 13 size=65560
  Chunk       12d0000088 sz= 13422408  R-free      "               "
  Chunk       1340000088 sz= 13422408  R-free      "               "
  Chunk       13c00000d8 sz= 13422328  R-free      "               "
 Reserved bucket 14 size=127506420
Total reserved free space   = 40267144

問題總結

• 11g中，shared pool被分為7個sub pool，每個sub pool又被分為4個duration，分別保存instance(never freed)，session，cursor，execution等相關信息，並且heap(x,0)是永久的，不會釋放，類型為perm。
• 從dump中也可以看到，當前heap中有3個extent，chunk幾乎均為perm類型和R-類型（R開頭的類型的chunk為保留池chunk）
• 繼續看free list，每個free list中有255個bucket，用於保存不同大小chunk鏈表，由於申請的大小為4160 bytes，則會從bucket 242開始往後尋找合適的free chunk，但是從dump中看，並沒有符合大小的free chunk，並且total free space也僅僅為360 bytes。
• 對於保留池，由於chunk的分配機制，shard pool碎片會越來越多，oracle為了保證大塊內存申請時，不至於找不到連續的空閒空間，便設計了保留池，oracle默認劃分10%作為保留池大小，通過參數shared\_pool\_reserved\_size來控制。
• 由於保留池是為了給較大的chunk準備的，所以保留池裏面只有大於4400的chunk，從dump文件中也可以看到，如果申請的大小小於4400字節，則只能從普通池中尋找free chunk，此處申請的是4160 bytes，所以無法用到保留池。4400這個值可以通過\_shared\_pool\_reserved\_min\_alloc參數調整。

綜上：此ORA-4031是因為heap 6 的duration 0的free space不足導致。

解決方案

1、關閉duration： alter system set "\_enable\_shared\_pool\_durations"=false scope=spfile;

2、擴容shared pool

3、shared\_pool\_reserved\_size，\_shared\_pool\_reserved\_min\_alloc等參數不建議調整，保留默認即可

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service