RE: [cx-oracle-users] Session Pooling

[Orr, S. <so...@ri...>]

> Try "select user from dual" on your established connection...
Here's what I found. The cx_Oracle.connect() method accepts credential
args as well as a pool arg. If you supply the pool arg you don't have to
supply the credentials but if you do they are ignored as all connections
use the credentials given when SessionPool is instantiated. This means
you have to establish separate pools for each distinct Oracle username
if you want to pool their connections.=20

> In general I have used the pool to acquire connections...
I'm establishing connections through my dbfun class. This way, if I
decide later to pool the connections for a particular user I don't have
to change the code outside my class. Also, I don't need to know if they
are in the pool as Connection.close() works regardless of pool status.
The valuable connection pooling enhancement only required a little extra
code.=20
-----------------------------------------------------------------
class myConnection(cx_Oracle.Connection):
    def __init__(self, *args, **kwargs):
        cx_Oracle.Connection.__init__(self, *args, **kwargs)
        self.pool =3D kwargs.get('pool',None)
    def close(self):
        if self.pool: self.pool.release(self)
        else: super(myConnection, self).close()

class dbfun(object):
    def __init__(self):
        self.pool=3DmySessionPool(user=3D'name', password=3D'pw', =
dsn=3D'local',
                                min=3D2, max=3D16, increment=3D2)
    def connect(self, *args, **kwargs):
        if kwargs.get('user','NoUser') =3D=3D 'name':
            kwargs.setdefault('pool', self.pool)
        return myConnection(*args, **kwargs)
-----------------------------------------------------------------

Thanks,
Steve Orr
Bozeman, Montana... A little South of Canada :-)


-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of
Anthony Tuininga
Sent: Tuesday, August 02, 2005 7:40 AM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Session Pooling

Exceeding the session limit should raise an exception (that is what
the code in cx_Oracle instructs Oracle to do) but there is a bug in
Oracle right now that causes that particular flag to be ignored. I'm
not sure if its fixed in Oracle 10g yet but I don't think so.

In general I have used the pool to acquire connections rather than
using the Connection class directly. In other words,

connection  =3D pool.acquire()

which is a little simpler than the code you indicated you are using.
:-) It also demonstrates why I've never run into the possibility of
using connections with different credentials than those established by
the pool. Are you certain that the credentials are actually different
on the established connection? The code in cx_Oracle automatically
calls pool.acquire() when a pool is passed in to the Connection
constructor. Try "select user from dual" on your established
connection.

Session pooling automatically implements statement caching and
cx_Oracle doesn't give you an option for turning it off at the moment.
As such no examples are needed. :-) If you are talking about caching
cursors in general then a simple dictionary suffices and in general
can save 15-20% if you are executing a lot of small singleton
query/insert/update/delete statements. You simply need to do the
following:

cursor =3D connection.cursor()
cursor.prepare(some_sql)

and then put that cursor in the cache. When you want to use it

cursor.execute(None, args)

Hope that helps.

On 8/1/05, Orr, Steve <so...@ri...> wrote:
> I'm looking to use session pooling for performance (Apache 2.0,
> mod_python, Quixote 2.0, cx_Oracle 4.x Oracle9). Here's some code for
> reference:
> import cx_Oracle
> class dbconnect(cx_Oracle.Connection):
>     def __init__(self, user=3DNone, password=3DNone, dsn=3DNone, =
pool=3DNone):
>         cx_Oracle.Connection.__init__(self, user=3Duser,
> password=3Dpassword,
>                                       dsn=3Ddsn, pool=3Dpool)
>         self.pool =3D pool
>     def close(self):
>         self.pool.release(self)
> class dbfun(object):
>     def __init__(self):
>         self.pool =3D cx_Oracle.SessionPool('user', 'pw', 'local', 1, =
8,
> 1)
>         self.pool.timeout =3D 30
>     def connect(self, user, password, dsn):
>         conn =3D dbconnect(user=3Duser, password=3Dpassword,
>                          dsn=3Ddsn, pool=3Dself.pool)
>         return conn
>=20
> Basically I subclassed the cx_Oracle connection object to over-ride
the
> close function to release a connection to the pool instead of closing
> it.
>=20
> Observations and Questions:
> Setting the timeout attribute doesn't seem to change anything, no
> connections are returned to the pool after 30 seconds. Is this a bug
or
> am I doing something wrong?
>=20
> If I exceed the session limit from Python it just hangs... Shouldn't
> cx_Oracle.Connection return an exception? Do I really HAVE to compare
> pool.busy and pool.opened in my code and raise my own exception?
>=20
> I found that if I kill a session from SQL*Plus it does not become
> available to the pool. Well okay.
>=20
> I found that when connecting through my dbfun class I could connect
with
> a different user/password than was established in the pool. What are
the
> ramifications of this? If I want to pool connections from different
> users do I need to create distinct pools for each user ID?
>=20
> Are there any other suggestions or sample code on how to implement
> connection pooling? Has anyone cached cursors and is it worth it?
>=20
> TIA,
> Steve Orr