What causes the difference between CPU time and Elapsed time in Oracle tkprof files

Question

When analysing Oracle tkprof trace files I have noticed that there is sometimes a big difference between the cpu time and the elapsed time, and I don't know what causes it.

For example:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      42.09          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch       45     14.44      62.71      48664     505513          0        1871
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       47     14.45     104.82      48664     505513          0        1871

The wait statistics are like this:

 Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      46        0.00          0.00
  SQL*Net message from client                    46        0.19          1.68
  buffer busy waits                             559        0.23          8.59
  db file scattered read                       5204        0.21          7.49
  db file sequential read                      4240        0.20         13.49
  latch free                                    215        0.11          3.62

I'm a software developer (not a DBA) so I'm generally looking these trace files to find inefficient queries or to see if an index could be used to stop a full table scan and so on. For this purpose I tend to go on the cpu time. In most cases the elapsed time is very similar to the cpu time.

I don't have access to the database that generated the trace file (it's from a client site) but I would like to understand what is going on so that I can make suggestions as to how to reduce the elapsed time.

Answer 1

The CPU time is the time your query actually needed; the rest is waiting for resources. On a busy server this could be largely due to waiting for CPU currently in use by other users; this doesn't show up in the wait stats.