should we substract?
A reader, January 11, 2003 - 8:02 pm UTC
Hi
Should we substract the two values? I mean if I do some manual DML through sqlplus and then issue rollback isnt that a transactiont oo? In that case substract shouldnt be correct right?
January 12, 2003 - 9:35 am UTC
ADD -- user commits + user rollbacks = number of transactions performed.
some people might just use user commits as number of transactions performed.
Me, I would use statspack as it tells me:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
...
Sorts: 23.46 3.35
Logons: 0.43 0.06
Executes: 156.05 22.31
Transactions: 7.00
without me even having to think about it!
Does SCN have 1:1 relationship to the transaction?
Frank, January 11, 2003 - 10:00 pm UTC
Tom,
You ansewers were very helpful. But could you answer the question in the subject and another question about the dbms_flashback.get_system_change_number in my previous message?
Thanks!
January 12, 2003 - 9:41 am UTC
geez, come on -- no, it is not. You yourself said as much!!!!
...
My test shows there is not 1:1 relationship, below is the screen shot in single
user(myself)
......
but not for the reason you thing (SMON or job queues or AQ was doing work in the background). in a distributed system, you can see the scn fly up as the systems sync their clocks. But anyway.
since it didn't have anything to do with the ANSWER TO THE question -- the question needed not look at SCN -- looking at SCN is wrong, I just gave you the answer, you know, the information you actually need.
1) no
2) the above are ok, but not relevant to answering the question
3) answered
user commits + rollbacks
Shankar, January 12, 2003 - 12:25 am UTC
Rollbacks are also considered as a transaction. so you should sum up 'user commits' and 'user rollbacks' for that snap period.
transaction count = user commits + user rollbacks
"look at this table, come back in a couple of minutes -- look again and subtract the two observations. Divide and you have TPS."
he meant to subtract transaction counts between two snap periods.
Shankar
January 12, 2003 - 9:54 am UTC
yes -- thanks for the clarification -- that is what I mean
( ((user commits time 2)+(user rollbacks time 2)) -
((user commits time 1)+(user rollbacks time 1)) ) / elapsed_time_in_seconds
Why is it so
Brijesh, January 13, 2003 - 12:57 am UTC
demo@ORA92> select * from v$sysstat where name like '%user%';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
4 user commits 1 35
5 user rollbacks 1 3
6 user calls 1 2534
demo@ORA92> drop table t;
Table dropped.
demo@ORA92> create table t (x int);
Table created.
demo@ORA92> select * from v$sysstat where name like '%user%';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
4 user commits 1 36
5 user rollbacks 1 3
6 user calls 1 2541
demo@ORA92> commit;
Commit complete.
demo@ORA92> commit;
Commit complete.
demo@ORA92> select * from v$sysstat where name like '%user%';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
4 user commits 1 36
5 user rollbacks 1 3
6 user calls 1 2548
demo@ORA92> rollback;
Rollback complete.
demo@ORA92> select * from v$sysstat where name like '%user%';
STATISTIC# NAME CLASS VALUE
---------- -------------------- ---------- ----------
4 user commits 1 36
5 user rollbacks 1 4
6 user calls 1 2553
The behaviour of rollback is different than commit.
I mean commit is only increasing when I make some changes, where as rollback value is increasing whether I make some change or not.
What does 'user calls' indicate ?
Thanks
Transaction and SCN
Varda, May 03, 2003 - 3:34 am UTC
Hi Sir,
This was exactly what i was lookin for.
Thank a Lot
user commits + user rollbacks is not the number of transactions they are doing
Dakshinamurty, July 15, 2008 - 4:16 am UTC
In one application which I am reviewing for performance, application is doing 6 dummy rollbacks for each transaction it is doing. We load tested the application with 30 users, and AWR (database Oracle 10gr2) was taken. No performance affect of dummy rollbacks was observed. The application team was telling they want to catch an unhandled exception in a huge code, which will rarely occur. Then I told application team that:
1. It is not a good practice to depend on dummy rollbacks.
2. Even if you depend, 1 rollback per transaction is expected. Why 6 rollbacks are happening?
3. For each dummy rollback they are doing, Oracle tries to undo all changes made by all the SQL statements in the transaction by using the corresponding undo tablespace and then tries to release all the transaction's locks of data.
4. Dummy rollbacks are put in place for uncommitted transactions, for which error handling is not done. Then transactions have to wait for dummy rollback to happen. Then the locks will be held for longer time than needed. When more users are running, the affect can bring the application to hault.
For this the application team is telling that unless there is a major performance improvement, it is difficult for them to put effort.
Please explain any other performance affects of dummy rollbacks and please comment on the above 4 points.
July 15, 2008 - 9:44 am UTC
tell us what a dummy rollback is first - I don't understand the flow here.
What dummy rollback is?
Dakshinamurty, Y, July 23, 2008 - 3:37 am UTC
Sorry for the delay.
We will be using application connection pooling.
A user will be getting database session connection from this pool and releasing after completion of transaction. In some part of our code, we didn't handle errors and if error is there, the connection will be released without rolling back.
For this purpose, we are doing a rollback after every connection is got. We are not checking whether the session is used at all from the pool and if, what application team is telling is correct there should have been one rollback per transaction. But they are much more.
That's why I am calling them as Dummy Rollbacks, actually many of them might have been called without any purpose.
July 24, 2008 - 10:05 am UTC
... In some part of our code, we didn't handle
errors and if error is there, the connection will be released without rolling
back.
....
you have serious bugs in your code then, fix it.
they are not dummy rollbacks if there is actual work to do, if there is nothing to do, we do nothing, if there is something to do - we do stuff (and hence it would NOT be a dummy rollback - it would be real)