Re: "Elapsed time" from statspack/sql_trace_tkprof_file different;

No, you cannot compare them. You do not know the composition of the averages. What is the distribution of the values? Example - A sql statement is run. Compare the "sql trace" and "statspack" results. 3 of the execs in the Statspack are the ones in the SQL trace.

SQL trace - 3 executions, 1 second average (3 execs of 1 second each) (3 * 1) = 3 / 3 = 1 second average
Statspack - 30 executions, 3 second average (29 execs of 1 second each, 1 exec of 61 seconds for a total of 90 seconds) (29 * 1) + (1 * 61) = 90 / 30 = 3 second average

Or

SQL trace - 3 executions, 21 second average (2 execs of 1 second each, 1 exec of 61 seconds). (2 * 1) + (1 * 61) = 63 / 3 = 21 second average Statspack - 30 executions, 3 second average (29 execs of 1 second each, 1 exec of 61 seconds for a total of 90 seconds) (29 * 1) + (1 * 61) = 90 / 30 = 3 second average

Granted, this is an extreme case (well, not all that extreme considering some performance I have seen), but it illustrates that you cannot absolutely compare a subset with a whole.

-- 
Daniel Fink

Oracle Performance, Diagnosis and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com


Zhu,Chao wrote:


> I am comparing per Execution cost/elapsed time.

> Total elapsed time / total executions = per execution cost. they are comparable.

>

> On 8/22/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:

>   

>> Assuming your stats below are for the same query, your tkprof is only

>> accounting for 32945 executions while the statspack is accounting for

>> almost 1 million executions so you're not comparing apples to apples.

>>

>> Regards,

>> Brandon

>>

>>     


--
http://www.freelists.org/webpage/oracle-l