Performance Tuning

Performance tuning is critical for ensuring the efficiency, reliability, and responsiveness of TxEventQ event processing. As systems scale and the volume of events increases, poor optimizations can lead to latency, bottlenecks, or even system failures. By applying the techniques described in this section, TxEventQ administrators can significantly enhance throughput, reduce latency, and improve the stability of event driven applications.

• Event Streams and Multiple Consumers
  • Creating a Partitioned Kafka Topic
  • Message Cache Optimization
• Tuning System Parameters
  • DB_BLOCK_SIZE
  • JAVA_POOL_SIZE
  • OPEN_CURSORS
  • PGA_AGGREGATE_TARGET
  • PROCESSES
  • SESSIONS
  • SGA_TARGET
  • STREAMS_POOL_SIZE

Event Streams and Multiple Consumers

Oracle Database automatically manages the database table partitions of a queue. As queue volume fluctuates, table partitions may be dynamically created as necessary, for example, when the queue table expands due to a message backlog. Once all messages are dequeued and no longer needed, the database table partition is truncated and made available for reuse.

Queue event streams can be configured on a queue during creation. A higher number of event streams per queue improves dequeue performance through consumer parallelization, but requires additional memory and database resources. When messages are enqueued, each message is routed to a specific event stream of the queue. Messages within an event stream maintain a strict, session-level ordering.

If you wish to manually set the number of event streams for a queue, you may do so using the SET_QUEUE_PARAMETER procedure. The following SQL script creates a queue, and configures it to have 5 event streams and key based enqueue. Key based enqueue allows message routing to a specific event stream within a queue by providing a message key at enqueue time.

begin
    dbms_aqadm.create_transactional_event_queue(
            queue_name         => 'my_queue',
            queue_payload_type => DBMS_AQADM.JMS_TYPE,
            multiple_consumers => true
    );

    -- Set the queue to have 5 event streams
    dbms_aqadm.set_queue_parameter('my_queue', 'SHARD_NUM', 5);
    -- The event stream for enqueued messages is determined by the message key
    dbms_aqadm.set_queue_parameter('my_queue', 'KEY_BASED_ENQUEUE', 1);

    -- Start the queue
    dbms_aqadm.start_queue(
            queue_name         => 'my_queue'
    );
end;
/

It is recommended to avoid creating global indexes on the partitioned table that backs a queue. Local indexes are generally not recommended either and may result in performance degradation.

Creating a Partitioned Kafka Topic

The following Java code snippet creates a TxEventQ topic with 5 Kafka partitions (5 TxEventQ event streams) using the Kafka Java Client for Oracle Database Transactional Event Queues. When working with TxEventQ, we’ll refer to Kafka topic partitions as event streams. The my_topic topic may have up to 5 distinct consumer threads per consumer group, increasing parallelization.

Properties props = // Oracle Database connection properties
NewTopic topic = new NewTopic("my_topic", 5, (short) 1);
try (Admin admin = AdminClient.create(props)) {
    admin.createTopics(Collections.singletonList(topic))
        .all()
        .get();
} catch (ExecutionException | InterruptedException e) {
    // Handle topic creation exception
}

Note that you can use as many producers per topic as required, though it is recommended to ensure consumers are capable of consuming messages at least as fast as they are produced to avoid creating a message backlog and decreasing message throughput. For additional resources and examples related to developing with Kafka APIs for TxEventQ, see the Kafka chapter.

Message Cache Optimization

TxEventQ includes a specialized message cache that allows administrators to balance System Global Area (SGA) usage and queue performance. Management of TxEventQ’s SGA usage benefits throughput, reduces latency, and allows greater concurrency. When paired with event streams, message caching reduces the need for certain queries, DML operations, and indexes. The cache is most effective when consumers can keep up with producers, and when it is large enough to store the messages (including payloads) for all consumers and producers for using TxEventQ.

The message cache uses the Oracle Database Streams pool. You can fine-tune the memory allocation for the Streams pool using the DBMS_AQADM SET_MIN_STREAMS_POOL and SET_MAX_STREAMS_POOL procedures.

Tuning System Parameters

System parameters can be modified as appropriate for your database installation and event volume to improve TxEventQ performance. This section describes key parameters for optimizing TxEventQ and provides a refresher on working with system parameters.

To update a system parameter, use the ALTER SYSTEM command. The following statement sets the SGA_TARGET parameter to 2 GB on the running instance and the parameter file.

alter system set sga_target = 2G scope = both;

The scope of a parameter may be MEMORY, SPFILE, or BOTH:

• MEMORY: changes are applied to the running instance without updating the parameter file.
• SPFILE: changes are applied to the parameter file and are available on the next restart.
• BOTH: changes are applied dynamically to the running instance, updating both memory and the parameter file.

The following query retrieves the current value of the SGA_TARGET parameter:

select name, value, isdefault, issys_modifiable, ismodified
from   v$parameter
where  name = 'sga_target';

DB_BLOCK_SIZE

The default block size of 8k is recommended. The block size of a database cannot be changed after database creation.

OPEN_CURSORS

The OPEN_CURSORS parameter defines the maximum number of open cursors a session can have in Oracle. The default value is 50, and it can be adjusted between 0 and 65535. If you are running a large amount of producers and consumers, it’s essential to set this value high enough to avoid running out of cursors. You can modify OPEN_CURSORS using the ALTER SYSTEM command.

PGA_AGGREGATE_TARGET

The PGA_AGGREGATE_TARGET parameter specifies the target total memory available for the Program Global Area (PGA), which is used by server processes for tasks like sorting and joining. The default is 10 MB or 20% of the SGA size, whichever is greater. It is modifiable and helps optimize SQL operations by adjusting memory for working areas. When set to 0, it changes memory management to manual.

To set a hard limit for aggregate PGA memory, use the PGA_AGGREGATE_LIMIT parameter.

PROCESSES

The PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to the database. It is essential for managing background processes, such as locks, job queues, and parallel execution. Workloads with a large number of producers and consumers may require adjustments to this parameter. Adjusting this value may require reevaluating related parameters like SESSIONS and TRANSACTIONS.

SESSIONS

The SESSIONS parameter defines the maximum number of sessions that can be created in an Oracle database. It is calculated as (1.5 * PROCESSES) + 22 by default and can be modified within a range of 1 to 65,536. It impacts the number of concurrent users and background processes. For Pluggable Databases (PDBs), the SESSIONS parameter is adjustable but cannot exceed the root container’s value. You should adjust the number based on your expected connections and background processes, including queue workloads.

SGA_TARGET

The SGA_TARGET parameter specifies the total size of the System Global Area (SGA) in Oracle, allowing automatic memory management of its components like the buffer cache, shared pool, and others. Its value can range from 64 MB to SGA_MAX_SIZE. It is modifiable via ALTER SYSTEM. If set to zero, SGA autotuning is disabled. Systems that make heavy using of message queuing should configure SGA_TARGET to an appropriately large value to get the most out of queue message caching.

If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE)
• Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)
• Data transfer cache (DATA_TRANSFER_CACHE_SIZE)

STREAMS_POOL_SIZE

The STREAMS_POOL_SIZE parameter defines the size of the Streams pool, a shared memory area used for TxEventQ and other database features. If SGA_TARGET and STREAMS_POOL_SIZE are both nonzero, Oracle Database Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If both the STREAMS_POOL_SIZE and the SGA_TARGET parameters are set to 0, then the first request for Streams pool memory will transfer 10% of the buffer cache shared pool to the Streams pool.