February 15, 2013

Transactions

It’s very easy to get a lot of information from an AWR (or Statspack) report – provided you remember what all the numbers represent. From time to time I find that someone asks me a question about some statistic and my mind goes completely blank about the exact interpretation; but fortunately it’s always possible to cross check because so many of the statistics are cross-linked. Here’s an example of a brief mental block I ran into a few days ago – I thought I knew the answer, but realised that I wasn’t 100% sure that my memory was correct:

In this Load Profile (for an AWR report of 60.25 minutes), what does that Transactions figure actually represent ?

And, while we’re at it, what does the “Rollback per transaction %” actually mean – and what, if anything, can we infer from the value ?

Since we’re looking at transactions, it’s a fairly safe bet that the figure is something to do with commits – but is it counting only committed transactions, or does it included rolled back transactions [philosophical question – is a change that’s never committed really a transaction, since it “never happened” as far as the rest of the world is concerned?]. Fortunately we can look at the Intance Activity Statistics to check.

First, though, lets convert the “Per Second” figures into an absolute value by multiplying by the duration in seconds (3,615) of the report. Allowing for rounding errors we’re looking for a value between ceiling(4.785 *3615) and floor(4.795 * 3615), i.e. between 17,298 and 17,338. Let’s also keep in mind that the “Rollback per transaction %” is very close to 25%.

Here, then, are a couple of useful figures from the Instance Activity:

Almost immediately you can see that user rollbacks are roughly one third of user commits (I promise I didn’t massage these number), which means the rollback percentage is just (user rollbacks / (user commits + user rollbacks)). As a further sanity check, 4,316 + 12,986 = 17,302 which falls nicely into our required range. Ta-da, job done: “Transactions” is the sum of user commits and user rollbacks (as a first approximation – but is it the whole answer).

Of course, I picked a fairly extreme example from my AWR library to make a point – which means you might now be asking whether all those rollbacks pose some sort of threat. How serious are they ? Luckily there are a couple more statistics that tell us:

The name of the first statistic has been trimmed a little in this text report, the full name is: “rolllback changes – undo records applied”. As you can see, we’ve only applied 222 undo records in our 4,316 rollback calls, so we’re not really rolling anything back (most of the time). Moreoever, the “transaction rollbacks” corroborates this observation – we have only attempted to rollback 59 “real” (data-changing) transactions. Most of those rollbacks are probably the default “rollback after every call” that some web application servers make.

As a closing thought – if you’re responsible for several different systems, it’s convenient to keep a couple of “reference” AWR or Statspack reports from busy, normal, and quiet periods for each system; that way, if you use the figures from one system to work out the meaning of some derived value you can use the figures from another system to check if your hypothesis is correct.

Footnote: there are two other statistics that include the text “undo records applied” (not that you’ll see those words in the textual AWR report), but they both relate to creating read-consistent copies of blocks.

Footnote 2: Is anyone getting worried by my “first approximation” comment – and has anyone started wondering if there may be more to transaction rollbacks that user rollbacks, and if so where they fit into the arithmetic ? To be continued.

Jonathan, I meant that just one cursor loop may cause a lot of recursive calls as every successful fetch call is counted as recursive. This is clear to see in case of plsql_optimize_level < 2 or select for update cursor loop where is no array optimization or for manual fetches of course.

Thanks for the demonstration. I’ve often wished that Oracle would introduce a “fetch count” statistic to match the parse count and execute count. (Splitting recursive calls into sys-recursive and user-recursive would be nice as well).

Great, isn’t it.
You can spend an awful lot of your time deliberately rolling back – but not quite far enough – and the “obvious” statistics tells you nothing about it. As Valentin points out, you have to cross-check with “rollback changes – undo records applied” to get an idea of how much work you are doing while rolling back. (And, of course, the rolling back from “write consistency” – as well as various other things – shows up in that statistic as well.)

Jonathan
If you issue a superflous COMMIT (commit that changes nothing ) the user commits statistics is not incremented. If you issue a superflous ROLLBACK (rollback that rolls nothing back as those probably done by web application servers ) the user rollback statistics is incremented while the rollback changes – undo records applied statistics aren’t incremented. If you issue a real rollback (rollback that rolls something back) both the user rollback statistics and the undo records applied are incremented. If you issue a rollback to savepoint (whatever it is superflous or real rollback savepoints) the user rollback statistics is not incremented while the statistics undo records applied is incremented for a real rollback to savepoint and it is not incremented for a superflous rollback to savepoint.

That is : for commits, we can trust the statistics user_commits but for rollbacks we had better to trust rollback changes – undo records applied

I can’t quote names and versions off the top of my head, but various middle-tier tools handling connection pooling will (or used to) “isolate” the work done by each request by issuing a rollback extremely frequently.

I am glad you touched on this topic since I was looking at my AWR last week and wondering what is my peak load in my system….so my question to you is …..can I use these stats ” Transactions per sec.” to determined when the peak load is on my production system?