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

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... .

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