7 Replies Latest reply on Aug 28, 2014 5:35 PM by cj

    DRCP with cx_Oracle doesn't appear to work as expected

    John Papanastasiou

      I'm trying to use DRCP in Oracle 11.2 with a Python client using cx_Oracle 5.1.2.


      If I create the connection by calling cx_Oracle.connect and adding a cclass argument and a purity argument, then the record in sys.v_$cpool_cc_stats with cclass_name set to my cclass will show an increase in num_requests and num_misses corresponding to the number of calls I make, with num_hits staying at 0.


      connection = cx_Oracle.connect(user=db['USER'], password=db['PASSWORD'], dsn=db['NAME'], cclass=db['OPTIONS']['CCLASS'], purity=cx_Oracle.ATTR_PURITY_SELF)


      If however I create an instance of a cx_Oracle.SessionPool, then pass that instance into the same cx_Oracle.connect call as an extra 'pool' argument, then num_misses goes up by 1, and num_hits goes up by num_requests - 1 (I assume this means the first request is a new connection, all the rest are using that connection).


      pool = cx_Oracle.SessionPool(user=db['USER'], password=db['PASSWORD'], dsn=db['NAME'], min=1, max=2, increment=1)

      connection = cx_Oracle.connect(user=db['USER'], password=db['PASSWORD'], dsn=db['NAME'], pool=pool, cclass=db['OPTIONS']['CCLASS'], purity=cx_Oracle.ATTR_PURITY_SELF)

      Is this correct?  Do I need to be creating a SessionPool client side, then using that to acquire and release connections?


      This article doesn't mention SessionPool at all.  I came across SessionPool in this post, but that isn't official documentation.


      FWIW, when I run select * from dba_cpool_info, I get the following:


        • 1. Re: DRCP with cx_Oracle doesn't appear to work as expected

          I believe you will need to create a session pool.

          Can you post a complete code snippet using cx_Oracle.SessionPool that exhibits the behavior you see?

          • 2. Re: DRCP with cx_Oracle doesn't appear to work as expected
            John Papanastasiou

            I've written the following script to help demonstrate.  It requires python 3.  If you only have python 2, then it must be at least 2.5 (as it uses contextlib.contextmanager) and you will have to change all the print function calls to print statements (if you have python 2.6, you can add from future import print_function as print, although I haven't tested this).


            When running the script, once with the SessionPool, and once without, I have the following records in sys.v_$cpool_cc_stats:



            The script requires 2 arguments, the DSN and USER, and has an optional argument --pool which if included will use a SessionPool.  Example usage (assuming code is saved to file test_drcp.py, my_drcp_db is the TNS entry referring to an Oracle database with DRCP started, and some_user is a user with read access in that database:


            ./test_drcp.py my_drcp_db some_user

            ./test_drcp.py --pool my_drcp_db some_user


            Copy the following code to a file, modify the #! to point to the python interpreter in an environment with cx_Oracle, and make sure it's executable.


            #! /home/john/envs/drcptest/bin/python


            import os

            import time

            import argparse

            from getpass import getpass

            from contextlib import contextmanager


            import cx_Oracle




            def oracle_db(use_pool, dsn, user, password):

                if use_pool:

                    pool = cx_Oracle.SessionPool(

                        user=user, password=password, dsn=dsn, min=1, max=2, increment=1)

                    connection = cx_Oracle.connect(

                        user=user, password=password, dsn=dsn, pool=pool,

                        cclass="WITH_POOL", purity=cx_Oracle.ATTR_PURITY_SELF)


                    connection = cx_Oracle.connect(

                        user=user, password=password, dsn=dsn, cclass="WITHOUT_POOL",


                cursor = connection.cursor()


                    yield cursor


                except cx_Oracle.OracleError:




                    if use_pool:






            def run_query_and_sleep(use_pool, dsn, user, password):

                print('> Starting {}'.format(os.getpid()))

                with oracle_db(use_pool, dsn=dsn, user=user, password=password) as cursor:

                    print('> Querying {}'.format(os.getpid()))

                    cursor.execute("select to_char(systimestamp) from dual")


                print('> Sleeping {}'.format(os.getpid()))


                print('> Finished {}'.format(os.getpid()))



            def main(*args, **kwargs):

                for x in range(100):

                    pid = os.fork()

                    if not pid:





            if __name__ == '__main__':

                parser = argparse.ArgumentParser('Test connection pooling with Oracle DRCP')

                parser.add_argument('dsn', help='TNS entry to use')

                parser.add_argument('user', help='Username to use for the connection')

                parser.add_argument('--pool', action='store_true', help='Use session pool')

                args = parser.parse_args()

                password = getpass('Enter password for {}> '.format(args.user))

                main(use_pool=args.pool, dsn=args.dsn, user=args.user, password=password)

            • 3. Re: DRCP with cx_Oracle doesn't appear to work as expected
              Avinash Nandakumar-Oracle

              The first way of connecting to drcp has a bug and fix will be added soon.

              The second connection using SessionPool is showing expected results.


              In both the cases the behaviour should be the same. NUM_HITS should go up and NUM_MISSES should not increase if purity self and connection class are used to connect to the DB.

              • 4. Re: DRCP with cx_Oracle doesn't appear to work as expected
                John Papanastasiou

                Thank you for taking the time to read this thread.  Is it a bug in cx_Oracle?  Do you have a link to the ticket associated with the bug you can share? 

                • 5. Re: DRCP with cx_Oracle doesn't appear to work as expected

                  Yes, it is a bug in cx_Oracle and a fix is in progress.

                  It has been reported in a different context (fix is the same) here: https://bitbucket.org/anthony_tuininga/cx_oracle/issue/2/use-of-cclass-causes-connection-leaks

                  1 person found this helpful
                  • 6. Re: DRCP with cx_Oracle doesn't appear to work as expected

                    Anthony just merged Avinash's first fix: https://bitbucket.org/anthony_tuininga/cx_oracle/commits/58eec36e9c1d108eff784d31aa64f9d345c08190

                    Avinash is continuing to look at some edge cases/leaks.

                    • 7. Re: DRCP with cx_Oracle doesn't appear to work as expected

                      To followup, the leak appears to be in the Oracle client library.  It was not seen with 11.2 or client libraries.