Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Frank.

Asked: January 11, 2003 - 5:26 pm UTC

Last updated: July 24, 2008 - 10:05 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom
I need to gather the TPS(transactions per second)of my database.
I used two methods below to find the SCN
1) Prior to 9i
alter system checkpoint
select checkpoint_change# from v$database
2) Oracle 9i
select dbms_flashback.get_system_change_number from dual;
My questions are:

1) Does Transaction has 1:1 relationship to the SCN. If i want to calculate how many transactions happened in the sample period, can i use the difference of the SCN (i.e, transactions = SCN_END-SCN_BEGIN).
2) How can i find the SCN of the DB if my methods above is not good?
3) If there is no 1:1 relationship between SCN and transaction, how can i calculate the transaction number of my system?

My test shows there is not 1:1 relationship, below is the screen shot in single user(myself)

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
431623

SQL> update scott.dept set dname=dname;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
431626

There was only 1 transaction, but the SCN changed from 431623 to 431626.



and Tom said...

why wouldn't you just use v$sysstat?

1* select * from v$sysstat where name like 'user%'
ops$tkyte@ORA9I> /

STATISTIC# NAME CLASS VALUE
---------- ------------------------------ ---------- ----------
4 user commits 1 11436149
5 user rollbacks 1 27339


commits = successful transactions, rollbacks = failed.

look at this table, come back in a couple of minutes -- look again and subtract the two observations. Divide and you have TPS.


Statspack will help you keep a history of this -- just take a statspack.snap, come back and do it again in 15 minutes. Now, you can compute TPS from that (actually - it does it for you!) Nice thing is it'll keep it in tables for as long as you like.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Tom Kyte
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!


Tom Kyte
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

Tom Kyte
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

Tom Kyte
January 13, 2003 - 8:00 am UTC

so? point is? ignore it -- it is noise. If you commit with no changes database is just saying "oh, never mind". A rollback is a rollback is a rollback.

ignore user calls in the context of this discussion. All stats are described in the documentation:
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/apc2.htm#27558 <code>






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.

Tom Kyte
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.
Tom Kyte
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)

More to Explore

DBMS_FLASHBACK

More on PL/SQL routine DBMS_FLASHBACK here