Re: same sql, same execution plan, but the time to complete is very different when run in different time.

  • From: "Yongping Yao" <yaoyongping@xxxxxxxxx>
  • To: staywithpin@xxxxxxxxx
  • Date: Sat, 10 Nov 2007 23:44:58 +0800

Wu,
  Is there some other SQL or job running when you got your 'Bad
running'?Or the hardware, esp. disks were busy with backup?

On Nov 10, 2007 3:12 PM, qihua wu <staywithpin@xxxxxxxxx> wrote:
> Hi, we have a batch job(run throught SQLPLUS) which runs every day. For the
> same sql with the same execution plan, but the time spent on CPU is largely
> different. One is 781 seconds and another one is 2193 seconds. As buffer
> gets eat CPU, so I also compared the number of buffer gets and found the
> number of buffer gets are very close. SQL parsing also eats CPU, but the sql
> run millions of times inside a procedure, so it should be only parsed once,
> and one time of parse shouldn't use much CPU. What else can eat so much CPU?
>
>
>
>
> Elapsed Time (s)
>
> CPU Time (s)
>
> Executions
>
> Buffer Gets
>
> SQL Text
>
>
> Good running
>
> 829
>
> 781
>
> 1,587,382
>
> 96,640,879
>
> UPDATE PM_ELIGIBILITY_TEMP ELI...
>
>
> Bad running
>
> 2,978
>
> 2,193
>
> 1,587,382
>
> 96,642,211
>
> UPDATE PM_ELIGIBILITY_TEMP ELI...
>
>   .
>
>



-- 
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc.
Blog:  http://spaces.msn.com/yaoyp/
--
http://www.freelists.org/webpage/oracle-l


Other related posts: