Calculation Formula of average transaction response time in Oracle Database

Calculation Formula of average transaction response time in Oracle Database

Calculation Formula of average transaction response time in Oracle Database

Note: This formula is taken from: eel <oracle optimization diary>, p316

Top 5 Timed Events

Event	Waits	Time (s)	Avg Wait (MS)	% Total Call Time	Wait Class
Direct path read	327,284	15,555	48	86.4	User I/O
CPU time		1,093		6.1
Db file sequential read	283,101	509	2	2.8	User I/O
Db file scattered read	99,520	180	2	1.0	User I/O
Enq: TX-row lock contention	274	133	485	. 7	Application

Wait Events

S-second

Cs-centisecond-100th of a second

MS-millisecond-1000th of a second

Us-microsecond-000000th of a second

Ordered by wait time desc, waits desc (idle events last)

Event	Waits	% Time-outs	Total Wait Time (s)	Avg wait (MS)	Waits/txn
Direct path read	327,284	0.00	15,555	48	32.66
Db file sequential read	283,101	0.00	509	2	28.25
Db file scattered read	99,520	0.00	180	2	9.93
Enq: TX-row lock contention	274	98.91	133	485	0.03
Log file sync	6,791	0.00	93	14	0.68
Control file sequential read	16,168	0.00	91	6	1.61
Log file parallel write	7,816	0.00	82	10	0.78
Name-service call wait	1,199	0.42	68	57	0.12

Take the "direct path read" Wait event as an example to calculate the Total Wait Time (in ms) of A --- "direct path read) for the 15555*1000 = 15555000msB --- "direct path read" Waits is 327284C --- "direct path read" and the Waits/txn is 32.66D --- "direct path read", the % Total Call Time is 86.4% = 0.864 average transaction response time = A/B * C/D = 15555000/327284*32.66/0.864 = 1796.584626 MS