Fetching user defined datatypes using oracledb in tick mode is consuming huge amount of memory.

[joshpintea]

Fetching user defined datatypes using oracledb in tick mode is consuming huge amount of memory.

1. What versions are you using?

   >>> import oracledb
   >>> oracledb.__version__
   1.4.1

Give your database version.

Oracle 19c

Also run Python and show the output of:

>>> import sys
>>> import platform

>>> print("platform.platform:", platform.platform())
platform.platform: Windows-10-10.0.19045-SP0
>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True
>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.9.5

And:

>>> import oracledb
>>> print("oracledb.__version__:", oracledb.__version__)
oracledb.__version__: 1.4.1

-->

2. Is it an error or a hang or a crash?

none of them. Application is not releasing the memory.

3. What error(s) or behavior you are seeing?

huge memory consumption by the app when fetching custom data types in tick mode

4. Does your application call init_oracle_client()?

Yes. I'm using 19.20 version.

>>> import oracledb
>>> oracledb.init_oracle_client()
>>> print(oracledb.client_version()
(19, 20, 0, 0, 0)

5. Include a runnable Python script that shows the problem.

I have the following user custom types and table

create or replace TYPE "vararray" AS TABLE OF VARCHAR2(4000);

create or replace TYPE "test" AS OBJECT
(
  attr1 VARCHAR2(100),
  attr2 vararray,
  attr3 VARCHAR2(100)
)

create or replace TYPE "testarray" AS TABLE OF test;

CREATE TABLE t_test
(       
  id number(1, 16),
  attr_array testarray
)

When I'm fetching all rows from t_test table, the python process is consuming alot of memory. The following is the python script:

import oracledb
import psutil
import os

pid = os.getpid()
python_process = psutil.Process(pid)

oracledb.init_oracle_client()

conn_str = ''
conn = oracledb.connect(conn_str)

cursor = conn.cursor()
cursor.execute(f"select * from t_test")

i = 0

for row in cursor:
    i += 1

    if i % 1000 == 0:
        print(f"it={i}: mem:", python_process.memory_info().rss / 1024 ** 2, 'MB')

cursor.close()
conn.close()

Observed behavior: memory used by the process is constantly growing. This can be observed only on thick mode. But I need to use thick mode since the final app needs to support also oracle 11g and thin mode can be used only from oracle versions >= 12

Output of the script:

it=1000: mem: 44.39453125 MB
it=2000: mem: 46.9453125 MB
it=3000: mem: 49.5390625 MB
....
it=21000: mem: 175.13671875 MB
....
it=78000: mem: 733.47265625 MB
....
it=102000: mem: 968.4375 MB
....

[anthony-tuininga]

This appears to be due to the underlying Oracle Client libraries but I am asking internally to verify. Note that there is an object cache which may be contributing to this. Do you know its size? (See the OBJECT_CACHE_OPTIMAL_SIZE parameter for details).

[anthony-tuininga]

Closing - no activity.

[cjbj]

An update on this issue with Thick mode memory growth using Oracle DB nested objects: @anthony-tuininga had logged an Oracle bug against the Oracle Client. This is now fixed and the change will be released in some future version of Oracle. If you need to request backports etc, the Oracle bug is 35915869