The following trace is from a query that is a cursor. Inside the query there are about 10-15 other sql operations (I didn't write this and I do not have approval to change this. The waits don't add up to the total response time.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.04 0.52 0 0 0 0
Fetch 2 17.08 119.26 59584 84489 0 145
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 17.13 119.79 59584 84489 0 145
the waits
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 7111 0.11 27.56
db file sequential read 1987 0.54 3.59
********************************************************************************
Elapsed = 119
CPU = 17
Elapsed-CPU = physical Reads= 102 seconds
However, my waits below add up to 31 seconds. When I look at the rest of the queries inside the cursor, they add up to more than the 120 seconds for this cursor. So I don't think this is a sum total of all the work going on inside the cursor.
I am trying to interpret this. I am not allowed to change the code. So I just need some help trying to figure out why my waits do not add up to the difference between CPU and elapsed time? Where is the mystery waiting?