In our project, we use the Oracle XA Connection pool. Only a small subset of the queries(transactions) are distributed. Rest are quite straightforward single database modification.
I would like to know if there is a performance difference in using XAConnections Vs the normal ones.
We use websphere v6.1 as the server.
If XAConn, is not very performant, then iam planning to have two datasources and use connections from them as appropriate.
Any pointers would be of great help.
I have no benchmarks to substantiate the following, this is just "we all know that" conventional wisdom. As with all performance discussions Your Milage Will Vary, if this is absolutely critical to your application then you need to perform your own benchmarks.
I believe that there is no signifincant performance overhead from using an XA-capable connection pool for non-XA work - WebSphere is careful to use 1PC transactions when only a single resource is used in a transaction. The primary overhead of XA is the extra Prepare/Commit/Forget set of XA messages, these will not occur in a simple 1PC case.
So the primary danger is of inadvertantly initiating a 2PC transaction. This can happen if you do several pieces of work supposedly against the same resoruce but with (for example) different isolation levels. You obtain a connection from the pool and do some work, that connection is now associated with your transaction until COMMIT. You "close" the connection, notionally returing it to the pool, but in fact WebSphere's pooling keeps the connection for your transaction. You ask for the connection again to do some more work, provided that you are asking for exactly the same connection you will be given the same connection again and so work continues in a single transaction - we have only 1PC, no overhead.
I would take the approach of initially haveing a single, XA-capable pool, but have two separate resoruce references, one for 2PC work, one for 1PC work. Have both references point to the same connection pool. Your code now is isolated from any need to have separate connection pools, it's just a matter of rebinding the resource-ref if you ever do need to make the change.