Re: tkprof elapsed time clarification

dbaoracleind_at_yahoo.com wrote:
> Hi
>
> I have a trace report created from tkprof that shows the following inf
> for a query run.
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.02 0.06 0 8 0
> 0
> Execute 1 0.01 0.02 0 0 0
> 0
> Fetch 9249 2.40 725.79 0 0 0
> 138720
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 9251 2.43 725.88 0 8 0
> 138720
>
>
> I am really interested in the elapsed time which shows as 725 seconds.
> I also had a select sysdate from dual before and after this query that
> showed the time taken for the query to complete as 12 secs instead of
> 725 secs.
>
> I am obviously missing something and would like to know where it is.
>
> Appreciate your help in advance,
>
> Thanks
>
> Sam

Quoting from "Optimizing Oracle Performance" by Cary Millsap pg 84: "Oracle's tkprof utility produces erroneous results in more cases than you might have imagined, especially in the STAT line processing. Oracle's tkprof has an exceptional reputation for reliability, but I'm convinced that one reason the tool maintains this reputation is that people simply never bother to double-check its output. To confirm or refute whether tkprof is giving correct output is impossible to do without studying raw trace data."

You found a problem - it may be a bug in Oracle, a bug in tkprof, a bug in the time reported by the operating system, a time synchronization utility that just happened to fire when the trace file was created, possibly using tkprof for Oracle 8 to analyze an Oracle 9 trace file, or something else. Consider taking a look at the raw trace file.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jul 13 2006 - 09:39:50 CDT