13 Tuning Transactions

This chapter provides background and tuning information for transaction optimization.

Logging Last Resource Transaction Optimization

The Logging Last Resource (LLR) transaction optimization through JDBC data sources safely reduces the overhead of two-phase transactions involving database inserts, updates, and deletes. Two phase transactions occur when two different resources participate in the same global transaction (global transactions are often referred to as "XA" or "JTA" transactions). Consider the following:

  • Typical two-phase transactions in JMS applications usually involve both a JMS server and a database server. The LLR option can as much as double performance compared to XA.

  • The safety of the JDBC LLR option contrasts with well known but less-safe XA optimizations such as "last-agent", "last-participant", and "emulate-two-phase-commit" that are available from other vendors as well as WebLogic.

  • JDBC LLR works by storing two-phase transaction records in a database table rather than in the transaction manager log (the TLOG).

See "Logging Last Resource Transaction Optimization" in Programming JTA for Oracle WebLogic Server.

LLR Tuning Guidelines

The following section provides tuning guidelines for LLR:

  • Oracle recommends that you read and understand "Logging Last Resource Transaction Optimization" in Programming JTA for Oracle WebLogic Server and "Programming Considerations and Limitations for LLR Data Sources" in Configuring and Managing JDBC Data Sources for Oracle WebLogic Server. LLR has a number of important administration and design implications.

  • JDBC LLR generally improves performance of two-phase transactions that involve SQL updates, deletes, or inserts.

  • LLR generally reduces the performance of two-phase transactions where all SQL operations are read-only (just selects).

  • JDBC LLR pools provide no performance benefit to WebLogic JDBC stores. WebLogic JDBC stores are fully transactional but do not use JTA (XA) transactions on their internal JDBC connections.

  • Consider using LLR instead of the less safe "last-agent" optimization for connectors, and the less safe "emulate-two-phase-commit" option for JDBC connection pools (formerly known as the "enable two-phase commit" option for pools that use non-XA drivers).

  • On Oracle databases, heavily used LLR tables may become fragmented over time, which can lead to unused extents. This is likely due to the highly transient nature of the LLR table's data. To help avoid the issue, set PCT_FREE to 5 and PCT_USED to 95 on the LLR table. Also periodically defragment using the ALTER TABLESPACE [tablespace-name] COALESCE command.

Read-only, One-Phase Commit Optimizations

When resource managers, such as the Oracle Database (including Oracle AQ and Oracle RAC), provide read-only optimizations, Oracle WebLogic can provide a read-only, one-phase commit optimization that provides a number of benefits – even when enabling multiple connections of the same XA transactions – such as eliminating XAResource.prepare network calls and transaction log writes, both in Oracle WebLogic and in the resource manager.

See "Read-only, One-Phase Commit Optimizations" in Programming JTA for Oracle WebLogic Server.