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