python에서 oracle db에 연결할 때 cs_Oracle이란 라이브러리를 사용하게 된다. 이 때 cursor, connection을 매번 호출하는 것보다 pool을 만들어놓고 재사용하면 효율이 좋아진다고 해서 알아보게 되었다. cx_Oracle 공식문서인 Connecting to Oracle Database를 번역했습니다.

시작 전 connection과 cursor 기본개념

Connection 객체로부터 cursor() 메서드를 호출하여 Cursor객체를 가져오게 된다. Cursor는 Fetch 동작, 즉 데이터를 데이터 조회 동작을 관리한다. Cursor의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 보낸다.(출처 : 예제로 배우는 파이썬 프로그래밍-MySQL쿼리)

sql문장의 리턴값은 cursor 객체에 들어있고 cursor.fetchone(), cursor.fetchmany(size), cursor.fetchall()이라는 친구들로 원하는 만큼만 꺼내올 수 있다. cursor객체에서 데이터를 가져오고 싶으면 fetch 메소드를 사용해서 가져와야 한다.(출처 : Min-Ho’s log

cursor.fetchall() 쿼리 결과의 모든 row을 튜플의 리스트 형태로 리턴한다. fetch할 것이 없으면 빈 리스트를 리턴한다.

cursor.fetchmany(size) size만큼의 row를 리턴한다. 반복해서 호출할 때마다 다음 셋을 튜플의 리스트 형태로 리턴한다. 더이상 row가 없다면 빈 리스트를 리턴한다.

cursor.fetchone() 하나만 리턴한다. row가 더이상 없다면 None을 리턴한다.

출처

Database Connection 생성/설정

cx_Oracle을 이용해 Oracle 데이터베이스에 연결하는 방법은 두 가지가 있다.

1. Standalone connections

어플리케이션이 데이터베이스에 싱글 유저 세션을 유지할 때 유용함. cx_Oracle.connect() 또는 alias인 cx_Oracle.Connection()으로 생성.

Example) Standalone Connection to Oracle Database

import cx_Oracle
userpwd = ". . ."  # 패스워드는 프롬프트로부터 받거나 환경변수로 받으면 된다
connection = cx_Oracle.connect(user="hr", 
                               password=userpwd,
                               dsn="dbhost.example.com/orclpdb1",  # data source name
                               encoding="UTF-8")

Closing Connections

연결이 더 이상 필요하지 않을 때 Connection.close()를 호출하여 연결을 해제해야 한다. 또는 연결에 대한 참조가 범위를 벗어나면(references go out of scope라고 표현) 자동으로 정리되도록 해도 된다. 또는 with 블록을 사용해도 된다.

with cx_Oracle.connect(user=user, 
                       password=password,
                       dsn="dbhost.example.com/orclpdb1",
                       encoding="UTF-8") as connection:
    cursor = connection.cursor()
    cursor.execute("insert into SomeTable values (:1, :2)",
                   (1, "Some string"))
    connection.commit()

이런식으로 블록으로 짜면 블록이 끝날때 connection은 닫히게 된다. 물론 블록 밖에서 connection 변수는 사용할 수 없다. 커넥션을 즉시 닫는 것은 커넥션 풀을 사용할 때 매우 중요하다. 다른 pool 유저가 재사용 할 수 있도록.

Connection Strings

cx_Oracle.connect() 과 cx_Oracle.SessionPool()에는 data source name을 나타내는 파라미터 dsn이 있다. 이는 Oracle Database connection string(string은 방법, 코드라고 보면 될 것 같다)이라고 해서 연결할 데이터베이스를 식별하는 역할을 한다. dsn string은 다음 중 하나로 만들 수 있다.

An Oracle Easy Connect string
An Oracle Net Connect Descriptor string
A Net Service Name mapping to a connect descriptor

하나씩 알아보자.

Easy Connect Syntax for Connection Strings

가장 간단한 connection string이다. tnsnames.ora같은 configuration 파일이 필요없다.

Example) 예를들어 서비스가 orclpdb1이고 host가 dbhost.example.com이면 다음과 같이 사용한다.

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               dsn="dbhost.example.com/orclpdb1",
                               encoding="UTF-8")

디폴트 port가 없는 데이터 베이스라면 dsn은 "dbhost.example.com:1984/orclpdb1"처럼 써야한다.

Easy Connect Syntax는 예전 sid를 사용할 수 없고 service name을 지원한다.

Oracle Net Connect Descriptor Strings

cx_Oracle.makedsn() 함수는 cx_Oracle.connect() 과 cx_Oracle.SessionPool()의 dsn 파라미터, 즉 connect descriptor string을 만드는데 사용된다. makedsn()함수는 데이터베이스의 hostname, port 번호, service name을 인자로 받는다.

Example) 예를들어 서비스가 orclpdb1이고 host가 dbhost.example.com이면 다음과 같이 사용한다.

dsn = cx_Oracle.makedsn("dbhost.example.com", 1521, service_name="orclpdb1")
connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,
                               encoding="UTF-8")

service_name을 명시하는 이유는 세 번째 파라미터가 service name이 아닌 sid(system identifier)이기 때문이다. 그러나 대부분의 데이터베이스들은 service name을 주로 사용한다. dsn으로 넘어가는 인자는 다음과 같아진다.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))

Net Service Names for Connection Strings

2. Pooled connections

커넥션 풀링(Connection pooling)은 어플리케이션이 데이터베이스에 자주 연결/연결해제를 할때 성능에 있어 중요한 요소이다. 풀(Pools)은 오라클의 *고가용성(high availability) 특징을 지원하고 신뢰해야하는 어플리케이션에 추천한다. 작은 풀(small pools) 또한 단지 몇 번의 연결만 원할 때 유용할 수 있다. Pool은 어플리케이션 초기화할 때 cx_Oracle.SessionPool()로 생성하면 되고, SessionPool.acquire()을 이용해 pool로부터 connection을 가져올 수 있다.

*고가용성(High Availability) : 시스템이 연속적으로 가동되며 사용 가능한 상태를 유지하는 능력. 시스템 또는 데이터베이스가 장애, 오류, 혹은 다른 문제로부터 회복하고 사용자에게 끊김 없이 서비스를 제공할 수 있는 더 높은 신뢰성

Connection Pooling

cx_Oracle의 connection pooling은 어플리케이션들이 데이터베이스로의 connection의 pool을 생성/관리할 수 있게 한다. 하나의 connection pool은 SessionPool()로 생성할 수 있다. 일반적으로 어플리케이션 초기화중에 호출된다. Pool 생성시에 initial pool size와 maximum pool size를 넘겨준다.

pool이 더 많은 연결을 필요로 할 때, 새로운 연결이 자동으로 생성된다. 연결이 더이상 필요하지 않을 때 pool은 min으로 취소될 수 있다.

Pool이 만들어진 후, acquire()를 호출하면 connection을 얻을 수 있다. 이 connection들을 standalone connection 사용 방식과 동일하게 사용하면 된다. Pool에서 얻어진 connection은 사용하고 나면 SessionPool.release() 또는 Connection.close()를 사용하여 다시 pool로 회수할 수 있다. 그렇지 않으면 connection을 참조하는 모든 변수들이 scope를 벗어날 때 회수된다. session pool은 SessionPool.close()를 사용하면 완전히 닫을 수 있다.

SessionPool에서 session이란 ‘데이터베이스와의 연결’을 나타낸다고 보면 될 것 같다(chatgpt).

min : 세션 풀이 생성될 때 사용된 세션의 수와 세션 풀에서 관리될 최소 세션 수
max : 세션 풀이 제어할 수 있는 세션의 최대 수
increment : 추가 세션이 생성되어야 할 때, 생성될 세션의 수.

Example

# session pool 생성
pool = cx_Oracle.SessionPool(user="hr", 
                             password=userpwd,
                             dsn="dbhost.example.com/orclpdb1", 
                             min=2, max=5, increment=1, 
                             encoding="UTF-8")

# Acquire a connection from the pool
connection = pool.acquire()

# Use the pooled connection
cursor = connection.cursor()
for result in cursor.execute("select * from mytab"):
    print(result)

# Release the connection to the pool
pool.release(connection)

# Close the pool
pool.close()

SessionPool 생성할 때 getmode 옵션을 주면 acquire() 호출 시 모든 connection이 사용중이더라도 사용 가능한 상태가 될때까지 기다린다.

# Create the session pool
pool = cx_Oracle.SessionPool(user="hr", 
                             password=userpwd,
                             dsn="dbhost.example.com/orclpdb1",
                             min=2, max=5, increment=1,
                             getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT,
                             encoding="UTF-8")

SessionPool.acquire()는 리턴하기 전에 cx_Oracle은 네트워크 전송이 open된 상태인지 가벼운 체크를 한다. 만약 open 상태가 아니라면 acquire()는 해당 connection을 폐기하고 다른 connection을 리턴한다. 그치만 데이터베이스 세션이 DBA에 의해 죽었는지, 데이터베이스 리소스 매니저 quota limit에 도달했는지는 알지 못한다. 이를 해결하기 위해 acquire()는 pool 안의 사용되지 않았던 connection을 반환할 때 full round-trip ping(왕복)을 데이터베이스에 SessionPool.ping_interval 초 만큼 수행한다. 만약 ping이 실패하면 해당 connection은 버려지고 다른 connection이 얻어진다. full ping은 시간 기반이므로, 모든 실패를 잡아내지 못한다. 또한 타임아웃이나 세션 킬은 acquire()다음과 cursor.execute() 전 시간 안에 일어날 수도 있다. 이를 해결하기 위해 어플리케이션은 각각의 acquire() 이후에 에러를 체크하고 어플리케이션만의 retry를 할 필요가 있다.

Connection Pool Sizing

오라클이 추천하는 방식은 고정된 size의 connection pool을 사용하는 것이다. 즉 min과 max를 같게 하고 ìncrement를 0으로 하는 것이다. 이것이 Connection storm, 즉 여러 client가 동시에 DB 연결을 시도하여 과도한 수의 connection이 설정되는 장애 상황을 예방할 수 있다.

배치문 실행 및 Bulk 로딩

cursor.execute()는 sql 한 줄만 수행하지만, 값만 바꿔 여러번 실행할 때는 cursor.executemany()를 사용하면 된다.

data = [
    (10, 'Parent 10'),
    (20, 'Parent 20'),
    (30, 'Parent 30'),
    (40, 'Parent 40'),
    (50, 'Parent 50')
]
cursor.executemany("insert into ParentTable values (:1, :2)", data)

출처

pool로부터 확인할 수 있는 정보는 다음과 같다.

pool.busy : 현재 acquire()된 세션 수를 반환한다.

pool.opened : 현재 session pool에 의해 열린 세션 수를 반환한다.

opened와 busy의 차이 : opened는 pool에 의해 현재 열린, pool안에 있는 세션의 수를 나타낸다. pool이 현재 확보하고 있는 세션의 수. busy는 pool 속의 세션 중에서 현재 작업 중인 세션(=connection 인 것 같다), 즉 쿼리나 다른 작업을 처리 중인 세션의 수를 나타낸다.

pool = cx_Oracle.SessionPool(user=properties['user'], 
                             password=properties['passwd'],
                             dsn=dsn, 
                             min=1, max=5, increment=1, 
                             encoding="UTF-8")
print(pool.opened)  # 1
print(pool.busy)  # 0

connection = pool.acquire()
print(pool.opened)  # 1
print(pool.busy)  # 1

connection2 = pool.acquire()
print(pool.opened)  # 2
print(pool.busy)  # 2

pool.release(connection2)  # pool로 반환
print(pool.opened)  # 2
print(pool.busy)  # 1

만약 increment가 2였다면, 두 번째 세션을 얻는 connection2 = pool.acquire()를 할 때 pool.opened는 3이 된다. 2개를 여니까.