Oracle error: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT when we try to access the Program in the Program List Page

Document ID : KB000118916
Last Modified Date : 02/11/2018
Show Technical Document Details
Question:
We have a Program with  2000+ subprojects  that causes an Oracle error: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT when we try to access the Program in the Program List Page.

app-ca log shows: 

ERROR 2018-10-17 15:57:34,001 [http-nio-6001-exec-14] niku.xql2 (clarity:0405665165:10781054__75F723C5-C96D-4D78-94CC-6EF9D288F481:projmgr.programs) Exception in processing 
java.lang.Exception: Invalid expression qprogramSums: com.niku.union.persistence.PersistenceException: 
SQL error code: 4036 
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT  
 

How do we resolve this issue?
Answer:
Ask your DBA to get the AWR report, analyze it, and check if you can increase the SGA. Here is an example:

After reviewing AWR report following are the observations.

1. pga_aggregate_limit is set to 6144MB and pga_aggregate_target is set to 1792MB. When the Aggregate target is reached and oracle process needs memory PGA is increase automatically by Oracle and the hard limit for that would be 6144MB which is defined by pga_aggregate_limit.  This can be further observed in the AWR report, following are the snippets.  1

You can see that PGA Usage is between 2594MB and 2914MB even though we have a pga_aggregate_target limit of 1792MB.

2. The other section named “PGA Memory Advisory” will provide you details on PGA values with corresponding DB statistics. Chose the value of PGA which is giving Cache hit ratio closer to 100% and reducing DB time.  

 2
At pga_aggregate_limit of 2509MB we are seeing an improvement in amount of reads and estimated DB time while keeping PGA Cache Hit % close to 100.

3. At the bottom of AWR you have a section for ADDM reports. I’ve observed an Undersized SGA event being logged. Recommendation is to increase the SGA to 25600MB.