案例：ORA-04031 12.1.0.2 on exadata x7

点击蓝字 关注我们

某银行客户近期频繁出现ORA-04031，报错如下:

[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/coredb/coredb1/trace/coredb1_ora_291223.trc
[TOC00001]
ORA-04031: unable to allocate 920 bytes of shared memory ("shared pool","SELECT
        ACC...","SQLA^559c65a1","qeeRwo: qeeCreateRwo")
[TOC00001-END]

此次报错的subheap为SQLA^559c65a1，通过trace查看该子堆与父堆的信息。

******************************************************
HEAP DUMP heap name="sga heap"  desc=0x600013d0
 extent sz=0x4d3a8 alt=272 het=32767 rec=9 flg=130 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x1 heap=(nil)
 fl2=0x60, nex=(nil) 
 pdb id=0
 ds for latch 1: 0x600884c8                                 
 ds for latch 2: 0x60092218                                 
 ds for latch 3: 0x6009bf68                                 
 ds for latch 4: 0x600a5cb8                                 
 ds for latch 5: 0x600afa08                                 
 ds for latch 6: 0x600b9758                                 
 ds for latch 7: 0x600c34a8                                 
 reserved granule count 0 (granule size 33554432)
******************************************************
HEAP DUMP heap name="SQLA^559c65a1"  desc=0x192d7c9b00
 extent sz=0xfe8 alt=32767 het=456 rec=0 flg=2 opc=2
 parent=0x600013d0 owner=0x192d7c99b8 nex=(nil) xsz=0x2000000 heap=(nil)
 fl2=0x27, nex=(nil), dsxvers=1, dsxflg=0x0
 dsx first ext=0x1a0df95090
 dsx empty ext bytes=0  subheap rc link=0x1a0df95100,0x1a0df95100
 pdb id=0
 Subheap has 85360 bytes of memory allocated

可以看到该子堆已经分配了85360 bytes，extent大小为4072。

根据chunk的分配原则，首先尝试在子堆中分配，如果子堆free list无法分配，则向父堆申请。这时申请的chunk大小将会是extent size，并作为一个新的extent挂在子堆下。

查看子堆的free list如下：

HEAP DUMP heap name="SQLA^559c65a1"  desc=0x192d7c9b00
 extent sz=0xfe8 alt=32767 het=456 rec=0 flg=2 opc=2
 parent=0x600013d0 owner=0x192d7c99b8 nex=(nil) xsz=0x2000000 heap=(nil)
 fl2=0x27, nex=(nil), dsxvers=1, dsxflg=0x0
 dsx first ext=0x1a0df95090
 dsx empty ext bytes=0  subheap rc link=0x1a0df95100,0x1a0df95100
 pdb id=0
EXTENT 0 addr=0x1933b5dfc8
  Chunk       1933b5dfd8 sz=      704    free      "               "
Dump of memory from 0x0000001933B5DFD8 to 0x0000001933B5E298
1933B5DFD0                   000002C1 C0B38F00          [........]
1933B5DFE0 00000000 00000000 2D7C9BD8 00000019  [..........|-....]
1933B5DFF0 2D7C9BD8 00000019 01010101 00000000  [..|-............]
1933B5E000 00000000 00000000 0EB447B8 00000000  [.........G......]
1933B5E010 00000000 00000070 00000001 C0B38F00  [....p...........]
1933B5E020 00000000 00000000 33B5E948 00000019  [........H..3....]
1933B5E030 2F36F320 00000019 00000001 00000000  [ .6/............]
1933B5E040 33B5DFE0 00000019 00000000 00000000  [...3............]
1933B5E050 33B5E050 00000019 33B5E050 00000019  [P..3....P..3....]
1933B5E060 000008D9 40B38F00 33B5DFF0 00000019  [.......@...3....]
1933B5E070 33B5DFF0 00000019 0EBBECFC 00000000  [...3............]
1933B5E080 000002A0 00000328 9DF614B8 00000019  [....(...........]
1933B5E090 33B5E200 00000019 00000000 00000000  [...3............]
1933B5E0A0 2F36F258 00000019 00000000 00000000  [X.6/............]
1933B5E0B0 00000000 00000000 00000000 00000000  [................]
1933B5E0C0 33B5E150 00000019 00000000 00000000  [P..3............]
1933B5E0D0 00000000 00000000 00000000 00000000  [................]
        Repeat 7 times
1933B5E150 33B5E300 00000019 00000000 00000000  [...3............]
1933B5E160 00000000 00040340 00000000 00000000  [....@...........]
1933B5E170 00000000 00000000 33B5E088 00000019  [...........3....]
1933B5E180 00000000 00000000 00000004 00000000  [................]
1933B5E190 00000000 00000000 D4881520 0000001A  [........ .......]
1933B5E1A0 00000006 00000010 00000000 00000000  [................]
1933B5E1B0 00000000 00000000 00000000 00000000  [................]
1933B5E1C0 000018CA FFFFFFFF 00000000 00000000  [................]
1933B5E1D0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
1933B5E1F0 893