PHP and Oracle: Christopher Jones

Python cx_Oracle and Oracle 11g DRCP Connection Pooling

cj — Thu, 21 Mar 2013 23:18:48 +0000

The topic of Oracle 11g DRCP connection pooling in Python cx_Oracle came up twice this week for me. DRCP is a database tier connection pooling solution which is great for applications run in multiple processes. There is a whitepaper on DRCP that covers a lot of background and talks about configuration. This whitepaper is ostensibly about PHP but is good reading for all DRCP users.

The first DRCP and cx_Oracle scenario I dealt with was a question about mod_python.

To cut a long story short, I created a handler and requested it 1000 times via Apache's 'ab' tool. In my first script, and despite having increased the default pool parameters, there were a high number of NUM_WAITS. Also NUM_AUTHENTICATIONS was high. Performance wasn't the best. Querying V$CPOOL_CC_STATS showed:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        992          8        478                1000

At least the session information in each DRCP server was reused (shown via a high NUM_HITS).

Results were better after fixing the script to look like:

from mod_python import apache
import cx_Oracle
import datetime

# Example: Oracle 11g DRCP with cx_Oracle and mod_python

# These pool params are suitable for Apache Pre-fork MPM
mypool = cx_Oracle.SessionPool(user='hr', password='welcome',
         dsn='localhost/orcl:pooled', min=1, max=2, increment=1)

def handler(req):
    global mypool

    req.content_type = 'text/html'
    n = datetime.datetime.now()
    req.write (str(n) + "<br>");

    db = cx_Oracle.connect(user='hr', password='welcome',
            dsn='localhost/orcl:pooled', pool=mypool, cclass="CJDEMO1",
            purity=cx_Oracle.ATTR_PURITY_SELF)

    cur = db.cursor()
    cur.execute('select * from locations')
    resultset = cur.fetchall()
    for result in resultset:
        for item in result:
            req.write (str(item) + " ")
        req.write ("<br>")
    cur.close()
    mypool.release(db)

    return apache.OK

The 'ab' benchmark on this script ran much faster and the stats from V$CPOOL_CC_STATS looked much better. The number of authentications was right down about to about 1 per Apache (ie. mod_python) process:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        977         23         13                  26

The NUM_HITS was high again, because the DRCP purity was ATTR_PURITY_SELF. If I hadn't wanted session information to be reused each time the handler was executed, I could have set the purity to ATTR_PURITY_NEW. If I'd done this then NUM_HITS would have been low and NUM_MISSES would have been high.

If you're testing this yourself, before restarting the DRCP pool don't forget to shutdown Apache to close all DB connections. Otherwise restarting the pool will block. Also, if you're interpreting your own V$CPOOL_CC_STATS stats don't forget to account for the DRCP "dedicated optimization" that retains an association between clients (mod_python processes) and the DB. The whitepaper previously mentioned discusses this.

The second place where DRCP and python came up this week was on the cx_Oracle mail list. David Stanek posed a question. He was seeing application processes blocking while waiting for a DRCP pooled server to execute a query. My variant of David's script is:

import os
import time
import cx_Oracle

# Example: Sub-optimal connection pooling with Oracle 11g DRCP and cx_Oracle

def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    con = cx_Oracle.connect(user=user, password=pw, dsn=dsn, cclass="CJDEMO2",
           purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    con.close()
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())
 
user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

This script forks off a bunch of processes - more than the number of pooled DRCP servers (see MAXSIZE in the DBA_CPOOL_INFO view). The first few processes grab a DRCP server from the pool and do their query. But they don't release the DRCP server back to the DRCP pool until after the sleep() when the process ends. The other forked processes are blocked waiting for those DRCP servers to become available. This isn't optimal pool sharing.

My suggestion was to use an explicit cx_Oracle session pool like this:

import os
import time
import cx_Oracle

# Example: Connection pooling with Oracle 11g DRCP and cx_Oracle
 
def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    mypool = cx_Oracle.SessionPool(user=user,password=pw,dsn=dsn,min=1,max=2,increment=1)
    con = cx_Oracle.connect(user=user, password=pw,
          dsn=dsn, pool = mypool, cclass="CJDEMO3", purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    mypool.release(con)
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())

user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

The mypool.release(con) call releases the DRCP server back to the DRCP pool prior to the sleep. When this second script is run, there is a smoothness to the output. The queries happen sequentially without noticeably being blocked.

Like with any shared resource, it is recommended to release DRCP pooled servers back to the pool when they are no longer needed by the application.

cx_Oracle 5.1 for Python is Available

cj — Tue, 29 Mar 2011 19:21:52 +0000

Anthony Tuininga has released the cx_Oracle 5.1 extension for Python. His announcement email has the details: http://mail.python.org/pipermail/python-announce-list/2011-March/008876.html.

Fast In-Memory Caching with JRuby & Jython Using Oracle Coherence

cj — Mon, 6 Dec 2010 10:27:24 +0000

My Aussie colleage Pas Apicella has been evaluating JRuby & Jython. One test was using them to access Oracle Coherence, a "replicated and distributed (partitioned) data management and caching services on top of a reliable, highly scalable peer-to-peer clustering protocol.". He blogged and shows code samples here.

cx_Oracle 5.0.4 Python driver for Oracle Database Released

cj — Wed, 28 Jul 2010 08:24:05 +0000

Anthony Tuininga has released the cx_Oracle 5.0.4 Python driver for Oracle Database.

Details are in his Sourceforge mail announcement.

Python and Ruby in Oracle Tuxedo

cj — Tue, 18 May 2010 21:53:29 +0000

Did you know you can now develop services and applications in Python or Ruby with Oracle Tuxedo? The Tuxedo team have a blog post about it at Python and Ruby in Tuxedo. I used to think of Tuxedo as a Transaction Processing Monitor but it has evolved into much more.

PyOhio - Python in Ohio, 25 & 26 July 2009

cj — Tue, 14 Jul 2009 09:29:41 +0000

PyOhio "The Free Ohio-based Python Miniconference" is on Saturday & Sunday July 25-26 at Ohio State University. On Sunday Catherine Devlin and Oracle Technology Network's Todd Trichler will have an Oracle/Python/Linux Tutorial. This will have something interesting for everyone. Even if you don't want to attend the tutorial fulltime, you can jump in for the bits important to you.

Want to learn more about Python and Oracle, or ask questions about best Python practices - then attend Catherine's Python section and ask questions. Want to know what Oracle database is about to understand its possibilities - then attend the installation part. Want to learn more about Linux and Virtualization - Todd is great on this.

Wish I was going. Send me a postcard!

Python cx_Oracle gets Database Change Notification Support

cj — Thu, 8 Jan 2009 14:58:03 +0000

Python's cx_Oracle gets Database Change Notification Support.

New Python cx_Oracle 5.0

cj — Wed, 17 Dec 2008 23:45:53 +0000

Now that Python 3000 has been released, Anthony Tuininga has announced cx_Oracle 5.0, the latest version of the extension for Oracle Database access. His announcement email contains all the details.

Among the new features, it support Oracle 11g Connection Pooling (DRCP) fully now and allows the connection class to be set. This enables maximum reuse of the Oracle pooled server processes by applications.

Python cx_Oracle 4.4.1 Released

cj — Wed, 29 Oct 2008 10:42:12 +0000

Anthony Tuinga's released cx_Oracle 4.4.1 yesterday: http://cx-oracle.sourceforge.net/. Changes are here.

OraPIG - Oracle Python Interface Generator Announced

cj — Mon, 9 Jun 2008 17:52:57 +0000

Mark Harrison, from Pixar Animation Studios, recently announced the first release of "OraPig", which creates Python interfaces for PL/SQL packages. The documentation is a good place to get an overview: http://www.markharrison.net/orapig/

The project itself is hosted at http://code.google.com/p/orapig/

cx_Oracle 4.3.3 for Python is out

cj — Thu, 4 Oct 2007 16:02:32 +0000

The cx_Oracle 4.3.3 extension module for Python was released on Tuesday. It is available on Sourceforge. Source and RPMs for various versions of Python and Oracle are available. This is the first release that is available with 11g support (though, of course, Oracle clients and databases of different releases can connect to each other).

Scripting for Documentation

cj — Thu, 26 Apr 2007 12:26:55 +0000

On his blog tahitiviews.blogspot.com, John Russell has posted a couple of exploratory articles on Python and Perl comparing them with PL/SQL. The observant person might recall that tahiti.oracle.com is a front page to Oracle's Database, Application Server and Collaboration Suite documentation. John does a lot of scripting magic with an ever changing set of manuals to build up an excellent online documentation system.