Re: Why is time_waited more than total elapsed time ?

On 20 Jun 2002 10:32:15 -0700, bchorng_at_yahoo.com (Bass Chorng) wrote:

>I am wondering why my time_waited on 'db file sequential read' is MUCH more
>than my total elapsed time in tkprof.
>
>I ran a long query in a controlled test enrivornment ( no other session running)
>and the query was executed right after a fresh bounce
>
>Here is the tkprof result of it:
>
>call count cpu elapsed disk query current rows
>------- ------ -------- ---------- ---------- ---------- ---------- ----------
>Parse 1 0.04 0.09 1 18 0 0
>Execute 1 0.00 0.00 0 0 0 0
>Fetch 5807 21.94 296.24 76109 376395 37 87091
>------- ------ -------- ---------- ---------- ---------- ---------- ----------
>total 5809 21.98 296.33 76110 376413 37 87091
>
>
>Note here that my elased time is 296.33 seconds.
>
>At the same time, I also trapped the difference of v$system_event
>before and after the run, and here is the result:
>
>TIMESTAMP EVENT TOTAL_WAITS TIME_WAITED
>------------------- ----------------------------------- ----------- -----------
>2002/06/20 10:02:44 db file sequential read 80962 41924
>2002/06/20 10:02:44 db file scattered read 291 1264
>
>Note here that my sequential read is 419.24 seconds.
>
>There are nothing else running and this is only a single session query.
>My parallel_min_servers is set to 0 and there is no hint in this query
>to tell Oracle to use parallel query.
>
>I thought the elased time (roughtly) = CPU time + event wait time.
>But this completely confuses me. Can anyone please explain this ?
>
>Thanks in advance.
>
>-bass
>
>BTW, the reason I trap the diff before and after on v$system_event, instead of
>using v$session_event, is the latter showed all zero, so the data is useless -
>maybe another question for those who know why.

No other session running: how about the background processes? Do you think they do nothing? Can't they be occupied coalescing your free space? If you look at the number of disk reads incurred you have to explain for some 9000 reads. So you are comparing apples and pears. Your assertion about v$session_event can't be true. Are you sure you checked the correct session?

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jun 20 2002 - 12:58:18 CDT