cx-oracle-users

[cx-oracle-users] cx_Oracle opens an excessive number of sessions

[Hannes B. <hab...@gm...>]

I have a problem using instantclient 11.2 and cx_Oracle 5.0.3. When I query
the database, cx_Oracle creates an excessive number of sessions to the
database (about 70). I already crashed the database once, because I went
over the allowed number of sessions (Oracle default is 150), after which my
sysadmin increased the number of allowed connections to about 4000. However,
he tells me I have to reduce the amount of resources I use and the number of
sessions, so he can set the allowed number of connections back to something
reasonable once the server goes into production.

The one thing I tried was to set cursor.arraysize to something smaller, but
it doesn't make any difference.

This is was happens:

import cx_Oracle

db = cx_Oracle.connect(user, pass, dsn)   # Nothing bad happens here
cursor = db.cursor()  # This is fine too

cursor.execute('SELECT foo FROM bar")   # Opens an excessive number of
sessions

cursor.fetchone()                                      # Read some records
cursor.fetchmany()

cursor.close()                                           # Doesn't close the
many sessions
db.close()                                                #  Closes the
sessions.

--Hannes

Re: [cx-oracle-users] cx_Oracle opens an excessive number of sessions

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

Hi,

I have never seen that behavior before and I have been using cx_Oracle
for well over a decade. Can you try the same thing with SQL*Plus? They
use the same base libraries so if there is a problem with the client
installation or other issue in your environment, that should show it
up. Perhaps try an older version of the instant client (11.1)?
Hopefully someone else may have seen this sort of behavior before and
can help you out further.

Anthony

On Thu, Jul 22, 2010 at 1:26 AM, Hannes Bretschneider
<hab...@gm...> wrote:
> I have a problem using instantclient 11.2 and cx_Oracle 5.0.3. When I query
> the database, cx_Oracle creates an excessive number of sessions to the
> database (about 70). I already crashed the database once, because I went
> over the allowed number of sessions (Oracle default is 150), after which my
> sysadmin increased the number of allowed connections to about 4000. However,
> he tells me I have to reduce the amount of resources I use and the number of
> sessions, so he can set the allowed number of connections back to something
> reasonable once the server goes into production.
> The one thing I tried was to set cursor.arraysize to something smaller, but
> it doesn't make any difference.
> This is was happens:
> import cx_Oracle
> db = cx_Oracle.connect(user, pass, dsn)   # Nothing bad happens here
> cursor = db.cursor()  # This is fine too
> cursor.execute('SELECT foo FROM bar")   # Opens an excessive number of
> sessions
> cursor.fetchone()                                      # Read some records
> cursor.fetchmany()
> cursor.close()                                           # Doesn't close the
> many sessions
> db.close()                                                #  Closes the
> sessions.
> --Hannes
> ------------------------------------------------------------------------------
> 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
>
>

Re: [cx-oracle-users] cx_Oracle opens an excessive number of sessions

[rwe <rog...@to...>]

Hi,
I have been chasing a similar problem.
The same code runs on several constellations without problem, except for 
one where I get an
excessove number of processes on the DB server. Killing the process gets 
rid of them.

I will check to see what version of cx_Oracle, Oracle client and Oracle 
are in use, as I
am pretty sure that it is a praticular combination that produces the 
problem.

Roger

-------- Original Message  --------
Subject: Re: [cx-oracle-users] cx_Oracle opens an excessive number of 
sessions
From: Anthony Tuininga <ant...@gm...>
To: cx-...@li...
Date: 22.07.2010 16:57
> Hi,
>
> I have never seen that behavior before and I have been using cx_Oracle
> for well over a decade. Can you try the same thing with SQL*Plus? They
> use the same base libraries so if there is a problem with the client
> installation or other issue in your environment, that should show it
> up. Perhaps try an older version of the instant client (11.1)?
> Hopefully someone else may have seen this sort of behavior before and
> can help you out further.
>
> Anthony
>
> On Thu, Jul 22, 2010 at 1:26 AM, Hannes Bretschneider
> <hab...@gm...> wrote:
>   
>> I have a problem using instantclient 11.2 and cx_Oracle 5.0.3. When I query
>> the database, cx_Oracle creates an excessive number of sessions to the
>> database (about 70). I already crashed the database once, because I went
>> over the allowed number of sessions (Oracle default is 150), after which my
>> sysadmin increased the number of allowed connections to about 4000. However,
>> he tells me I have to reduce the amount of resources I use and the number of
>> sessions, so he can set the allowed number of connections back to something
>> reasonable once the server goes into production.
>> The one thing I tried was to set cursor.arraysize to something smaller, but
>> it doesn't make any difference.
>> This is was happens:
>> import cx_Oracle
>> db = cx_Oracle.connect(user, pass, dsn)   # Nothing bad happens here
>> cursor = db.cursor()  # This is fine too
>> cursor.execute('SELECT foo FROM bar")   # Opens an excessive number of
>> sessions
>> cursor.fetchone()                                      # Read some records
>> cursor.fetchmany()
>> cursor.close()                                           # Doesn't close the
>> many sessions
>> db.close()                                                #  Closes the
>> sessions.
>> --Hannes
>> ------------------------------------------------------------------------------
>> 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
>>
>>
>>     
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>   

Re: [cx-oracle-users] cx_Oracle opens an excessive number of sessions

[Amaury F. d'A. <ama...@gm...>]

Hello,

We just encountered a similar issue where there were too many processes on the
DB server.  the number of rows in v$process was much higher that the number in
v$session.  Eventually some programs get an error: "ORA-00020: maximum number
of processes exceeded"

Here we have a bunch of processes that use a database connection, then "go to
sleep" for a while. They do close the connection, but the object is kept in a
global variable.

I think this is a bug in cx_Oracle: when connection.close() is called,
OCISessionEnd() is called, but OCIServerDetach() is not.  It will be called
only by connection.__del__, when the connection object is itself destroyed.

I tried to move the call to OCIServerDetach() in Connection_Close, and indeed
the occurrence in v$process disappears when I call connection.close().
Since a closed connection is unusable and the OCIServer handle cannot be
reused, it makes more sense to free this resource sooner.

Or course the workaround is to delete the connection object, with a
"conn=None" or similar.  But cx_Oracle should be changed IMO.
What do you think?

2010/7/28 rwe <rog...@to...>:
> Hi,
> I have been chasing a similar problem.
> The same code runs on several constellations without problem, except for
> one where I get an
> excessove number of processes on the DB server. Killing the process gets
> rid of them.
>
> I will check to see what version of cx_Oracle, Oracle client and Oracle
> are in use, as I
> am pretty sure that it is a praticular combination that produces the
> problem.

-- 
Amaury Forgeot d'Arc