python-oracledb

Pipelining allows an application to send multiple, independent statements to Oracle Database in a single call. This reduces network round-trips, which is most beneficial when performing many small operations in rapid succession or when the network to the database is slow.

Key Characteristics:

• Sequential Execution: Operations are executed sequentially by the database, not concurrently. Pipelining improves application responsiveness by allowing the application to perform non-database work while the database processes the pipeline.
• Thin Mode & asyncio: Pipelining is only supported in python-oracledb Thin mode with asyncio.
• Database Requirement: True pipelining (with round-trip reduction) requires Oracle AI Database 26ai or later. On older databases, operations are executed sequentially by the driver, providing no performance benefit but allowing for code portability.
• Limitations: Query results or OUT binds from one operation cannot be passed to subsequent operations within the same pipeline.

Implicit connection pooling is an option for applications that cause excessive database server load due to many standalone connections but cannot be easily rewritten to use python-oracledb connection pooling.

It allows application connections to share pooled servers in DRCP (Database Resident Connection Pooling) or Oracle Connection Manager in Traffic Director Mode's (CMAN-TDM) Proxy Resident Connection Pooling (PRCP).

Key Characteristics:

• No code changes required: Applications do not need to be modified to acquire or release connections explicitly.
• Automatic lifecycle: Connections are internally acquired from the pool when used and released back to the pool when not in use (potentially between oracledb.connect() and Connection.close()).
• Availability: Works in both Thin and Thick modes.
• Requirements: Requires Oracle Database 26ai. Thick mode also requires Oracle Client version 23 libraries.

Warning: It is recommended to use python-oracledb's local connection pooling instead of implicit connection pooling whenever possible, as local pooling provides better control over server reuse.

Database Resident Connection Pooling (DRCP) enables database resource sharing for applications using a large number of connections across multiple client processes or application servers. Instead of each Python connection using a dedicated database server process, DRCP allows pooling of these processes, reducing memory requirements on the database host.

When to use DRCP:

• Applications sharing the same database credentials and similar session settings (e.g., date formats).
• Applications that acquire a connection, perform short-duration work, and release it quickly.
• Applications that cannot use a local driver connection pool but still need performance benefits from process reuse.

Best Practices:

• Use DRCP in conjunction with python-oracledb's local connection pool (oracledb.create_pool() or oracledb.create_pool_async()) for maximum efficiency.
• Avoid using DRCP for long-running operations.
• Always specify a connection class (cclass) to allow optimal session reuse.

Connection pooling improves performance by reusing existing database connections instead of the expensive process of creating new ones. python-oracledb provides several pooling solutions:

• Driver Connection Pools: Managed by the driver layer. Best for applications with many users performing short-duration database work. Created via oracledb.create_pool() or oracledb.create_pool_async(). Recommended for most performance and scalability needs.
• DRCP (Database Resident Connection Pooling): Pools server processes on the database host. Useful for a large number of application connections (e.g., multiple application processes). It is recommended to use DRCP in conjunction with a driver connection pool.
• PRCP (Proxy Resident Connection Pooling): Handled by Oracle's mid-tier connection proxy (CMAN-TDM).
• Implicit Connection Pooling: Automatically detects when applications are not performing database work and allows the server process to be used by others. Ideal for legacy applications that cannot be updated to use explicit driver pools.

Oracle Globally Distributed Database (formerly Oracle Sharding) allows data to be distributed across a pool of databases.

Note: This feature is only supported in python-oracledb Thick mode.

To route a connection directly to a specific shard, use the shardingkey and (if using composite sharding) supershardingkey parameters in oracledb.connect() or ConnectionPool.acquire().

Key Details:

• Sharding Key: A required sequence of values used to route to a shard. Supported types: string (VARCHAR2), number (NUMBER), bytes (RAW), and date (DATE). TIMESTAMP is not supported.
• Super Sharding Key: Required when using composite sharding (partitioning by a range/list, then by a shard key).
• Connection Pooling: Use the max_sessions_per_shard attribute in oracledb.create_pool() to balance connections across shards.
• Coordinator Shard: To access data across multiple shards, connect to the coordinator shard catalog database without providing shard keys.

# Sharding by VARCHAR2
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              shardingkey=["SCOTT"])

# Sharding by NUMBER
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              shardingkey=[110])

# Sharding by DATE
import datetime
d = datetime.datetime(2014, 7, 3)
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              shardingkey=[d])

# Sharding by RAW
b = b'\x01\x04\x08'
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              shardingkey=[b])

# Multiple keys (Composite Sharding)
key_list = [70, "SCOTT", "gold", b'\x00\x01\x02']
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              shardingkey=key_list)

# Using a Super Sharding Key
connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb",
                              supershardingkey=["goldclass"],
                              shardingkey=["SCOTT"])