Re: Execute Count in AWR Report

I stand corrected. You have obviously devoted much more attention to detail than me. Now that you mentioned adaptive plans, I noticed that Ian hasn't mentioned his DB version anywhere.

On 7/18/20 4:30 PM, Jonathan Lewis wrote:
> Secondly:
> The second execution of the statement has a different execution plan.
> This suggests three obvious possibilities:
>
> (a) the second run is by a different user with a different optimizer
> environment - but I think that's unlikely because it's Peoplesoft, and
> it's a SYNC process, so it's probably being run by a Peoplesoft "batch
> process" schemaname (Even so, if it is a second child cursor executed
> by a different schema the window for the first child cursor being
> flushed is now 53 minutes before the next AWR snapshot is taken.)
>
> (b) the second run had to re-optimize because the plan (heap 6) from
> the first execution had been flushed from memory (which would knock
> your "the SGA couldn't possibly be so small that something could get
> flushed in 6 minutes" argument on the head). But since the text hasn't
> changed the plan should be unchanged unless the object statistics had
> changed - so I'd probably discount this option too.
>
> (c) Maybe this is a version of Oracle where statistics feedback is
> active and the second execution used a second child cursor because
> Oracle re-optimized based on the statistics it accumulated on the
> first execution. That would make Oracle invalidate the first child -
> which means it would be more likely to be flushed from the library
> cache and, again, we've got a window of 53 minutes for that to happen.
>
> Regards
> Jonathan Lewis

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

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