Welcome, Guest
Username: Password: Remember me

TOPIC: Synthetic rollbacks/commits

Synthetic rollbacks/commits 5 years 2 months ago #95

Since I just found out that Carey and Karen both have moved to Method R, I will repost my message from the Hotsos blog. After taking a class with both Karen and Carey, I know where to go to get the answers. :cheer:

Let me first state that last year the Profiler helped me identify an Oracle bug (Metalink DocId #7715044) relating to ODP.net--so I love this utility.

I am no facing a situation where I can't for the life of me figure out why my trace is showing so many "synthetic rollbacks/commits" and am wondering if there might be some other bug. I have scoped the transaction properly and out of 39 seconds, over 16 seconds is attributed to SQL*Net message from client wait, and when I drill down into the contributors, all I see are synthetic rollbacks and commits.

Does anyone know of any bugs relating to IIS, ODP, Oracle 10g, etc.. that might cause this amount of overhead?
The administrator has disabled public write access.

Re:Synthetic rollbacks/commits 5 years 2 months ago #96

Hi, Chad.

We are grateful to be of help. Thanks for letting us know it has helped. We hope to help you further, at least, with this explanation.

We construct synthetic statements in the Profiler output to hold response time for commits or rollbacks. These transaction ending calls may be explicit or they may be implicit. When you see these so-called statements have lots of SQL*Net message from client (SNMFC) time attributed to them, you can be assured of the fact that just prior to the commit or rollback there was time spent outside of Oracle: (1) time spent executing application program code, (2) true user idle time, (3) time spent waiting on a third party response, and (4) possible other explanations.

Based on my observations over the years, my best guess is that it is #1 or #2. So, what I would say is that the application is working hard processing the data that it received just prior to the SNMFC.

BTW, Carey, is a fairly common misspelling of Cary's name. I don't think he would ever correct anyone himself and that's just one of the many reasons why we love him.
The administrator has disabled public write access.

Re:Synthetic rollbacks/commits 5 years 2 months ago #97

I'll add a bit under #4 Jeff lists as "possible other explanations".

Given your use of IIS, ODP, etc., you may be experiencing implicit commits from one of the drivers associated with your Oracle connection. Sometimes there is an "auto commit" feature associated with certain drivers that causes a commit to be executed after each SQL statement. You may want to check out that possibility as well.
The administrator has disabled public write access.

Re:Synthetic rollbacks/commits 5 years 2 months ago #98

Thank you very much Jeff/Karen for your comments, and my apologies for misspelling Cary's name.

I agree that it is certainly possible that some type of driver could be causing this issue. However, I have not been able to find any hits on "auto rollbacks", which it seems to be doing also. We have a very basic Windows 2003 server running IIS and it's configured with the Oracle client--including ODP.net. Going by the Profile output, for my 45 second scoped transaction there were 3,562 commits and 996 rollbacks. When I say "transaction", it's actually the process of just logging into the application through the front end--which I made sure to scope properly. In fact, I also made sure there was no one else using the database and nothing else was running.

The following were the two top consumers of execution time.

SQL*Net message from client 16.247
SQL*Net message from client [think time] 15.942

Almost all of the 16.247 seconds is attributed to 1) synthetic rollback, 2) synthetic commit. I too have read many hits on Google regarding the auto commit, but if I look at the raw trace file, I see "XCTEND rlbk=0, rd_only=1" and "XCTEND rlbk=1, rd_only=1" all throughout it, and not just after a select. By the way, what is a rollback on a read only transaction? :)

Again, thank you for any feedback on this issue. Right now with our clients, this 1 to 2 minute application signon process is killing them.

The administrator has disabled public write access.

Re:Synthetic rollbacks/commits 5 years 2 months ago #99

As I understand it, ODP does execute an implicit commit - even for a select. Have you tried enclosing your code with begin/end statements? I believe you have to control the transaction boundary yourself in order to keep ODP from looking at each statement individually and doing the commit/rollback deal.

I'm not sure how it differentiates on when to commit and when to rollback, or if it does at all, but I think you should investigate coding transaction boundaries and see how that goes.
The administrator has disabled public write access.

Re:Synthetic rollbacks/commits 5 years 2 months ago #100

It's hard to believe that ending transactions is the culprit.

If the signon really took 45s (your second post) and all of your trace file is part of that 45s, then at least 32.1s (16.2s+15.9s) of the 45s is spent executing client code that doesn't interact with the RDBMS. That's about 71% of the response time. Knowing which part of the form is responsible might require application instrumentation.

But then again, if my assumptions are accurate, we know that the latest the form contacted Oracle is 12.9s (45s-32.1s) into the signon attempt. With more knowledge of the form and correlation of its steps to the trace data, you can figure out why it's taking so long.

If the 32.1s and the 45s don't go together in the context I describe, then we need to start over.

Now, for me to get more perspective I have to ask this: how many times does an average person sign onto the application each shift?
The administrator has disabled public write access.