deqone() with JSON queue causes PGA memory leak in Oracle process PGA area in ILOC_KPDLOB #346
Description
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.