Oracle change in PGA resulted in Memory problem

Question

We have Oracle 12c at a Windows Server 2012.

PGA memory was:

show parameter pga;

We changed it to:

After 5 days, the Memory increased and eventually the system was down, both the application and the listener in Oracle.

The Memory in the machine is 68G.

In the 5th day, we had many clients connected to the database.

Important note: The server has as well Remote Desktop Protocols (RDPs), not only the Oracle.

The system worked well before the change, with delays, but ok. We made the change to avoid the delays, and consulting v$pga_target_advice. Now we reversed it to the previous situation in order for the users to be able to work.

How can I increase the PGA without crashing the system?

The SGA is as well 5GB, could that be connected?

Has anyone else experienced something similar?

Answer 1

This is vague. I see no proof provided for the database being responsible for that memory allocation.

You have a 5 GB SGA and a 5 GB PGA limit. So the memomry allocation of the database should not go much higher than that. But it is still possible for the PGA to grow higher, because pga_aggregate_limit is not enforced for SYS processes.

From your wording, it seems the application runs on the same server. With the data you provided, I could just say the application consumed that memory. Yes, I have seen it many times.

Answer 2

Try to set memory_target and memory_max_target AMM parameters to allow Oracle to take full control of the memory management instead of setting the SGA & PGA sizes separately.