Re: Execute Count in AWR Report
Date: Sat, 18 Jul 2020 22:32:20 +0000
Message-ID: <BYAPR07MB51600D68AD49AF75A20D575AE27D0_at_BYAPR07MB5160.namprd07.prod.outlook.com>
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mladen Gogala <gogala.mladen_at_gmail.com> Sent: Saturday, July 18, 2020 2:18 PM
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: 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 -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jul 19 2020 - 00:32:20 CEST