Thread: RE: [cx-oracle-users] Session Pooling
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Orr, S. <so...@ri...> - 2005-08-01 22:59:35
|
So long without answers? Are my questions too hard? :-)
I was wrong about the SessionPool.timeout attribute. It works just fine.
I can configure the number of processes which Apache spawns and even
though each process establishes a reusable pool of database connections
they level off with inactivity and scale up when needed. Pooling
connections with cx_Oracle/Apache/mod_python serves up data driven web
pages really FAST!=20
I'm still wondering about the ramifications of adding connections with
different credentials than what the session pool was created with.=20
I'd also like to get some feedback on caching cursors. It seems like I
saw some code for that in the cx utilities or whatever. I'm just
wondering how much coding effort is required and what's the benefit?=20
Steve Orr
-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of Orr,
Steve
Sent: Monday, August 01, 2005 11:28 AM
To: cx-...@li...
Subject: [cx-oracle-users] Session Pooling
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,=20
dsn=3Ddsn, pool=3Dself.pool)
return conn
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.
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?
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
-------------------------------------------------------
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&op=3Dick
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Orr, S. <so...@ri...> - 2005-08-02 18:26:59
|
> 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
|
|
From: Anthony T. <ant...@gm...> - 2005-08-02 13:41:06
|
On 8/1/05, Orr, Steve <so...@ri...> wrote:
> So long without answers? Are my questions too hard? :-)
No. Just a long weekend here in Canada. :-)
> I was wrong about the SessionPool.timeout attribute. It works just fine.
> I can configure the number of processes which Apache spawns and even
> though each process establishes a reusable pool of database connections
> they level off with inactivity and scale up when needed. Pooling
> connections with cx_Oracle/Apache/mod_python serves up data driven web
> pages really FAST!
Sounds good.
> I'm still wondering about the ramifications of adding connections with
> different credentials than what the session pool was created with.
See earlier e-mail.
> I'd also like to get some feedback on caching cursors. It seems like I
> saw some code for that in the cx utilities or whatever. I'm just
> wondering how much coding effort is required and what's the benefit?
See earlier e-mail.
> Steve Orr
>=20
>=20
> -----Original Message-----
> From: cx-...@li...
> [mailto:cx-...@li...] On Behalf Of Orr,
> Steve
> Sent: Monday, August 01, 2005 11:28 AM
> To: cx-...@li...
> Subject: [cx-oracle-users] Session Pooling
>=20
> 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&op=3Dick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>=20
>=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
>
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X