[Python] CLOB attribute of a oracle record type is taking 0.5 seconds for each update using...

I'm trying to update the CLOB attribute of a record type created using below script. I have 40,000 CLOB items, which I'm updating into 40,000 record type DB_TYPE_OBJECT via python code. Each record objects' CLOB attribute (column CLOB_CONTENT) update using python is taking about 0.5 seconds (This is just for creating DB_TYPE_OBJECT) . This is really weird since it's similar to updating an attribute of any other object. (CLOB size is less, size is no issue).

Note: This happens only for CLOB type (for 40,000 items it takes about 3 or 4 hours). when I try without CLOB type (just REC_ID), it finishes up within few seconds.

FYI: This same code and record types is working faster in my personal workspace (tested by running docker image of oracle db ). This issue is popping up at my workplace where database is hosted on aws. (No network issue etc I believe as the same type of code is working faster in Java).

Python3.12 oracledb -> 19.3

create TYPE CLOB_REC_OBJECT as OBJECT(
REC_ID NUMBER,
CLOB_CONTENT CLOB
);


import os
import time

import numpy as np
import oracledb
import pandas as pd


dsn = "localhost:1521/ORCLCDB"
username = "SYS"
password = "mypassword1"

try:
instant_client_dir = os.path.join(os.environ.get("HOME"), "Documents", "OracleDB/instantclient_23_3")
oracledb.init_oracle_client(lib_dir=instant_client_dir)

num_objects = 40000
data = {
'long_string_column': [f"This is a very long string example for row {i}. It can contain various characters and be quite lengthy to simulate real-world data scenarios." * 5 for i in range(num_objects)],
'numeric_column': np.random.randint(1000, 100000, size=num_objects)
}

df = pd.DataFrame(data)

conn = oracledb.connect(user=username, password=password, dsn=dsn, mode=oracledb.SYSDBA)
print("Connected to Oracle Database (CDB Root) using Thin Client successfully!")

cursor = conn.cursor()
my_objects_list = []
rec_type = conn.gettype("CLOB_REC_OBJECT")

rec_list = [rec_type.newobject() for _ in range(num_objects)]

start_time = time.time()

for row in df.itertuples():
new_obj = rec_list[row[0]]
setattr(new_obj, 'REC_ID', i)
setattr(new_obj, 'CLOB_CONTENT', f"Name_{i}"*4000)
my_objects_list.append(new_obj)

end_time = time.time()

print(f"Time taken to create objects and set attributes with setattr(): {end_time - start_time:.4f} seconds")
print(f"Number of objects in the list: {len(my_objects_list)}")

except oracledb.Error as e:
error_obj, = e.args
print(f"Error connecting to Oracle Database: {error_obj.message}")
finally:
if 'cursor' in locals() and cursor:
cursor.close()
if 'conn' in locals() and conn:
conn.close()


I have provided the lastest code which I tried. I would like to know why is updating a CLOB attribute using python and oracledb takes so much time, what are the checks that I can perform for this to find the root cause and fix this issue. even if it's 40,000 items, it should finish in around 16 seconds just like my personal system does.

Also, if there's any alternative faster way to do this. (First preference would be to find root cause of this issue and fix)

Continue reading...