7 Replies Latest reply: Aug 28, 2014 12:35 PM by cj RSS

    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:

       

      "CONNECTION_POOL""STATUS""MINSIZE""MAXSIZE""INCRSIZE""SESSION_CACHED_CURSORS""INACTIVITY_TIMEOUT""MAX_THINK_TIME""MAX_USE_SESSION""MAX_LIFETIME_SESSION"
      "SYS_DEFAULT_CONNECTION_POOL""ACTIVE"44022030012050000086400
        • 1. Re: DRCP with cx_Oracle doesn't appear to work as expected
          cj

          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:

            "CCLASS_NAME""NUM_REQUESTS""NUM_HITS""NUM_MISSES""NUM_WAITS""WAIT_TIME""CLIENT_REQ_TIMEOUTS""NUM_AUTHENTICATIONS"
            "DEV_DRCP.WITHOUT_POOL"10001006200100
            "DEV_DRCP.WITH_POOL"1008812000100

             

            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

             

             

            @contextmanager

            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)

                else:

                    connection = cx_Oracle.connect(

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

                        purity=cx_Oracle.ATTR_PURITY_SELF)

                cursor = connection.cursor()

                try:

                    yield cursor

                    connection.commit()

                except cx_Oracle.OracleError:

                    connection.rollback()

                finally:

                    cursor.close()

                    if use_pool:

                        pool.release(connection)

                    else:

                        connection.close()

             

             

            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(cursor.fetchall())

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

                time.sleep(10)

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

             

             

            def main(*args, **kwargs):

                for x in range(100):

                    pid = os.fork()

                    if not pid:

                        run_query_and_sleep(**kwargs)

                        os._exit(0)

             

             

            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
                  kmohan

                  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

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

                    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
                      cj

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