Re: Oracle trace with timed stat

In general, timed_statistics for very short SQL statement like this can be very misleading because the timer granularity is just 1/100 second.

In the example, tkprof is claiming that it totalled just 5.49 CPU seconds to execute your statement 52,253 times. This is a little unlikely (though not completely impossible).

The trouble is that the statement probably took (say) 1/1000 sec for a total of 52 seconds. But possibly ninety-nine times out of 100 it finished inside the same tick, and on one occasion in 100 it started in one tick and finished in the next - thus recording 1/100 sec on 500 occasions for an apparent total of 5 seconds.

Summary:
In theory the time is the total time for all executions. In practise, for very short statements the time may have a considerable margin of error.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



kerbiquet_at_hotmail.com wrote in message <91ll10$2ne$1_at_nnrp1.deja.com>...


>Oracle 8.0.6 HP UX 11

>

>I got the following trace, and not 100% sure how to understand time

>statictics. Does the trace meen that the last execute call took 3.58 s

>cpu or is it the total time of the 52253 execute calls that took that

>time?

>

>

>SELECT TIME_ID

>FROM

>"TIME" WHERE TIMEDATE = TO_DATE(:b1,'RRRRMMDD') + 1

>

>

>call count cpu elapsed disk query current rows

>------- ------ -------- ---------- ---------- ---------- ---------- ----

>------

>Parse 1 0.00 0.00 0 0 0 0

>Execute 52253 3.58 3.03 0 0 0 0

>Fetch 52253 1.91 1.15 3 156759 0 52253

>------- ------ -------- ---------- ---------- ---------- ---------- ----

>------

>total 104507 5.49 4.18 3 156759 0 52253

>

>

>

>Sent via Deja.com

>http://www.deja.com/

>