deqone() with JSON queue causes PGA memory leak in Oracle process PGA area in ILOC_KPDLOB

[christo4nextria]

platform.platform: Linux UBUNUTU sys.maxsize > 2**32: True platform.python_version: 3.10.12 Oracle version: 23.4 FREE python-oracledb version: Version: 2.2.1

This problem causes a memory leak in the Oracle session process, eventually hitting the 2 GB PGA limit after about 50,000 dequeues.

Does your application call init_oracle_client()? => YES running Thick mode

Schema objects:

begin
    dbms_aqadm.create_queue_table('JSON_QUEUE_TABLE', 'JSON');
    dbms_aqadm.create_queue('DEMO_JSON_QUEUE', 'JSON_QUEUE_TABLE');
    dbms_aqadm.start_queue('DEMO_JSON_QUEUE');
end;
/

Load Queue with 50,000 records:

DECLARE
  enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_handle     RAW(16);
  json_payload       JSON;
BEGIN
 for cur in (select json_object (key 'ID' value rownum) as json_payload from dual connect by level <=50000) loop
   json_payload:= json(cur.json_payload);
    DBMS_AQ.ENQUEUE(
      queue_name         => 'DEMO_JSON_QUEUE',
      enqueue_options    => enqueue_options,
      message_properties => message_properties,
      payload            => json_payload,
      msgid              => message_handle
    );
  end loop;

  COMMIT;
END;
/

Test script:

import oracledb
oracledb.init_oracle_client()

import json
import time
from datetime import datetime

from typing import List, Tuple, Any

try:
    dsn = oracledb.makedsn('192.168.2.1', '1521', service_name='FREEPDB1')
    connection = oracledb.connect(user='demouser', password='secret', dsn=dsn)
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3] + ": INIT DB Connection");
except oracledb.Error as e:
    print("Error:", e)

def dequeue_message():

    queue = connection.queue("DEMO_JSON_QUEUE", payload_type='JSON')

    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3] + ": INIT queue");

    while True:
        #print("Loop")
        message =queue.deqone()
        connection.commit()

def main():
    dequeue_message()

if __name__ == '__main__':
    main()

Error printed :

Traceback (most recent call last):
  File "/home/xxx/dqwtestjson.py", line 32, in <module>
    main()
  File "/home/xxx/dqwtestjson.py", line 29, in main
    dequeue_message()
  File "/home/xxx/dqwtestjson.py", line 25, in dequeue_message
    message =queue.deqone()
  File "/usr/local/lib/python3.10/dist-packages/oracledb/aq.py", line 86, in deqone
    message_impl = self._impl.deq_one()
  File "src/oracledb/impl/thick/queue.pyx", line 83, in oracledb.thick_impl.ThickQueueImpl.deq_one
  File "src/oracledb/impl/thick/utils.pyx", line 456, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 446, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-04036: PGA memory used by the instance or PDB exceeds PGA_AGGREGATE_LIMIT.
Help: https://docs.oracle.com/error-help/db/ora-04036/

Oracle trace files from automatic incident showing ILOC_KPDLOB

This is NOT complete log, only copy pasted relevant section - this will make sense to Oracle DBAs.

=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
95% 1583 MB, 40657 chunks: "ILOC_KPDLOB               "
         koh-kghu sessi  ds=0x7f484b5bc738  dsprt=0x7f484b5817c0

Dumping only 7 of 10 process heap scans requested
===============================================
PGA memory detail for pid 71, OS pid 955448
===============================================
    65216 bytes, 420 chunks: "miscellaneous             "
          multiple heaps  ds=(nil)  dsprt=(nil)
1660106624 bytes,40657 chunks: "ILOC_KPDLOB               "
          koh-kghu sessi  ds=0x7f484b5bc738  dsprt=0x7f484b5817c0
 48900920 bytes,1781 chunks: "free memory               "
          koh-kghu sessi  ds=0x7f484b5bc738  dsprt=0x7f484b5817c0
 34949536 bytes, 332 chunks: "free memory               "
          session heap    ds=0x7f484b5817c0  dsprt=0x7f484ba0e5c0
   125064 bytes,   1 chunk : "Fixed Uga                 "
          Fixed UGA heap  ds=0x7f484b6d0c18  dsprt=0x7f484ba0b5c0
    90128 bytes,   1 chunk : "ksectab: kseget_caller_qk "
          pga heap        ds=0x7f484ba0b5c0  dsprt=(nil)
    86088 bytes,   2 chunks: "row cache                 "
          pga heap        ds=0x7f484ba0b5c0  dsprt=(nil)
    73872 bytes,   1 chunk : "inode                     "
          buckets_kdliug  ds=0x7f4848be6238  dsprt=0x7f484b5817c0
    68280 bytes,   2 chunks: "frame segment             "  SQL
          kxs-heap-f      ds=0x7f4848bf62b8  dsprt=0x7f484b5817c0
    65616 bytes,   2 chunks: "free memory               "
          top uga heap    ds=0x7f484ba0e5c0  dsprt=(nil)
    62400 bytes,  15 chunks: "kxsFrame4kPage            "
          session heap    ds=0x7f484b5817c0  dsprt=0x7f484ba0e5c0
    59720 bytes,   1 chunk : "free memory               "
          top call heap   ds=0x7f484ba0e300  dsprt=(nil)
    54912 bytes,   2 chunks: "dbgeInitProcessCtx:InvCtx "
          diag pga        ds=0x7f484b9c5818  dsprt=0x7f484ba0b5c0
    49344 bytes,   3 chunks: "kxsFrame16kPage           "
          session heap    ds=0x7f484b5817c0  dsprt=0x7f484ba0e5c0
    46464 bytes,  16 chunks: "kxsc: kkspsc0 2           "
          session heap    ds=0x7f484b5817c0  dsprt=0x7f484ba0e5c0
    40992 bytes,   1 chunk : "kfkio bucket              "
          pga heap        ds=0x7f484ba0b5c0  dsprt=(nil)
    37376 bytes,   3 chunks: "kgiob                     "
          session heap    ds=0x7f484b5817c0  dsprt=0x7f484ba0e5c0
    37024 bytes,   4 chunks: "alloc server hndl         "
          Alloc environm  ds=0x7f484b5bb120  dsprt=0x7f484b5817c0
    35104 bytes,   6 chunks: "FILE:dbgtb.c:7371         "

Same code with OBJECT type queue does NOT cause a memory leak with 50,000 or even 500,000 records.

[anthony-tuininga]

We will look into it. Thanks for the detailed report!

[anthony-tuininga]

I have pushed a patch that corrects this issue. If you are able to build from source you can verify that it works for you, too.

[christo4nextria]

@anthony-tuininga thank you for looking into this and solving it quickly!

We will need a download to use in production code. Do you know what release will have this patch and an approximate timeframe?

[anthony-tuininga]

This should be included in version 2.3 which is scheduled for sometime in the next few weeks. The exact timeframe is unknown at this point, though!

[anthony-tuininga]

This issue was corrected in version 2.3.0, which was just released.