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

[Anthony T. <ant...@gm...>]

Hi,

Sorry I can't really be of much help. I've never seen this sort of
behavior myself. All I can suggest is looking at the status of the
query when it is taking a long time (is it ACTIVE or INACTIVE -- in
Oracle doing something or not in Oracle doing something else) or
performing a trace to see where the holdup is. Another suggestion to
confirm is that you are using the same Oracle home for your cx_Oracle
work and your SQL*Plus work -- sometimes that can bite you. :-) And if
you find something intriguing, please let us know!

Anthony

On Fri, Jul 9, 2010 at 2:32 AM, Roger Downing <rog...@gm...> wrote:
> 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
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>