点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!!
问题现象
2022-03-03T14:46:54.069097+08:00 Errors in file
/u01/app/oracle/diag/rdbms/bbccdd/BBCCDD2/trace/BBCCDD2_ora_
315565.trc (incident=8217088) (PDBNAME=DDMMPP): ORA-04031:
unable to allocate 3632 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","KGLHD")
DDMMPP(8):Use ADRCI or Support Workbench to package the
incident. See Note 411.1 at My Oracle Support for error and
packaging details. Errors in file
/u01/app/oracle/diag/rdbms/bbccdd/BBCCDD2/trace/BBCCDD2_ora_
315565.trc (incident=8217089) (PDBNAME=DDMMPP): ORA-04031:
unable to allocate 3632 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","KGLHD") ORA-04031:
unable to allocate 3632 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","KGLHD")
ERROR: ORA-04031: unable to allocate 3632 bytes of shared
memory ("sharedpool","unknown object","sga
heap(1,0)","KGLHD") ORA-04031: unable to allocate 3632 bytes
of shared memory ("sharedpool","unknown object","sga
heap(1,0)","KGLHD")
问题分析
SUBPOOL Bytes MBytes
------------------------------ ---------------- --------
shared pool (1): 2,684,354,560 2,560
shared pool (2): 19,327,352,832 18,432
shared pool (3): 2,147,483,648 2,048
shared pool (4): 2,684,354,560 2,560
shared pool (5): 10,737,418,240 10,240
shared pool (6): 2,684,354,560 2,560
shared pool (7): 2,684,354,560 2,560
shared pool (Total): 42,949,672,960 40,960
可以看到,shared pool 共 40G,划分了 7 个 subpool,其中第 2 个和第 5 个 subpool 共占用了 70%,导致其他 subpool 仅分配到 2G 左右内存。
SUBPOOL NAME Bytes MBytes
------------------ -------- ------------ -----------
shared pool (1): KGLHD 8,269,776 7.89
shared pool (2): KGLHD 360,131,768 343.45
shared pool (3): KGLHD 20,219,616 19.28
shared pool (4): KGLHD 22,442,928 21.40
shared pool (5): KGLHD 141,734,720 135.17
shared pool (6): KGLHD 12,913,608 12.32
shared pool (7): KGLHD 24,109,944 22.99
很明显,KGLHD 只在subpool 2 和 5 中分配到了充足的资源,出问题的subpool 1 中,占用甚至不足 10M。
SUBPOOL NAME Bytes MBytes
----------------- -------------------------- ------------- --------
shared pool (1): keomg: entry list 604,054,352 576.07
shared pool (1): free memory 534,204,528 509.46
shared pool (1): gcs resources 291,444,832 277.94
shared pool (1): KSRMA State Object 170,908,840 162.99
shared pool (1): gcs shadows 158,311,416 150.98
shared pool (1): ksunfy_meta 1 117,650,192 112.20
shared pool (1): file queue buckets 89,878,528 85.71
shared pool (1): gc name table 67,108,864 64.00
shared pool (1): ges enqueues 61,721,488 58.86
shared pool (1): SO private sga 59,555,768 56.80
继续检查这 500M 左右的 "free memory",发现绝大部分是 R-free 状态,只有 80M 是真正的 free 状态,而且基本没有大块内存, 例如 3-4K 区间仅剩余 15M。
SubPool# SGA_HEAP Chunk Comment size COUNT(*) STATUS KBYTES
-------- -------------- --------------- ----- ---------- -------- -------
1 sga heap(1,0) free memory 0-1K 622 R-free 87
1 sga heap(1,0) free memory 1-2K 506 R-free 486
1 sga heap(1,0) free memory 2-3K 503 R-free 1,005
1 sga heap(1,0) free memory 3-4K 210 R-free 619
1 sga heap(1,0) free memory 4-5K 205 R-free 815
1 sga heap(1,0) free memory 5-6k 14 R-free 71
1 sga heap(1,0) free memory 6-7k 17 R-free 104
1 sga heap(1,0) free memory 7-8k 11 R-free 77
1 sga heap(1,0) free memory 8-9k 33 R-free 271
1 sga heap(1,0) free memory 9-10k 15 R-free 136
1 sga heap(1,0) free memory > 10K 792 R-free 449,804
1 sga heap(1,0) free memory 0-1K 10115 free 2,110
1 sga heap(1,0) free memory 1-2K 16605 free 17,676
1 sga heap(1,0) free memory 2-3K 15479 free 29,485
1 sga heap(1,0) free memory 3-4K 5981 free 16,696
到此,可以做出阶段性结论:
SUBPOOL NAME Bytes MBytes
----------------- ------------------- -------------- -----------
shared pool (1): keomg: entry list 604,088,144 576.10
shared pool (1): KGLH0 12,842,408 12.25
shared pool (1): KGLHD 8,170,336 7.79
shared pool (2): SQLA 7,561,449,328 7,211.16
shared pool (2): KGLH0 2,564,622,552 2,445.81
shared pool (2): KGLHD 366,014,472 349.06
shared pool (3): SQLA 374,480,288 357.13
shared pool (3): KGLH0 107,810,984 102.82
shared pool (3): KGLHD 16,761,536 15.99
shared pool (4): SQLA 456,668,944 435.51
shared pool (4): KGLH0 176,545,616 168.37
shared pool (4): KGLHD 25,405,536 24.23
shared pool (5): SQLA 4,575,963,200 4,363.98
shared pool (5): KGLH0 1,413,732,672 1,348.24
shared pool (5): KGLHD 153,350,928 146.25
shared pool (6): SQLA 126,857,624 120.98
shared pool (6): KGLH0 98,979,456 94.39
shared pool (6): KGLHD 19,056,792 18.17
shared pool (7): SQLA 407,530,136 388.65
shared pool (7): KGLH0 201,948,440 192.59
shared pool (7): KGLHD 34,028,216 32.45
SQL_ID SHARABLE_MEM SHARE_MBYTES PERM_MBYTES VERSION_COUNT LOADED_VERSIONS EXECUTIONS CON_ID
------------- ------------ -------------- -------------- ------------- --------------- ---------- ------
7d0t2fv5n66wn 79202250 75.5 30.0 39 1039 76922 16
bdrzph2y51sfh 102235921 97.5 56.1 45 1095 12716 11
5jaak0rx5422v 270332732 257.8 127.9 7 1607 7416 17
9wt8h6xdb0j8v 331135193 315.8 181.7 24 3548 34335 14
872qtktva0rnb 351625657 335.3 136.8 47 3847 374426 5
fymtpyuk43skn 759622923 724.4 374.6 7 7139 89121 9
00d1sjdu6sng9 -1.272E+09 -1,213.5 1,800.8 24 23174 615520 18
1n2f602g64zkf 2539148612 2,421.5 2,627.6 38 84238 1003418 11
INST_ID CON_ID ADDRESS FIRST_LOAD_TIME OBSOLETED COUNT(*)
------- ------ ---------------- -------------------------------------- ---------- ----------
2 11 00000000B0437910 2022-03-04/02:28:55 Y 50
2 11 000000063F6CD4E0 2022-03-04/02:28:57 Y 50
2 11 000000060F91EBA8 2022-03-04/02:28:59 Y 50
2 11 0000000338025B98 2022-03-04/02:29:01 Y 50
2 11 000000060C00D938 2022-03-04/02:29:02 Y 50
2 11 0000000819237980 2022-03-04/02:29:03 Y 50
2 11 00000005F7E44578 2022-03-04/02:29:05 Y 50
2 11 00000005F659BA30 2022-03-04/02:29:07 Y 50
2 11 00000005F0756210 2022-03-04/02:29:09 Y 50
2 11 00000005EC35B6E8 2022-03-04/02:29:10 Y 50
2 11 00000005F1F88D68 2022-03-04/02:29:11 Y 50
2 11 00000005DEA7D5E0 2022-03-04/02:29:12 Y 50
2 11 000000060B0A0640 2022-03-04/02:29:13 Y 50
2 11 00000005DC69FDC0 2022-03-04/02:29:14 Y 50
2 11 00000005D29247C8 2022-03-04/02:29:15 Y 50
2 11 00000005D75B86F8 2022-03-04/02:29:16 Y 50
2 11 00000005CAE61778 2022-03-04/02:29:17 Y 50
2 11 00000005C74D7398 2022-03-04/02:29:20 Y 50
2 11 00000005BB31A998 2022-03-04/02:29:21 Y 50
2 11 00000005C77472C8 2022-03-04/02:29:22 Y 50
2 11 000000059590ECB8 2022-03-04/02:29:23 Y 50
2 11 00000005B98B8710 2022-03-04/02:29:24 Y 50
2 11 00000004AE0D81E0 2022-03-04/02:29:26 Y 50
2 11 00000004F05D5A50 2022-03-04/02:29:26 Y 50
2 11 000000063CB0F1F0 2022-03-04/02:29:27 Y 50
2 11 000000048BD43C08 2022-03-04/02:29:28 Y 50
2 11 0000000495C193C8 2022-03-04/02:29:28 Y 50
2 11 0000000590FA8CD8 2022-03-04/02:29:29 Y 50
2 11 0000000436DA25C0 2022-03-04/02:29:30 Y 50
2 11 0000000449408CA0 2022-03-04/02:29:30 Y 50
2 11 00000003FD8225C8 2022-03-04/02:29:31 Y 50
2 11 00000004193CCAF8 2022-03-04/02:29:31 Y 50
2 11 000000037CE6B3F0 2022-03-04/02:29:32 Y 50
2 11 00000003CB204990 2022-03-04/02:29:32 Y 50
2 11 00000003EBB13878 2022-03-04/02:29:32 Y 50
2 11 000000032B937650 2022-03-04/02:29:33 Y 50
2 11 00000003394727C8 2022-03-04/02:29:33 Y 50
2 11 0000000367AC8AD0 2022-03-04/02:29:33 Y 50
2 11 000000033947A208 2022-03-04/02:29:34 Y 50
2 11 00000002CDAD8E50 2022-03-04/02:29:35 Y 50
2 11 0000000237C7CC70 2022-03-04/02:29:36 Y 50
2 11 0000000271458618 2022-03-04/02:29:36 Y 50
2 11 000000017D9C3090 2022-03-04/02:29:37 Y 50
2 11 00000001D5990058 2022-03-04/02:29:37 Y 50
2 11 00000000BF30CBD0 2022-03-04/02:29:38 Y 50
SQL_ID ADDRESS CHILD_NUMBER CHILD_ADDRESS REASON
------------- ---------------- ------------ ---------------- -----------------
1n2f602g6rzkf 000000063F6CD4E0 0 0000000168575E98 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 1 0000000168575C68 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 2 00000004F2318760 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 3 00000004F2318530 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 4 00000004F2318300 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 5 000000069F22B380 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 6 000000069F22B150 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 7 000000069F22AF20 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 8 00000005964A1FE0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 9 00000005964A1DB0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 10 00000005964A1B80 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 11 000000061BFE2888 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 12 00000006161D6358 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 13 00000006162FB108 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 14 00000006162FA8B8 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 15 0000000617CE3200 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 16 0000000614DFBC20 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 17 00000006146BEB68 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 18 0000000611CD1D40 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 19 0000000617C50140 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 20 0000000617C4FCE0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 21 0000000611530530 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 22 000000061152ECE0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 23 00000006115842E0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 24 0000000611583C88 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 25 0000000615FD5088 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 26 00000006163239C8 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 27 000000061834F528 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 28 000000061834F0C8 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 29 000000081B05F0E8 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 30 00000004BD96AD08 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 31 0000000610AB1748 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 32 000000061117E1C0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 33 000000061016E1B0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 34 000000081B05EC70 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 35 000000060F908A00 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 36 00000006153F2678 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 37 000000081C0DC7C0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 38 000000081563E768 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 39 0000000610AB5D00 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 40 000000060FEFF868 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 41 000000061A3FB9B0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 42 000000080DBAE1E0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 43 000000022BB30E88 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 44 000000060FEFEFE0 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 45 000000023B46F420 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 46 00000006151D7A50 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 47 000000063841C690 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 48 0000000637FF2700 Bind mismatch(8)
1n2f602g6rzkf 000000063F6CD4E0 49 000000061016DB58 Bind mismatch(8)
CHILD# ADDRESS POSITION MAX_LENGTH DATATYPE BIND_NAME DATATYPE_STRING BIND_MISMATCH
------ ---------------- ---------- ---------- ------------- ---------- ---------------- --------------
26 00000001D62E1140 18 22 NUMBER :18 NUMBER Y
27 00000002D00CDE60 18 32 VARCHAR2 :18 VARCHAR2(32) Y
36 00000001D626D0A0 18 22 NUMBER :18 NUMBER Y
37 00000001DE4B2AD8 18 32 VARCHAR2 :18 VARCHAR2(32) Y
INSERT INTO xxxxx.TTT_EXT(DEAL_DATE,BOSS_CUST_ID,CUST_ID,CUST_NAME,SHORT
_NAME,ADDR,PHONE,GROUP_NUM,POST_CODE,WEB,FAX,GROUP_EMAIL,OWN
ER_NAME,DUMMY,CUST_STATE,STATE,GROUP_LEVEL,STRU_CODE,STRU_CO
DE2,STRU_CODE3,AREA_LEVEL,IS_INSIDE,STRU_LEVEL,PARENT_GROUP,
CARP_LEVEL,VALUE_LEVEL,IS_IMPORENT,CREATE_DATE,DONE_DATE,CRE
ATE_ID,OP_ID,CUST_TYPE,CUST_TYPE2,IS_QUALIFIED,SERVICE_LEVEL
,OFFICE_BUILD_NAME,PROFESS_MARKET_NAME,BUILDING_MARKET_NUM,I
NDUSTRY_ZONE_NAME,INDUSTRY_ZONE_NUM,PROFESS_MARKET_NUM,GROUP
_RESIDENT,GROUP_ANNUAL_TURNOVER,GROUP_SIZE,GROUP_CUST_LEVEL_
2016,MOD_SUPER_UNIT,OP_COUNT,ARTI_PON_COVER,EXT_4,INFO_PRODU
CT_BUDGET,STAFF_COM_BUDGET,ARTI_TRANSFER_COVER,ARTI_OPTICAL_
COVER
,COVER_END_DATE,COVER_TYPE) VALUES
(: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)
分析结论
但当前环境的应用使用绑定变量不够规范,即变量类型不确定,导致游标共享失败,超出阈值过高的子游标数量引发父游标的大量频繁重建,带来以下影响:
解决方案:
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129364.html
Oracle数据库4031故障分析 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; m...
摘要:薛定谔的猫以上内容是前提,友情提示,如你有理解模糊之处,请先阅读对应的文章。这个例子告诉大家薛定谔的猫混入了的字典中,而且答案是,打开笼子,这只猫就会死亡。 showImg(https://segmentfault.com/img/remote/1460000019217212?w=1880&h=1253); 本文原创并首发于公众号【Python猫】,未经授权,请勿转载。原文地址:ht...
摘要:只有当超时故障扇区等明确故障项出现后,两者关联才确诊硬盘故障,否则只是隔离观察,不报修。如果存在进程住时间超过分钟,我们认为这个硬盘故障的影响面已扩大到了整机,需要进行重启消除影响。 随着阿里大数据产品业务的增长,服务器数量不断增多,IT运维压力也成比例增大。各种软、硬件故障而造成的业务中断,成为稳定性影响的重要因素之一。本文详细解读阿里如何实现硬件故障预测、服务器自动下线、服务自愈以...
摘要:的解析与执行过程第一个弹出函数体第二个弹出函数体第三个弹出第四个弹出第五个弹出最后一行报错执行分析第行,没有关键字,不解析第行,遇到关键字,解析到全局的头部第行,没有关键字,不解析第行,遇到关键字,解析到全局的头部第行,没有关键 js的解析与执行过程 showImg(https://segmentfault.com/img/bVbe2UQ?w=1744&h=782); alert(a)...
阅读 1347·2023-01-11 13:20
阅读 1685·2023-01-11 13:20
阅读 1133·2023-01-11 13:20
阅读 1860·2023-01-11 13:20
阅读 4101·2023-01-11 13:20
阅读 2705·2023-01-11 13:20
阅读 1386·2023-01-11 13:20
阅读 3598·2023-01-11 13:20