Devart Forums

Hello,

our customer found that ODAC generates this SQL

begin :result := sys.dbms_transaction.local_transaction_id(true); end;

4 times during execution of another SQL statement. From another topic of this forum we know that it is during the active transaction. But this topic was 5 years old. Is there any progress in it? The main problem is that we want to speed up our DB and these repeatedly generated SQL statements slows down the whole process.

Thanks.

Hello,

This PL/SQL block is used to start a new transaction or to get the ID of already running transaction. Maybe when executing your code ODAC need to get the current transaction ID, and the PL/SQL block called several times.

Please send a complete small sample to alexp*devart*com to demonstrate the problem.

Hello,
We're also very much interested in this question.
During an active transaction, ODAC issues this statement at least once for every SELECT statement. (It isn't visible to TOraSQLMonitor because it is done via TOCICommand.Execute, in TOCITransaction.LocalTransactionId.) This doubles the number of queries executed and causes performance problems for us. Is there a way to avoid it?

We start and end transactions always via ODAC. We think the transaction ID cannot change inbetween, so it doesn't have to be fetched more than once.

Maybe it is possible to add an option to cache the transaction ID locally?

Kind regards,
Walter

Hello,

We cannot save and use ID transactions locally, because current transaction cannot be closed by Commit or Rollback ODAC methods, but in a procedure, function, etc. That's why you need to check the current actual transaction ID.

What about adding a locking mechanism on session level to stop doing this?

Something like "lock_local_transaction_id" and "unlock_local_transaction_id".

When I do not need the transaction state I could disable it and enable it later on.

And please not only a session property.

Or should I create a suggestion at uservoice?

Regards
Jens

Done

http://devart.uservoice.com/forums/1046 ... nlock-loca

the main problem is in module OraTransaction: after each statement this code makes additional round-trip to the Oracle server. it is really terrible additional unprepared anonymous pl/sql block, that must be parsed each time and works noticeably slower than user code.

why sys.dbms_transaction.local_transaction_id?
why you are not using OCI (attributes of the service context) to handle transactions?

by commenting this code you can make you application more than two times faster!

Hello,

We have fixed this problem; now the transaction ID is obtained only when the StartTransaction method is called.