XA performance

[Former Member]

I tried running simple test -

Simple EJB method is performing single XA DataSource Access.

On NetWeaver it performs about 5-9 times slower comparing to equal test using single Non-XA DataSource Access.

But exactly the same 2 tests on Weblogic have almost the same perfomance.

Are there any ways to optimize the situation when using single XA resource in transaction, to avoid two-phase commit?

This is my test: 2 EJB methods with Required transaction attribute, each of them is called 1000 times.

public void runXAOnly() throws RemoteException, NamingException, SQLException {

doStatement("java:comp/env/jdbc/jts/testXA", "insert into test values ('text', 10)");

doStatement("java:comp/env/jdbc/jts/testXA", "select * from test");

}

public void runNonXAOnly() throws RemoteException, NamingException, SQLException {

doStatement("java:comp/env/jdbc/jts/testNonXA", "insert into test values ('text', 10)");

doStatement("java:comp/env/jdbc/jts/testNonXA", "select * from test");

}

private void doStatement(String poolPath, String statement) throws NamingException, SQLException {

String stmtVal = statement;

InitialContext ic = new InitialContext();

DataSource _dataSource = (DataSource) ic.lookup(poolPath);

java.sql.Connection conn = _dataSource.getConnection();

PreparedStatement stmt = null;

try {

stmt = conn.prepareStatement(stmtVal);

stmt.execute();

} finally {

if(stmt != null) stmt.close();

conn.close();

}

}

[Former Member]

I understood the reason under my timings.

In fact I used non-XA pool with WebLogic with "Honor global transactions" flag. Each XA transaction is allowed to use one such non-XA DataSource.

The NetWeaver analogue of that DataSource seems to be oracle.jdbc.pool.OracleConnectionPoolDataSource, and not oracle.jdbc.xa.client.OracleXADataSource. With ConnectionPoolDataSource my transaction executes 20 msecs. Probably this type of DataSource should be preferred and used where possible instread of XA or JDBC 1.x (no XA support) DataSources.

[Former Member]

The bug seems to be in Oracle 9.2 thin JDBC driver.

I replaced the driver with Oracle 10.1 thin and the exception in multithreaded situation disappeared.

The times also changed a bit:

22 msecs on NetWeaver with non-XA pool, thin Oracle 10.1 driver

38 msecs on NetWeaver with XA pool, thin Oracle 10.1 driver.

Yet the question why XA is slower remains.

[Former Member]

What is even worse, with XA data source I constanly receive

Caused by: javax.ejb.EJBException: nested exception is: java.sql.SQLException: ORA-01591: lock held by in-doubt distributed transaction 6.13.307382

when trying to update the same table row from 2 Threads 200 times. Each update is a separate transaction.

I could understand performance degradation in that scenario, but the exception is definitely a BUG somewhere (in NW, Oracle thin driver or Oracle server).

[Former Member]

In my setup with NetWeaver or WebLogic on localhost and Oracle DB on another machine on LAN, the following code

void doWithDS(String jndi) throws Exception{
          DataSource ds = (DataSource)new InitialContext().lookup(jndi);
          Connection c = ds.getConnection();
          try{
              Statement st = c.createStatement();
              st.executeUpdate("update site set description='"+new java.util.Date()
                +"' where site='ADE2'");
              st.close();
          }finally{
              c.close();
          }
}

executes with <trans-attribute>RequiresNew</trans-attribute>:

10-20 msecs on WebLogic with XA pool, thin Oracle driver

10-20 msecs on NetWeaver with non-XA pool, thin Oracle driver

40-50 msecs on NetWeaver with XA pool, thin Oracle driver.

So the slowdown with trivial XA on NetWeaver is 2-3 times.

Anyone tried to optimize it?