sys.dbms_transaction.local_transaction_id(true

Stefan Dusik · Post by **Stefan Dusik** » Thu 05 May 2011 12:09

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.

AlexP · Post by **AlexP** » Thu 05 May 2011 13:34

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.

wraaflaub · Post by **wraaflaub** » Tue 14 Jun 2011 12:46

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

AlexP · Post by **AlexP** » Fri 17 Jun 2011 07:55

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.

jfudickar · Post by **jfudickar** » Fri 17 Jun 2011 09:58

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

jfudickar · Post by **jfudickar** » Thu 23 Jun 2011 13:09

Done

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

Stealth · Post by **Stealth** » Mon 04 Jul 2011 08:52

the main problem is in module OraTransaction:

Code: Select all

function TOraTransaction.DetectInTransaction(CanActivate: boolean = False): boolean;
…
    Result := FITransaction.DetectInTransaction(CanActivate);
…

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!

AlexP · Post by **AlexP** » Fri 08 Jul 2011 13:29

Hello,

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