Hi All,
We are recently upgraded our database to oracle 12c and we have assigned 50GB to PGA_AGGREGATE_LIMIT.
Actually when we ran a batch job with more parallel session around 100 - 200 connections failed with stale connection issue . we found that the ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT and the sessions are terminated .
we investigated further ,we found the sessions are not releasing the memory its holding even its become inactive . when we query the v$process_memory to know the Other category consume more memory and its not released . Could you please advice how to fix the stale connection issue .
Category
Other
PL/SQL
SQL
our batch trying to execute etCLOBVal()value queries around 3,00,000.
select column.getCLOBVal() from table where condition.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production