Forum Stats

  • 3,770,288 Users
  • 2,253,090 Discussions
  • 7,875,388 Comments

Discussions

DRCP with cx_Oracle doesn't appear to work as expected

John Papanastasiou
John Papanastasiou Member Posts: 3
edited Aug 28, 2014 1:35PM in Python

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

Best Answer

  • Avinash Nandakumar-Oracle
    Avinash Nandakumar-Oracle Member Posts: 1
    Accepted Answer

    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.

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee

    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?

  • 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)

  • Avinash Nandakumar-Oracle
    Avinash Nandakumar-Oracle Member Posts: 1
    Accepted Answer

    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.

  • 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? 

  • Kmohan-Oracle
    Kmohan-Oracle Member Posts: 120

    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

    John Papanastasiou
  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee

    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.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,643 Employee

    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.

This discussion has been closed.