首页 » Cloud, ORACLE 9i-23ai » Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache

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

  • 2020/11/06
  • Cloud, ORACLE 9i-23ai
  • 393 views
  • Troubleshooting 12c ora-4031 “ges resource dynamic” lot of FB resource cache已关闭评论

Troubleshooting ORA-04031: unable to allocate 13840 bytes of shared memory “ges resource dynamic” in 12C+

记录过几个导致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

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：