Forum Stats

  • 3,769,928 Users
  • 2,253,031 Discussions


Python DRCP connection pooling problem: v$cpool_cc_stats - num_hits = 0

438494 Member Posts: 21
edited Dec 19, 2009 3:28PM in Python
I am having trouble getting DRCP connection pooling to work properly with Python and cx_oracle. I am using Oracle, Python 3.0.1, and cx_oracle 5.0.2-11g, on Windows 32-bit. I have started the connection pool, created a tns_names entry with server = pooled. I am connecting as follows (using real values for username, password, and sid): db = cx_Oracle.connect('username/[email protected]', cclass = 'TEST1.CLASS').

I am able to connect. However, performance (connect speed) is no different. When I query v$cpool_cc_stats, I see only one row. The value for cclass_name is USER.SHARED (with my real username), num_requests is 16, num_hits is 0, num_misses is 16 (the 16s will go up each time I connect). It appears to me that the connection pool is working, but it is not ever re-using a connection and thus is providing no value. It is as if it doesn't see that I set a value for cclass.

Has anyone been able to make DRCP work with Python and cx_oracle? Did you get num_hits greater than zero? If num_hits = zero is normal, then is there any benefit to using DRCP? Does anyone know what I am doing wrong? Thanks.

- Jack


  • 438494
    438494 Member Posts: 21
    edited Dec 2, 2009 4:55PM
    I made some progress on this issue, but it is still not solved completely. I tried setting a variable:

    my_purity = cx_Oracle.ATTR_PURITY_SELF

    (I now believe purity must be set to SELF in order to re-use a pooled server).

    I got an error:

    AttributeError: 'module' object has no attribute 'ATTR_PURITY_SELF'

    I decided I might have an install issue. I had installed cx_Oracle-5.0.2-10g.win32-py3.0.msi, then cx_Oracle-5.0.2-11g.win32-py3.0.msi, then I uninstalled the first one (10g). Since cx_Oracle was still working, I assumed I was ok. I went back and uninstalled the 11g version, tried cx_Oracle, it didn't work (successful complete uninstall?). Then I re-installed the 11g version.

    All of a sudden Oracle does recognize my connection class. I got a second row in v$cpool_cc_stats. Now I have two rows USER.SHARED and USER.APP_01. When I connect, the APP_01 counters go up. Clearly I did have an install issue. However... I am still not able to get num_hits to go above zero. Here is what I am using now:

    my_cclass = 'APP_01'
    my_purity = cx_Oracle.ATTR_PURITY_SELF
    db = cx_Oracle.connect(my_user, my_password, my_dsn, cclass = my_cclass, purity = my_purity)

    Anyone know what I am doing wrong? Anyone able to get num_hits greater than zero?

    Edited by: jmulho on Dec 2, 2009 1:55 PM
  • What is your pool configuration?
    You can do 'select * from dba_cpool_info'

    And, how are you doing the connections? One after another by re-running the same application? Or while app1 is connected, do you run app2 (or another invocation of app1 separately)?
  • 438494
    438494 Member Posts: 21
    My configuration is the default.

    I am doing the connections one after another by re-running the same application. User keystrokes on web page, that sends ajax request to Apache, that invokes python app through mod_wsgi, that uses cx_oracle to connect, execute PL/SQL, disconnect. Next user keystroke does the same thing again with different parameter.

    I have also tried connecting and disconnecting multiple times within one invokation of the python app, but that too just made num_misses go up.
This discussion has been closed.