ORA-00039: error during periodic action ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Caused By: java.sql.SQLException: ORA-00039: error during periodic action ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

We see these errors frequently in oracle 12c databases,  PGA_AGGREGATE_LIMIT can be set manually or by default and its a limiting value on the aggregate PGA memory that instance can use max.
the sessions (running untunable DMLs) using the most memory will be terminated and the above said errors get reported.

An incident is a single occurrence of a problem, an incident is created for each problem occurrence

The sessions that experience ORA-04036:  errors will get terminated and these sessions current sql statements can be seen in trace files.


What is the fastest solution to this?

We either run one of the below alter to solve

alter system set pga_aggregate_limit=0 scope=both;

or

alter system set pga_aggregate_limit=10G scope=both;

Does it solves the problem permanently?  I dont think it will.. so what should we do to solve this problem . there are two ways either DML producers (Developers) tune their queries, if they say something is wrong with db  .. then as  DBA has to dig deep down to find the DML statements that causing issues...

here it is one of the method i use to find the queries that cause these issues..

We have a good tool known as ADRCI

login into adrci

adrci> show incidents

Find incidents that are causing 04036 issues

show incident -mode detail -p "incident_id=XXXX"

it shows the incident files with KEY INCIDENT_FILE, these are actually trace files..

Get the trace file names and open trace files to see causes.... the trace files shows more than required info.. session id, time, everything .. more importantly it shows  like

----- Current SQL Statement for this session (sql_id=xxxxxxxxxxxxxxxxx) -----
 we get not just SQL_ID but whole SQL statements that caused these issues... what else we want,.. let  developers know DML statements     and or  get these statements tuned..

 Hope it helps u all..

 Open for suggestions and comments.