Forum Stats

  • 3,769,829 Users
  • 2,253,027 Discussions
  • 7,875,217 Comments

Discussions

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

2

Answers

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,220 Bronze Trophy

    Hello Marcelo

    Thanks for this detailed feedback

    But this PGA memory leak will kick in only for those applications using Advanced Queueing? right?

    Best regards

    Mohamed Houri

  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge

    @Mohamed Houri - I have a 19.12 CDB with 30 PDBs on Linux and Windows and I am "not" using Advanced Queueing, nor Streams and the issue with Q Processes Memory Leak still happens.

  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge
    edited Oct 5, 2021 4:30PM

    Below is the temporary workaround I am using so I don't need to shutdown/startup the db instance.

    I am still working with Oracle Support on the SR that I have opened on Aug/03 and till now Oracle Support has not found a definitive viable workaround and has not logged a bug for the issue yet. : (

  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge
    edited Oct 5, 2021 11:03PM

    Someone else was experiencing the same issue in their production environment.

    https://community.oracle.com/mosc/discussion/comment/16841951#Comment_16841951

    here's the steps provided from Oracle:

    Check if AQ_SRVNTFN_TABLE_Q_1 dequeue is enabled or disabled:

    select owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS';

    If the dequeue is disabled, re-enable it with the following:

    EXECUTE DBMS_AQADM.START_QUEUE (queue_name => 'SYS.AQ_SRVNTFN_TABLE_Q_1');

    check again to confirm the change. This stopped the Q processes from growing in their case.

    -----------------------------------------------------------------------------------------------

    I applied the workaround described above on my databases 19.12 Linux and 19.12 Windows but the Q processes memory leak still happens and the Q processes continue to take more and more memory and do not release the memory.  : (


  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge
    edited Oct 5, 2021 11:04PM

    Oracle Support replied to my SR this morning Oct/05/2021 and Oracle Support has finally agreed to file a bug for this issue. I will post more details once I have an update from Oracle Support.

  • Nenad Noveljic
    Nenad Noveljic Member Posts: 11 Blue Ribbon

    With regard to workaround, I would prefer killing the q processes when they grow instead of setting pga limit to 0. Killing a q process won't crash the database:

    pargs -e 28979 | grep -i bg

    envp[44]: SKGP_HIDDEN_ARGS=<BG...

  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge

    I am using a temporary workaround till Oracle has an One-Off Patch.

    I posted the workaround on Sep/30, please see above the post with the script "q_processes_memory_leak_temporary_workaround.txt", with this workaround I don't need to stop/start the oracle instance and I don't need to kill the q processes manually in the OS, which in Windows is difficult to do.

  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge
    edited Oct 7, 2021 6:29PM

    Update. I opened the Oracle Support SR on Aug/03/2021, more than 30 days ago, and I had 2 Spin-Off SRs, each time I had to provide more and more logs, and at each Spin-Off SR I had to engage with another support engineer and explain the problem all over again, the first parent SR owner was not following up directly with the Spin-Off SR owners to coordinate and explain the issue, hence we ended up wasting a lot of time, only when the first parent SR owner aggreged to file the bug that another Oracle support engineer took ownership of the parent SR and with his help we finally found a workaround that worked. I wish that person had been involved in the SR from the beginning, we would probably had encountered the workaround a lot sooner.

    Mohamed Houri
  • Marcelo Marques
    Marcelo Marques Member Posts: 171 Bronze Badge
    edited Oct 8, 2021 3:20PM Accepted Answer

    *** WORKAROUND THAT RESOLVES THE ISSUE ***

    This is the workaround that resolves the issue "Q Processes Memory Leak".

    PROBLEM DESCRIPTION:  After applying 19.12 Patchset (19-JUL-2021) the Q Processes take more and more memory and do not release the memory, this causes PGA to grow larger and larger, if PGA_AGGREGATE_LIMIT=<null> then will encounter ORA-04036, if PGA_AGGREGATE_LIMIT=0 then PGA can grow unlimited, and the Q Processes will consume all server memory".

    ORA-4036 - "PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA memory are not eligible to receive ORA-4036 interrupts." !!! this can cause user sessions to disconnect !!!

    I have encountered the issue on 19.12 Linux and 19.12 Windows. The workaround below resolves the issue on both platforms.

    CAUSE:  Oracle explains the 19.12 Patchset (19-JUL-2021) disables the dequeue for AQ_SRVNTFN_TABLE_Q_1, this should be enabled, across any and all patched CDB and "ALL" PDBs.

    WORKAROUND:

    1. Reset the Q Processes to free pga memory !!!
    2. Re-enable Q Processes Dequeue on CDB and "ALL" PDBs !!!

    Thanks,

    Marcelo Marques | Principal Product Engineer | Esri

    OCP - Oracle Certified Professional

    Mohamed Houri
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,220 Bronze Trophy

    Marcelo,

    Thank you very much for this benevolent effort to share with us your case of of pga overconsumption following the upgrade to 19.12. I really appreciated it.

    Just today I shared your issue with my colleague who is going to upgrade one of his bases from 19.8 to 19.12 to solve an unreported bug of LOB TEMPORARY not released by the call to DBMS_LOB.FreeTemporary from the JDBC pool of connection as it used to be in their 12.2 previous release.

    I suppose that the final workaround is the one you have already shared in one of your previous interventions.

    Best regards

    Mohamed Houri