------------------------------------------------------------------------------------------------------
--Oracle Enterprise Linux 7.9 x64
--Oracle Database Enterprise Edition 19.12 (19-JULY-2021)
--Oracle Grid Infrastructure 19.12 (19-JULY-2021) for Standalone Server ( NOT RAC - GI HAS/ASM ONLY)
------------------------------------------------------------------------------------------------------
After the 19.12 Patchset was applied the database instance started to show repeated ORA-4036 in the database alert log.
------------------------------------------------------------------------------------------------------
"PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process."
------------------------------------------------------------------------------------------------------
It got to the point that it consumed all Linux memory and Linux start to swap.
This caused further ORA-4036 errors, and eventually was necessary to stop/start the database instance to free up the Linux memory,
then the database worked fine again, till the same issue occurred.
The workload did not changed, and when 19.11 (19-APRIL-2021) Patchset was installed the ORA-4036 was not occurring.
I opened a SR with Oracle Support and the temporary workaround was to set PGA_AGGREGATE_LIMIT=0.
README_FIRST_adrci_show_problems.txt (4.23 KB)------------------------------------------------------------------------------------------------------
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3
------------------------------------------------------------------------------------------------------
If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
For a PDB, the default value is the same as the CDB's default value.
------------------------------------------------------------------------------------------------------
Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail.
However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup.
If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
------------------------------------------------------------------------------------------------------
But after a few days, PGA keeps growing larger and larger. But workload only has 5 users connected.
README_PGA_MEMORY.txt (2.76 KB)I was able to determine that these processes are taking lots of PGA space and not releasing it.
------------------------------------------------------------------------------------------------------
SESSION_ID, SESSION_SERIAL, PROCESS_NAME, PID_THREAD, CURRENT_SIZE_MB, MAXIMUM_SIZE_MB
5645 64179 Q009::mcsdboralnx5.esri.com 22773 6,115.40 6,115.40
4707 62132 Q007::mcsdboralnx5.esri.com 22769 6,147.46 6,147.46
6116 6422 Q00A::mcsdboralnx5.esri.com 22775 6,179.53 6,179.53
3767 25363 Q005::mcsdboralnx5.esri.com 22765 6,211.46 6,211.46
5174 9587 Q008::mcsdboralnx5.esri.com 22771 6,243.46 6,243.46
------------------------------------------------------------------------------------------------------
All these Q processes are related to "BACKGROUND Streams EMON Regular Slave".
The 19.12 patchset has many fixes for PGA memory leaks, and other memory fixes too, maybe those could have introduced this issue.
--Database 19 Release Updates and Revisions Bugs Fixed Lists ( Doc ID 2523220.1 )
------------------------------------------------------------------------------------------------------
27933036 pga memory leak at koh-kghu call / kol vstring for dbms_aq.listen call
32066061 Memory Leak in "kxsclb in kxscf" Component Causing ORA-4030/ORA-4036
32033795 Unreasonable large memory allocations by query processing code
32338476 Memory Leak: auditpqreceive
------------------------------------------------------------------------------------------------------
I have opened a SR with Oracle Support about this problem.
I could rollback the 19.12 Patchset till Oracle has a patch.
But I want to know if anyone has a workaround, such as setting a hidden parameter, that could help resolve the issue.