How to calculate Average Response Time Per TXN over a sample period

[uday]

Using only statistics available within the database, this calculation gives the best approximation of response time, in seconds, per transaction during a sample period. This is useful in further diagnosing performance issues by drilling down into individual components like Waits, CPU etc.


ResponseTime = (deltaTotalWait + deltaCpuTime) / deltaCommits + deltaRollbacks)

where:

deltaTotalWait: difference of \'sum of time waited for all wait events in v$system_event\' between sample end and start

deltaCpuTime: difference of \'select value from v$sysstat where name=\'CPU used by this session\'\' between sample end and start

deltaCommits: difference of \'select value from v$sysstat where name=\'user commits\'\' between sample end and start

deltaRollbacks: difference of \'select value from v$sysstat where name=\'user rollbacks\'\' between sample end and start


[Edited by uday on 04-03-2001 at 02:37 PM]

[apfaff]

I've looked at your article and wrote a pl/sql process to track the response time for metrics. We have a very badly written application that we are supporting.

Question: How do you handle the negative results? Am I looking at this correctly?

Thanks for your help,
apfaff

[salernov]

Can you explain your rational behind this?

Do you have a script already written that grabs the
start & stop time & calculates this? Like UTLB & ESTAT?

[mithalr]

Hi guys,
I know this is an old thread but what I require is related to this.
I have used the following script to get transaction speed on my DB based on the formular supplied by UDAY but the results are unclear. Can you tell me if this is really the number of seconds used per transaction or is my script incorrect:-

select ((WAIT + val) / TRXN_CNT) as TRXN_SPEED
from (select sum(TOTAL_WAITS) as WAIT from v$system_event) ,
(select value as VAL from v$sysstat where name='CPU used by this session
'),
(select sum(value) as TRXN_CNT from v$sysstat where name in ('user rollb
acks','user commits'))
/ SQL> /

TRXN_SPEED
----------
51914.4751

SQL>