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:
FolksFor 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 Earliercall 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 374CASE 2 Latercall 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 solelyfor 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-mailaddress. 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
