某银行客户近期频繁出现ORA-04031，报错如下:
|
1 2 3 4 5 6 7 8 9 |
[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查看该子堆与父堆的信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
****************************************************** 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如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
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 8938A048 161BEF84 ECDBBD88 2D39B55E [H.8.........^.9-] 1933B5E200 33B5E2A0 00000019 00000010 00000000 [...3............] 1933B5E210 00000004 00000000 00000000 00000000 [................] 1933B5E220 00000000 00000000 00000000 00000000 [................] Repeat 6 times 1933B5E290 00000000 00000000 [........] Chunk 1933b5e298 sz= 928 freeable "qeeRwo: qeeCrea" ... FREE LISTS: Bucket 0 size=24 Chunk 1a0df950c8 sz= 0 kghdsx Bucket 1 size=88 Bucket 2 size=152 Bucket 3 size=280 Bucket 4 size=536 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 8938A048 161BEF84 ECDBBD88 2D39B55E [H.8.........^.9-] 1933B5E200 33B5E2A0 00000019 00000010 00000000 [...3............] 1933B5E210 00000004 00000000 00000000 00000000 [................] 1933B5E220 00000000 00000000 00000000 00000000 [................] Repeat 6 times 1933B5E290 00000000 00000000 [........] Bucket 5 size=1048 Bucket 6 size=2072 Bucket 7 size=4120 Bucket 8 size=4144 Bucket 9 size=4168 Bucket 10 size=4192 Bucket 11 size=4216 Total free space = 704 |
请求940字节，但是子堆free list只有704 byte，所以这时会像父堆申请一个子堆extent size的chunk 即4072 bytes，但是trace中并未发现父堆的详细信息。
|
1 2 3 4 |
Did Not dump SGA Heap desc=0x600013d0. Current time: 10/26/2021 04:13:50 Last SGA heap dump at 10/26/2021 04:13:38 SGA Heap Dump Count = 2 |
查看subpool的top组件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 |
============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1 ---------------------------------------------- "ksfqpn " 430 MB 30% "gcs resources " 375 MB 26% "gcs shadows " 207 MB 14% "db_block_hash_buckets " 100 MB 7% "free memory " 93 MB 6% "init_heap_kfsg " 48 MB 3% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% "object queue hash buckets " 14 MB 1% "Checkpoint queue " 13 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 1 ---------------------------------------------- "ksfqpn " 430 MB 26% "gcs resources " 375 MB 23% "gcs shadows " 207 MB 13% "SQLA " 132 MB 8% "free memory " 114 MB 7% "db_block_hash_buckets " 100 MB 6% "init_heap_kfsg " 48 MB 3% "KGLH0 " 46 MB 3% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% ----------------------------------------- free memory 207 MB memory alloc. 2880 MB Sub total 3086 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2 ---------------------------------------------- "ksfqpn " 430 MB 29% "gcs resources " 375 MB 25% "gcs shadows " 207 MB 14% "db_block_hash_buckets " 102 MB 7% "free memory " 92 MB 6% "SQLA " 36 MB 2% "FileOpenBlock " 24 MB 2% "KGLH0 " 20 MB 1% "gcs res hash bucket " 16 MB 1% "dbktb: trace buffer " 15 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 2 ---------------------------------------------- "ksfqpn " 430 MB 26% "gcs resources " 375 MB 22% "gcs shadows " 207 MB 12% "SQLA " 168 MB 10% "free memory " 114 MB 7% "db_block_hash_buckets " 102 MB 6% "KGLH0 " 52 MB 3% "FileOpenBlock " 24 MB 1% "gcs res hash bucket " 16 MB 1% "dbktb: trace buffer " 15 MB 1% ----------------------------------------- free memory 206 MB memory alloc. 2944 MB Sub total 3150 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3 ---------------------------------------------- "ksfqpn " 430 MB 29% "gcs resources " 375 MB 25% "gcs shadows " 209 MB 14% "free memory " 109 MB 7% "db_block_hash_buckets " 101 MB 7% "SQLA " 20 MB 1% "gc name table " 18 MB 1% "gcs res hash bucket " 16 MB 1% "dbktb: trace buffer " 15 MB 1% "KGLH0 " 14 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 3 ---------------------------------------------- "ksfqpn " 430 MB 26% "gcs resources " 375 MB 22% "gcs shadows " 209 MB 12% "free memory " 136 MB 8% "SQLA " 133 MB 8% "db_block_hash_buckets " 101 MB 6% "KGLH0 " 47 MB 3% "KQR L PO " 23 MB 1% "gc name table " 18 MB 1% "gcs res hash bucket " 16 MB 1% ----------------------------------------- free memory 245 MB memory alloc. 2908 MB Sub total 3153 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4 ---------------------------------------------- "ksfqpn " 430 MB 29% "gcs resources " 375 MB 25% "gcs shadows " 210 MB 14% "free memory " 111 MB 7% "db_block_hash_buckets " 100 MB 7% "SQLA " 44 MB 3% "KGLH0 " 22 MB 1% "gcs res hash bucket " 16 MB 1% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 4 ---------------------------------------------- "ksfqpn " 430 MB 25% "gcs resources " 375 MB 22% "gcs shadows " 210 MB 12% "SQLA " 176 MB 10% "free memory " 131 MB 8% "db_block_hash_buckets " 100 MB 6% "KGLH0 " 56 MB 3% "gcs res hash bucket " 16 MB 1% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% ----------------------------------------- free memory 242 MB memory alloc. 2971 MB Sub total 3213 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5 ---------------------------------------------- "ksfqpn " 430 MB 30% "gcs resources " 375 MB 26% "gcs shadows " 210 MB 15% "db_block_hash_buckets " 101 MB 7% "free memory " 96 MB 7% "SQLA " 17 MB 1% "KGLH0 " 15 MB 1% "KQR X SO " 15 MB 1% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 5 ---------------------------------------------- "ksfqpn " 430 MB 26% "gcs resources " 375 MB 23% "gcs shadows " 210 MB 13% "SQLA " 129 MB 8% "free memory " 115 MB 7% "db_block_hash_buckets " 101 MB 6% "KGLH0 " 46 MB 3% "KQR X SO " 38 MB 2% "KQR X PO " 18 MB 1% "dbktb: trace buffer " 15 MB 1% ----------------------------------------- free memory 211 MB memory alloc. 2879 MB Sub total 3090 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6 ---------------------------------------------- "ksfqpn " 430 MB 30% "gcs resources " 375 MB 26% "gcs shadows " 208 MB 14% "db_block_hash_buckets " 100 MB 7% "free memory " 92 MB 6% "keomg: entry list " 36 MB 3% "ges big msg buffers " 30 MB 2% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% "object queue hash buckets " 14 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 6 ---------------------------------------------- "ksfqpn " 430 MB 23% "gcs resources " 375 MB 20% "free memory " 233 MB 12% "gcs shadows " 208 MB 11% "PMR sga heap " 134 MB 7% "SQLA " 128 MB 7% "db_block_hash_buckets " 100 MB 5% "KGLH0 " 47 MB 2% "keomg: entry list " 36 MB 2% "ges big msg buffers " 30 MB 2% ----------------------------------------- free memory 325 MB memory alloc. 3021 MB Sub total 3346 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 7 ---------------------------------------------- "ksfqpn " 430 MB 29% "gcs resources " 375 MB 25% "gcs shadows " 207 MB 14% "db_block_hash_buckets " 101 MB 7% "free memory " 94 MB 6% "FileOpenBlock " 24 MB 2% "KQR L PO " 18 MB 1% "SQLA " 16 MB 1% "dbktb: trace buffer " 15 MB 1% "ASH buffers " 14 MB 1% ============================================== TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 7 ---------------------------------------------- "ksfqpn " 430 MB 23% "gcs resources " 375 MB 20% "gcs shadows " 207 MB 11% "free memory " 204 MB 11% "SQLA " 138 MB 7% "PMR sga heap " 134 MB 7% "db_block_hash_buckets " 101 MB 5% "KGLH0 " 47 MB 2% "FileOpenBlock " 24 MB 1% "KQR L PO " 18 MB 1% ----------------------------------------- free memory 298 MB memory alloc. 3069 MB Sub total 3367 MB TOTALS --------------------------------------- Total free memory 1734 MB Total memory alloc. 20 GB Grand total 22 GB |
发现组件ksfqpn的内存占用异常，gcs resources和gcs shadows也占用偏高，ksfqpn与io接口相关，ksfq – kernel service functions sequential file io interface，通过查阅mos文档发现与Bug 25058080 Excessive allocation of memory “ksfqpn” in SGA using RMAN backups in Exadata匹配。
其bug主要原因是由于内部算法导致ksfqpn内存重用和回收出现了异常。导致ksfqpn大小是x$ksfgp的1000倍以上