Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache

微信扫一扫,分享到朋友圈

Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache

记录过几个导致SGA中“ges resource dynamic”逐渐增大的问题,这里又在12c遇到了一个ora-4031问题,不太符合那里的描述和已知bug,  这里是在v$ges_resource中大量的FB资源的cache,这里简单记录。

oracle@anbob1:/home/oracle/support> cat > pro.sc < show incident -mode detail -p "problem_id=7"
> EOF
oracle@anbob1:/home/oracle/support> adrci script=pro.sc|grep -E "ERROR_ARG1|ERROR_ARG2|ERROR_ARG3|ERROR_ARG4|ERROR_ARG5"|grep -vE "ERROR_ARG10|ERROR_ARG11|ERROR_ARG12"
CREATE_TIME                   2020-11-05 04:21:18.797000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
CREATE_TIME                   2020-11-05 04:20:21.774000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
CREATE_TIME                   2020-11-05 04:20:11.215000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
CREATE_TIME                   2020-11-04 02:37:28.203000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
CREATE_TIME                   2020-11-04 02:37:24.067000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
CREATE_TIME                   2020-11-04 02:37:04.207000 +08:00
ERROR_ARG1                    13840
ERROR_ARG2                    shared pool
ERROR_ARG3                    unknown object
ERROR_ARG4                    sga heap(5,0)
ERROR_ARG5                    ges resource dynamic
SQL> select inst_id,name,round(bytes/1024/1024/1024,1) in_gb from gv$sgastat where name='ges resource dynamic';
INST_ID NAME                            IN_GB
---------- -------------------------- ----------
1 ges resource dynamic              4.5
2 ges resource dynamic              5.8
SQL> select * from (
select substr(resource_name,instr(resource_name,'[',1,3)+1,2),master_node,count(*)
from gv$ges_resource
group by substr(resource_name,instr(resource_name,'[',1,3)+1,2),master_node
order by 3 desc)
where rownum<11;
SU MASTER_NODE   COUNT(*)
-- ----------- ----------
FB           1   13832551
FB           2    1418685
BL           2    1187476
BL           1    1106200
QQ           1      60828
QQ           2      60687
HW           1      38000
QC           1      28596
QI           1      24565
QI           2      24388
10 rows selected.
# You can use the following query to find the top enq:
SQL> select count(*) cnt,
2  regexp_replace(resource_name2, '([^,])*,([^,]*),([^,]*)', '\3')   ges_type
3  from v$ges_enqueue
4  group by
5  regexp_replace(resource_name2, '([^,])*,([^,]*),([^,]*)', '\3') order by 1 desc ;
CNT GES_TYPE
---------- ----------
7125283 FB
2541211 BL
19001 HW
12298 YH
12297 VH
8512 WR
8432 WL
5137 VV
4429 AE
3251 AF
2578 TS
2440 CR
1607 TM
1261 GA
1259 EA
1174 MR
...

FB ==>Format Block
通常是insert等批量格式化数据库块。FB类型的GES资源无需CACHE(Bug 29922435).

SQL> @pd ges_dire
Show all parameters and session values from x$ksppi/x$ksppcv...
INDX I_HEX NAME                                               VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------- ------------------------------ ----------------------------------------------------------------------
1086   43E _ges_direct_free                                   FALSE                          if TRUE, free each resource directly to the freelist
1089   441 _ges_direct_free_res_type                          ARAH                           string of resource types(s) to directly free to the freelist

解决方案

disable FB cache, like ‘BB’, Increase according to the type of cache,set the following:

“_ges_direct_free_res_type”=’CTARAHDXBBFB’

If there are more enq which are growing then please set the following:

_ges_direct_free=true

微信扫一扫,分享到朋友圈

Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache

学 .NET5 从 Fur 开始,1.0.0-rc.final.84 发布

上一篇

MeterSphere开发者手册

下一篇

你也可能喜欢

Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache

长按储存图像,分享给朋友