[cx-oracle-users] CLOB performance issue

[Vladimir R. <gre...@gm...>]

Hi.

I have encountered a performance problem when inserting values into a
column of CLOB datatype. For example I have a table:

create table table_clob (msg_id number, message clob)

Here is my code to reproduce the issue:

-----
import time
import cx_Oracle
import sys

print('Python', sys.version, 'on', sys.platform)
print('cx_Oracle.version:', cx_Oracle.version)
num_records = 100
con = cx_Oracle.connect('user/password@sid')
print('DB version:', con.version)
cur = con.cursor()
cur.prepare("insert into table_clob (msg_id, message) values (:msg_id,
:msg)")
cur.bindarraysize = num_records
msg_arr = cur.var(cx_Oracle.CLOB, arraysize=num_records)
text = '$'*2**20    # 1 MB of text
rows = []

# Prepare data.
start_time = time.perf_counter()
for id in range(num_records):
    msg_arr.setvalue(id, text)
    rows.append( (id, msg_arr) )

# Insert data.
print('{} records prepared, {:.3f} s'
      .format(num_records, time.perf_counter() - start_time))
start_time = time.perf_counter()
cur.executemany(None, rows)
con.commit()
print('{} records inserted, {:.3f} s'
      .format(num_records, time.perf_counter() - start_time))

cur.close()
con.close()
-----

which yields the results:

-----
Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:06:53) [MSC v.1600 64
bit (AMD64)] on win32
cx_Oracle.version: 5.1.3
DB version: 11.2.0.1.0
100 records prepared, 5.384 s
100 records inserted, 4.536 s
-----

Versions of my DB and my client are the same.

What's worrying me is the time of preparation of msg_arr structure. 5
seconds is abnormally much for copying of 100 MB in memory. 4.5 seconds
seems OK for sending it over the network.
The problematic step is msg_arr.setvalue(id, text). If I comment it, script
takes just milliseconds to complete (inserting null into CLOB column of
course).
I also noticed a persistent disk activity during the "prepare" stage.
Perhaps cx_Oracle library stores some data in temporary files on disk? This
could be the reason of the problem...

I wonder if there is a way to improve performance of "preparation" part of
my code.

Thanks in advance for your help.