Re: SQL Traces' Comparison

I advise to look at the raw trace in detail. Since tkprof summarizes all identical sql into one entry there is no guarantee that all 17 executions in CASE 2 used the same plan. tkprof simply uses the first execution plan in the trace for the summarized entry. If subsequent executions use a different plan you wouldn't know from tkprof. I'm not saying that that is the case here, but it is a possibility with a greater than zero probability.

At 11:10 AM 8/14/2008, VIVEK_SHARMA wrote:
>Folks
>
>For the same SQL with the same execution Path on the same Database,
>what is the possible cause for High Elapsed Time ub Case 2 (below)?
>NOTE - Table sizes having grown Slightly (by a few GB) in Case 2
>
>Will provide any detail needed
>
>Cheers & Thanks
>
>
>P.S.
>
>CASE 1 Earlier
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ----------
>---------- ----------
>Parse 24 0.01 0.00 0 0 0
> 0
>Execute 36 0.05 0.03 0 0 0
> 0
>Fetch 55 0.03 0.02 0 2099 0
> 374
>------- ------ -------- ---------- ---------- ----------
>---------- ----------
>total 115 0.09 0.06 0 2099 0
> 374
>
>
>CASE 2 Later
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ----------
>---------- ----------
>Parse 15 0.00 0.00 0 0 0
> 0
>Execute 17 0.00 0.00 0 0 0
> 0
>Fetch 17 30.30 91.66 0 3207424 0
> 68
>------- ------ -------- ---------- ---------- ----------
>---------- ----------
>total 49 30.30 91.66 0 3207424 0
> 68
>
>**************** CAUTION - Disclaimer *****************
>This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
>for the use of the addressee(s). If you are not the intended
>recipient, please
>notify the sender by e-mail and delete the original message.
>Further, you are not
>to copy, disclose, or distribute this e-mail or its contents to any
>other person and
>any such actions are unlawful. This e-mail may contain viruses.
>Infosys has taken
>every reasonable precaution to minimize this risk, but is not liable
>for any damage
>you may sustain as a result of any virus in this e-mail. You should
>carry out your
>own virus checks before opening the e-mail or attachment. Infosys
>reserves the
>right to monitor and review the content of all messages sent to or
>from this e-mail
>address. Messages sent to or from this e-mail address may be stored on the
>Infosys e-mail system.
>***INFOSYS******** End of Disclaimer ********INFOSYS***

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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