Thursday Mar 21, 2013

Python cx_Oracle and Oracle 11g DRCP Connection Pooling

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.

Tuesday Dec 11, 2012

Excitement! Updated Underground PHP and Oracle Manual is Available for Download

We're thrilled to have a major update of the free Underground PHP and Oracle Manual released on OTN.

The Underground PHP and Oracle Manual is designed to bridge the gap between the many PHP scripting language and the many Oracle Database books available. It contains unique material about PHP's OCI8 extension for Oracle Database, and about other components in the PHP-Oracle ecosystem. It shows PHP developers how to use PHP and Oracle together, efficiently and easily.

The book has been completely refreshed. It has been updated for Oracle XE 11g and the latest PHP OCI8 extension. There are new chapters about using PHP with Oracle TimesTen, NetBeans and Oracle Tuxedo. There is also a new chapter about installing PHP on Oracle Solaris. The book now clocks in at 347 pages of great content.

Acknowledgements are due to all those who have helped with this and previous editions of the book. Thanks to the product teams that assisted with brand new content. In particular Craig Mohrman contributed the chapter about PHP on Solaris. Jeffry Rubinoff contributed the base text for the chapter on PHP and NetBeans.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today