I am having trouble getting DRCP connection pooling to work properly with Python and cx_oracle. I am using Oracle 18.104.22.168, 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/password@sid', 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.
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:
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.