Solved

SGA/PGA values Oracle database

  • 3 June 2021
  • 4 replies
  • 372 views

Userlevel 4
Badge +9

Hi

when we receive and IFS build spec it mentions that the SGA/PGA value should be set as a standard. We believe this is set in the Oracle template. One of our customers had an issue where IFS crashed out. After checking the Oracle logs I could see the below error: 

 

</msg>
<msg time='2021-05-28T10:36:42.289+01:00' org_id='oracle' comp_id='rdbms'
client_id='DESKTOP-GN5SK69-WIA-CONSOLE-8040' type='UNKNOWN' level='16'
host_id='IFSPR0DDB' host_addr='fe80::e1e8:57d1:e76c:9814%7' module='DBMS_SCHEDULER'
pid='6528' con_uid='1' con_id='1'
con_name='CDB$ROOT'>
<txt>Errors in file E:\APP\ORACLE\diag\rdbms\ifspcdb\ifspcdb\trace\ifspcdb_j004_6528.trc:
ORA-04031: unable to allocate 32808 bytes of shared memory ("shared pool&quot;,&quot;FND_EVENT_ACTION_API&quot;,&quot;PLMCD^563f5f87&quot;,&quot;BAMIMA: Bam Buffer&quot;)
ORA-06508: PL/SQL: could not find program unit being called: &quot;IFSAPP.FND_EVENT_ACTION_API&quot;
ORA-06512: at &quot;IFSAPP.EVENT_SYS&quot;, line 117
ORA-06512: at &quot;IFSAPP.EVENT_SYS&quot;, line 117
ORA-06512: at &quot;IFSAPP.BACKGROUND_JABS_FAIL_EVU&quot;, line 19
ORA-04088: error during execution of trigger &apos;IFSAPP.BACKGROUND_JABS_FAIL_EVU&apos;
ORA-06512: at &quot;IFSAPP.TRANSACTION_SYS&quot;, line 593
ORA-06512: at &quot;IFSAPP.BATCH_SYS&quot;, line 1946
ORA-04031: unable to allocate 32792 bytes of shared memory (&quot;shared pool&quot;,&quot;UPDATE TRANSACTION_SYS_LOCAL...&quot;,&quot;SQLA^5c7b331e&quot;,&quot;pdy3M63_Finish_Page_Table: Copy data seg&quot;)
ORA-06512: at &quot;IFSAPP.TRANSACTION_SYS&quot;, line 1093
ORA-06512: at &quot;IFSAPP.TRANSACTION_SYS&quot;, line 921
ORA-04031: unable to allocate 32808 bytes of shared memory (&quot;shared pool&quot;,&quot;FND_EVENT_ACTION_API&quot;,&quot;PLMCD^563f5f87&quot;,&quot;BAMIMA: Bam Buffer&quot;)
ORA-06508: PL/SQL: could not find program unit being called: &quot;IFSAPP.FND_EVEN
</txt>
</msg>
 

I have spoken to our dba guys and they believe that the SGA/PGA was explicitly set to 4GB when the RAM allowed upto 40GB. 

My question is should IFS provide this value for each customer instance that we build? Also how is this value calculated? Can it be added to the sizing guide? 

Regards

Gary 

icon

Best answer by Ragaventhan Sathananda 3 June 2021, 19:16

View original

This topic has been closed for comments

4 replies

Userlevel 7
Badge +15

Hi @crpgaryw 

First we set the value of the SGA and PGA as per the template value as you said and then depending on the customer data volume you need to tune those parameters. You may use the oracle memory advisories to fine tune the values for the parameters depending on the data volume of the database. 

Hope this helps you.

Userlevel 4
Badge +9

Hi @crpgaryw 

First we set the value of the SGA and PGA as per the template value as you said and then depending on the customer data volume you need to tune those parameters. You may use the oracle memory advisories to fine tune the values for the parameters depending on the data volume of the database. 

Hope this helps you.

Hi Ragaventhan,

thanks for the response. Do you have a guide on this? It sounds to me like this should be a DBA administrator task. 

Regards

Gary 

Userlevel 7
Badge +15

Hi @crpgaryw 

There no such guide for it. You may refer the Oracle memory advisory to adjust the values. Exactly it is a DBA task. 

Userlevel 6
Badge +13

@crpgaryw ,

Hope you  can refer the below  as well regarding the memory management.