[cx-oracle-users] Performance problems with stored procedure calls

[Roger D. <rog...@gm...>]

Hi there,
I am using cx_Oracle 5.0.3 on Python 2.4 with Oracle Instant client 11.0.7
speaking to an Oracle 11gR2 RAC system.

The code:
import cx_Oracle
conn = cx_Oracle.connect(user='********',password='********',dsn='********')
cur = conn.cursor()
cur.arraysize = 50

for i in range(100):
  params =
('/tdls-1/i99/data/2010/rogfile1-%d'%(i),12345678,'tdls-1','container','i99','mx123-4',0)

  print params
  cur.callproc('PKG_STORAGED.add_file',parameters=params)

Performance of this code has been highly variable, from 300 seconds down to
1.6 seconds. A similar simple loop run via SQLPlus finishes in about 1.5
seconds reliably. I have tried changing cur.arraysize with no real change
observed.

When the code is running slowly, it stays that way for quite some time; yet
I find that this morning it is running quickly. Before you state the
obvious, during a period of slowness, SQLPlus is still able to execute the
loop in under 2 seconds.

I have also noticed that when it is slow if I try to change the code to use
executemany() instead then that too is slow, seeming to hang (strace shows
though that it is still active).

My question is, therefore, what factors could there be specific to cx_Oracle
that would be affecting performance so dramatically? The machine is
relatively high spec with 4 cores and 4G of RAM and is otherwise idle.

Hoping you can help!

Roger