[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

Overview

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.

(Reference) Set MINSIZE and MAXSIZE of DRCP (database resident connection pooling) of Oracle Database to 1 and try to connect from multiple sessions.

Client-side connection pool

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 connection pool and homologous connection pool

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

[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
Introduction to PyQt4 Part 1
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
Introduction to discord.py (3) Using voice
Introduction to Ansible Part ③'Inventory'
Series: Introduction to cx_Oracle Contents
Introduction to Ansible Part ④'Variable'
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
Introduction to Socket API Learned in C Part 2 Client Edition
Introduction to Socket API Learned in C Part 3 TCP Server / Client # 1
Introduction to Ansible Part 2'Basic Grammar'
Introduction to Python Hands On Part 1
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
Introduction to Ansible Part 1'Hello World !!'
Introduction to Socket API Learned in C Part 4 UDP Server / Client # 1
Python beginners publish web applications using machine learning [Part 2] Introduction to explosive Python !!
Introduction to Discrete Event Simulation Using Python # 1
Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)
[PyTorch] Introduction to document classification using BERT
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
Introduction to Discrete Event Simulation Using Python # 2
Introduction to Tornado (3): Development using templates [Practice]
Kaggle: Introduction to Manual Feature Engineering Part 1
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to cx_Oracle] (16th) Handling of LOB types
[PyTorch] Introduction to Japanese document classification using BERT
[Introduction to cx_Oracle] (5th) Handling of Japanese data
Introduction to Tornado (2): Introduction to development using templates-Dynamic page generation-
Introduction to Scapy ② (ICMP, HTTP (TCP) transmission using Scapy)