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

hi:

Many reasons can cause the difference.

For example:

storage performance, network performance, physical reads, enqueues ....

You can do a 10046 trace to see which part consumes most of CPU.

On Nov 10, 2007 3:12 PM, qihua wu <staywithpin_at_gmail.com> 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...
>
> .
>
>

-- 
Eagle Fan

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