CLOB performance issue

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.

------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users