Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

19.12 Linux - PGA MEMORY VERY LARGE DUE TO Q PROCESSES - BACKGROUND Streams EMON Regular Slave

Marcelo MarquesSep 7 2021 — edited Sep 7 2021

------------------------------------------------------------------------------------------------------
--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.
This post has been answered by Marcelo Marques on Oct 7 2021
Jump to Answer

Comments

Mike Kutz

bitmap
Bitmap indexes implies Serialization. That's like having the db parameter _SLOW=TRUE.
It can speed up SELECTS, but at a cost for other DML operations.
In my uses, it's not about the "low cardinality" of the answers in a column, but the "low cardinality" of your query results.
Eg finding out that a particular song, sung by Adam Sandler about Tinder results for other Eskimos in his tribe, is true
I've used them to speed up ad hoc queries on final reporting tables.
Billy uses them to show you can count 42B rows in under 1s.
Partition
"Prevent FTS" is one use for Partitions.
The primary use I've seen is for data management.
Need to remove 1 mo worth of data? Drop a Partition
Also, research ILM in the Data warehouse guide. This helps automate the task of moving chunks of old data for you. (Eg move 1mo worth of 3yr old data from tablespace on SSD to compressed read-only tablespace on SATA)

User_JNHXJ

Thank for replying mike
i have a couple of question
1.what u mean by ad hoc query is query using bind variable in where clause? or is there a different meaning?(from what i know ad hoc query is a type of query where result set is depent on the value supplied to a variable)

2."Need to remove 1 mo worth of data? Drop a Partition" even if we drop a partition the actual table still have the data from that partition right? and for what purpose we dropping this data? for memory or just deleting old data?

Mike Kutz

Ad hoc
This is what most end users want to perform.
APEX IR searches are ad hoc queries. Especially those faceted searches.
Bitmap indexes could drastically improve multi-column queries of these types even on a 1 B row table.
But, INSERT/UPDATE/DELETES would be horrible. You'll want to do infrequent bulk operations from a single transaction only. Tools like DBMS_PARALLEL_EXECUTE will cause the crud operation to take longer if parallel_level is higher than 1. (I learned this the hard way)
Drop Partition
You usually remove old data for legal reasons.
Partitions are individuals tables magically joined together to act as 1 table. If you read the history of Oracle, Partitions started in v7.3 as Partitioned VIEWs.
So, when you DROP a Partition, you are actually dropping a table.
Which then brings up Partition EXCHANGE. In this case you are actually swapping one table for another. And because the DD is only swapping the pointers, this is extremely fast.
You can swap in/out a table's worth of data in the blink of an eye. (I'm ignoring INDEXES)
Again, I find Partitions more useful for data management. But queries performance shouldn't be ignored.

User_JNHXJ

thanks for the partition part i got a little insigth about it.
but still have question about bitmap
can you explain example of "faceted searches" is it like result set with low cardinality?(FYI first time hear faceted word so i do not understand)

Mike Kutz

Faceted Search example using APEX.

https://blogs.oracle.com/apex/post/apex-192-faceted-search

1 - 5

Post Details

Added on Sep 7 2021
#general-database-discussions
29 comments
6,204 views
1 mention