0
# Session Pooling
1
2
Connection pooling for scalable applications with configurable pool sizes, connection sharing, and automatic connection management for high-performance Oracle Database access.
3
4
## Capabilities
5
6
### Pool Creation and Configuration
7
8
Create and configure session pools for optimal connection management.
9
10
```python { .api }
11
class SessionPool:
12
def __init__(self, user: str, password: str, dsn: str, min: int, max: int,
13
increment: int, connectiontype=Connection, threaded=True,
14
getmode=SPOOL_ATTRVAL_NOWAIT, events=False, homogeneous=True,
15
externalauth=False, encoding=None, nencoding=None, edition=None,
16
timeout=0, waitTimeout=0, maxLifetimeSession=0,
17
sessionCallback=None, maxSessionsPerShard=0, stmtcachesize=20,
18
ping_interval=60, **kwargs):
19
"""
20
Create session pool for connection management.
21
22
Parameters:
23
- user (str): Database username
24
- password (str): Database password
25
- dsn (str): Data source name
26
- min (int): Minimum number of connections in pool
27
- max (int): Maximum number of connections in pool
28
- increment (int): Number of connections to create when pool is exhausted
29
- connectiontype: Connection class to use (default: Connection)
30
- threaded (bool): Enable thread safety
31
- getmode (int): Connection acquisition mode
32
- events (bool): Enable Oracle events
33
- homogeneous (bool): All connections use same credentials
34
- externalauth (bool): Use external authentication
35
- encoding (str): Character encoding
36
- timeout (int): Session timeout in seconds
37
- waitTimeout (int): Time to wait for connection (seconds)
38
- maxLifetimeSession (int): Maximum session lifetime (seconds)
39
- sessionCallback: Function called when session created/returned
40
- stmtcachesize (int): Statement cache size per connection
41
- ping_interval (int): Connection ping interval (seconds)
42
"""
43
```
44
45
Usage examples:
46
47
```python
48
# Basic pool creation
49
pool = cx_Oracle.SessionPool("scott", "tiger", "localhost:1521/XE",
50
min=2, max=10, increment=2)
51
52
# Pool with custom configuration
53
pool = cx_Oracle.SessionPool(
54
user="hr",
55
password="password",
56
dsn="prod_db",
57
min=5,
58
max=50,
59
increment=5,
60
timeout=300, # 5 minute session timeout
61
waitTimeout=10, # 10 second wait for connection
62
stmtcachesize=50, # Larger statement cache
63
ping_interval=30 # Ping every 30 seconds
64
)
65
66
# Pool with session callback
67
def session_callback(connection, requestedTag, actualTag):
68
"""Called when session acquired from pool"""
69
print(f"Session acquired with tag: {actualTag}")
70
connection.current_schema = "HR"
71
72
pool = cx_Oracle.SessionPool("user", "pass", "dsn", 2, 10, 2,
73
sessionCallback=session_callback)
74
```
75
76
### Connection Management
77
78
Acquire and release connections from the pool.
79
80
```python { .api }
81
class SessionPool:
82
def acquire(self, user=None, password=None, cclass=None, purity=None,
83
tag=None, matchanytag=False, shardingkey=[],
84
supershardingkey=[]) -> Connection:
85
"""
86
Acquire connection from pool.
87
88
Parameters:
89
- user (str): Override username for heterogeneous pools
90
- password (str): Override password for heterogeneous pools
91
- cclass (str): Connection class for DRCP
92
- purity (int): Session purity (ATTR_PURITY_NEW, ATTR_PURITY_SELF)
93
- tag (str): Requested session tag
94
- matchanytag (bool): Accept any tagged session
95
- shardingkey (list): Sharding key for sharded databases
96
- supershardingkey (list): Super sharding key
97
98
Returns:
99
Connection object from pool
100
"""
101
102
def release(self, connection: Connection, tag=None) -> None:
103
"""
104
Return connection to pool.
105
106
Parameters:
107
- connection: Connection object to return
108
- tag (str): Tag to associate with returned session
109
"""
110
111
def drop(self, connection: Connection) -> None:
112
"""
113
Drop connection from pool permanently.
114
115
Parameters:
116
- connection: Connection object to drop
117
"""
118
119
def close(self, force=False) -> None:
120
"""
121
Close pool and all connections.
122
123
Parameters:
124
- force (bool): Force close even with active connections
125
"""
126
```
127
128
Usage examples:
129
130
```python
131
# Basic connection acquisition
132
conn = pool.acquire()
133
try:
134
cursor = conn.cursor()
135
cursor.execute("SELECT * FROM employees")
136
# Use connection...
137
finally:
138
pool.release(conn)
139
140
# Context manager (automatic release)
141
with pool.acquire() as conn:
142
cursor = conn.cursor()
143
cursor.execute("SELECT COUNT(*) FROM employees")
144
count = cursor.fetchone()[0]
145
print(f"Employee count: {count}")
146
147
# Tagged session management
148
with pool.acquire(tag="readonly") as conn:
149
conn.current_schema = "HR_READONLY"
150
# Connection returned with "readonly" tag
151
152
# Acquire tagged session (reuses previous session if available)
153
with pool.acquire(tag="readonly") as conn:
154
# Gets same session configuration as above
155
pass
156
157
# Drop problematic connection
158
conn = pool.acquire()
159
try:
160
# If connection has issues...
161
pool.drop(conn) # Permanently remove from pool
162
except:
163
# Don't release back to pool
164
pool.drop(conn)
165
```
166
167
### Pool Properties and Monitoring
168
169
Monitor pool status and configuration.
170
171
```python { .api }
172
class SessionPool:
173
@property
174
def username(self) -> str:
175
"""Pool username"""
176
177
@property
178
def dsn(self) -> str:
179
"""Data source name"""
180
181
@property
182
def tnsentry(self) -> str:
183
"""TNS entry (alias for dsn)"""
184
185
@property
186
def max(self) -> int:
187
"""Maximum number of connections"""
188
189
@property
190
def min(self) -> int:
191
"""Minimum number of connections"""
192
193
@property
194
def increment(self) -> int:
195
"""Connection increment value"""
196
197
@property
198
def opened(self) -> int:
199
"""Current number of opened connections"""
200
201
@property
202
def busy(self) -> int:
203
"""Current number of busy connections"""
204
205
@property
206
def timeout(self) -> int:
207
"""Session timeout in seconds"""
208
209
@property
210
def getmode(self) -> int:
211
"""Connection acquisition mode"""
212
213
@property
214
def homogeneous(self) -> bool:
215
"""Whether pool is homogeneous"""
216
217
@property
218
def name(self) -> str:
219
"""Pool name"""
220
221
@property
222
def stmtcachesize(self) -> int:
223
"""Statement cache size per connection"""
224
225
@property
226
def ping_interval(self) -> int:
227
"""Connection ping interval in seconds"""
228
```
229
230
Usage examples:
231
232
```python
233
# Monitor pool status
234
print(f"Pool status: {pool.busy}/{pool.opened}/{pool.max} (busy/opened/max)")
235
print(f"Pool efficiency: {(pool.busy/pool.opened)*100:.1f}%")
236
237
# Pool configuration info
238
print(f"Min connections: {pool.min}")
239
print(f"Max connections: {pool.max}")
240
print(f"Increment: {pool.increment}")
241
print(f"Timeout: {pool.timeout} seconds")
242
243
# Check if pool needs tuning
244
if pool.busy == pool.max:
245
print("Warning: Pool at maximum capacity")
246
elif pool.opened > pool.min and pool.busy < pool.min:
247
print("Info: Pool may be oversized")
248
```
249
250
## Pool Acquisition Modes
251
252
Control how connections are acquired from the pool.
253
254
```python { .api }
255
SPOOL_ATTRVAL_WAIT: int # Wait for available connection
256
SPOOL_ATTRVAL_NOWAIT: int # Don't wait, raise error if none available
257
SPOOL_ATTRVAL_FORCEGET: int # Force new connection beyond max limit
258
SPOOL_ATTRVAL_TIMEDWAIT: int # Wait with timeout for connection
259
```
260
261
Usage examples:
262
263
```python
264
# Pool that waits for connections
265
wait_pool = cx_Oracle.SessionPool("user", "pass", "dsn", 2, 5, 1,
266
getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT)
267
268
# Pool that fails immediately if no connections available
269
nowait_pool = cx_Oracle.SessionPool("user", "pass", "dsn", 2, 5, 1,
270
getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT)
271
272
# Try to acquire with specific behavior
273
try:
274
conn = nowait_pool.acquire()
275
except cx_Oracle.DatabaseError:
276
print("No connections immediately available")
277
```
278
279
## Session Purity
280
281
Control session state and reusability.
282
283
```python { .api }
284
ATTR_PURITY_DEFAULT: int # Default purity
285
ATTR_PURITY_NEW: int # Require new session
286
ATTR_PURITY_SELF: int # Self-contained session
287
```
288
289
Usage examples:
290
291
```python
292
# Require fresh session (no previous state)
293
conn = pool.acquire(purity=cx_Oracle.ATTR_PURITY_NEW)
294
295
# Self-contained session (cleaned up automatically)
296
conn = pool.acquire(purity=cx_Oracle.ATTR_PURITY_SELF)
297
```
298
299
## Advanced Pool Features
300
301
### Heterogeneous Pools
302
303
Create pools that support multiple user credentials.
304
305
```python
306
# Create heterogeneous pool
307
hetero_pool = cx_Oracle.SessionPool(None, None, "dsn", 2, 10, 2,
308
homogeneous=False,
309
externalauth=True)
310
311
# Acquire connections with different credentials
312
hr_conn = hetero_pool.acquire(user="hr", password="hr_pass")
313
sales_conn = hetero_pool.acquire(user="sales", password="sales_pass")
314
```
315
316
### DRCP (Database Resident Connection Pooling)
317
318
Use Oracle's Database Resident Connection Pooling for additional scalability.
319
320
```python
321
# Connect to DRCP-enabled service
322
drcp_dsn = "hostname:1521/service_name:POOLED"
323
pool = cx_Oracle.SessionPool("user", "pass", drcp_dsn, 0, 5, 1)
324
325
# Use connection class for better pooling
326
conn = pool.acquire(cclass="MYAPP")
327
```
328
329
### Session Callbacks
330
331
Implement session callbacks for connection initialization.
332
333
```python
334
def init_session(connection, requestedTag, actualTag):
335
"""Initialize session when acquired from pool"""
336
# Set session-specific configuration
337
connection.current_schema = "APP_SCHEMA"
338
339
# Set session parameters
340
cursor = connection.cursor()
341
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
342
cursor.close()
343
344
# Log session acquisition
345
print(f"Session initialized with tag: {actualTag}")
346
347
pool = cx_Oracle.SessionPool("user", "pass", "dsn", 2, 10, 2,
348
sessionCallback=init_session)
349
```
350
351
## Pool Sizing Guidelines
352
353
Recommendations for pool configuration:
354
355
```python
356
# Conservative pool for low-traffic applications
357
small_pool = cx_Oracle.SessionPool("user", "pass", "dsn",
358
min=2, max=10, increment=2)
359
360
# Aggressive pool for high-traffic applications
361
large_pool = cx_Oracle.SessionPool("user", "pass", "dsn",
362
min=10, max=100, increment=10)
363
364
# Web application pool (typical sizing)
365
web_pool = cx_Oracle.SessionPool("user", "pass", "dsn",
366
min=5, # Always keep 5 connections
367
max=50, # Scale up to 50 under load
368
increment=5, # Add 5 at a time
369
timeout=300, # 5 minute session timeout
370
waitTimeout=30) # Wait up to 30s for connection
371
```
372
373
## Error Handling
374
375
Pool-specific error handling patterns:
376
377
```python
378
try:
379
conn = pool.acquire()
380
except cx_Oracle.DatabaseError as e:
381
error_obj, = e.args
382
if error_obj.code == 24496: # Pool exhausted
383
print("Connection pool exhausted")
384
elif error_obj.code == 24457: # Pool closed
385
print("Connection pool has been closed")
386
else:
387
print(f"Database error: {error_obj.message}")
388
389
# Always release connections, even on error
390
conn = None
391
try:
392
conn = pool.acquire()
393
# Use connection...
394
except Exception as e:
395
print(f"Error: {e}")
396
finally:
397
if conn:
398
pool.release(conn)
399
```
400
401
## Pool Cleanup
402
403
Proper pool cleanup and resource management:
404
405
```python
406
try:
407
# Use pool for application lifetime
408
pool = cx_Oracle.SessionPool("user", "pass", "dsn", 5, 50, 5)
409
410
# Application logic using pool...
411
412
finally:
413
# Clean shutdown
414
if pool:
415
pool.close(force=True) # Close all connections
416
```