oracle
deqone() with JSON queue causes PGA memory leak in Oracle process PGA area in ILOC_KPDLOB
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.
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.
@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?
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!
This issue was corrected in version 2.3.0, which was just released.