Formula for calculating the average transaction response time in the Oracle database, and the oracle Response Time

Formula for calculating the average transaction response time in the Oracle database, and the oracle Response Time

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