Re: [cx-oracle-users] Session Pooling

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

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=3DN=
one):
>         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
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>