Series: Introduction to cx_Oracle Table of Contents
--Using Oracle Cloud
The standard connection method for Oracle Database is by no means fast. Rather, it tends to be ridiculed if it is slow. However, by using the connection pool function, it is possible to achieve connection performance that is comparable to other products. And now, both on the server side and the client side, Oracle Database itself provides connection pool functionality. With Oracle Database, you can benefit from connection pools in any connection environment, not just Python. The server-side connection pool function (DRCP (Database Resident Connection Pool), database resident connection pooling) is a server-side function and can be used in any environment. If you are interested in server-side settings, please see below. However, in order to use DRCP, it is necessary to specify options at the time of connection, so the part that requires coding will be explained in another time. This time, I will explain about the connection pool on the client side.
cx_Oracle has an API for using the client-side connection pool function of Oracle Client. When using a client-side connection pool, first create a connection pool (session pool). The connection is completed by then using the Acquire () method of the Connection object instead of the connect () method of the cx_Oracle object to acquire the session from the pool.
sample13a.py
import cx_Oracle
import time
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
#Normal connection speed measurement
t1 = time.time()
connection = cx_Oracle.connect(USERID, PASSWORD, DESTINATION)
t2 = time.time()
connection.close()
print(f"Connection time when the pool is not in use: {t2 - t1}Seconds")
#Connection pool connection speed measurement
pool = cx_Oracle.SessionPool(USERID, PASSWORD, DESTINATION, min=1, max=2, increment=1)
t1 = time.time()
connection = pool.acquire()
t2 = time.time()
pool.release(connection)
print(f"Connection time when using the pool: {t2 - t1}Seconds")
The 6th line from the bottom calls cx_Oracle.SessionPool () to create a session pool (connection pool). The first three arguments are similar to Connection.connect (). The fourth and subsequent arguments specify the size of the connection pool to be created. min is the minimum number of connections, max is the maximum number of connections, and increment is the increment value for increasing the number of sessions from min to max. The default values for all three are on the sample. If your application uses multithreading, specify True for the thread argument. It is recommended to keep the min and max values the same and keep the number of sessions fixed to avoid performance overhead due to the increase or decrease in the number of sessions. Adjust the specified number according to the application operating status. To terminate (release) the secured connection, call the release () method. Specify the connection that acquired () (in this example, "connection") as an argument.
The execution result cannot be posted due to adult circumstances, but if you try it, you can see that the connection is overwhelmingly faster when using the connection pool. However, this sample does not include the session pool creation time in the connection time. If you include it, regardless of the value in the above example, the larger the min value, the longer it will take to create the pool, so the normal connection will be faster. Regarding the proper use of normal connection and connection pool usage, it is possible to connect the normal connection to the DB many times, such as a serverless application, for applications such as batch processing applications where the number of connections is often one. I think it's a good idea to use connection pools for expected applications.
Heterogeneous and Homogeneous in the title indicate whether all connected users are the same (homogeneous) or not (heterogeneous) in the same connection pool. The default is homogeneous. In the case of the same type connection pool described so far, the connection users are fixed, but in the case of the heterogeneous connection pool described below, it is possible to mix users. While flexible, the connection speed is slower than similar connection pools because you have to authenticate each time. cx_Oracle.SessionPool () has a default True argument called homogeneous. If you change this to False and connect, you can specify different users for each session. Specify the user name and password in Connection.acquire () instead of cx_Oracle.SessionPool ().
sample13b.py
import cx_Oracle
import time
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
#Homogeneous connection pool
pool = cx_Oracle.SessionPool(USERID, PASSWORD, DESTINATION, min=2, max=2)
t1 = time.time()
connection = pool.acquire()
t2 = time.time()
pool.release(connection)
print(f"Connection time when using the same type connection pool: {t2 - t1}Seconds")
#Heterogeneous connection pool
pool = cx_Oracle.SessionPool(dsn=DESTINATION, min=2, max=2, homogeneous=False)
t1 = time.time()
connection = pool.acquire(user=USERID, password=PASSWORD)
t2 = time.time()
pool.release(connection)
print(f"Connection time when using heterogeneous connection pool: {t2 - t1}Seconds")
Please note that the argument names (user, password, dsn, respectively) are required for the arguments of the user name, password, and connection destination information.
Recommended Posts