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

[Henning v. B. <hen...@ar...>]

> 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

You should use session tracing and TKPROF to see what's really going on.
HTH
Henning