Re: Execute Count in AWR Report

I'm never in a hurry to declare a bug when a commonly occurring phenomenon has simple explanations that need to be discounted first.

You may be right that the timeline I've suggested is unrealistic for Peoplesoft, but I can see at least two reasons for questioning your opinion

First:
We've been told
"
Frist [sic] run (full sync): FSTST(07/15 12:04) took 3 mins retrieve rows: 1870248
Second run (incremental): FSTST(07/15 12:13) took 1mins 58 sec. retrieve rows 0.
"
A statement that runs for 3 minutes to insert 1.8M rows is not a "everyone's running it all the time and it can't possibly get flushed" OLTP statement even if the system is an OLTP system,

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

On Sat, Jul 18, 2020 at 7:25 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 7/18/2020 2:05 PM, Jonathan Lewis wrote:
> > 12:00:00 - statement not in the library cache
> > 12:04:51 - statement executes for the first time and gets into library
> > cache
> > 12:07:31 - statement finishes first execution and is no longer pinned
> > in library cache
> > 12:10:00 - statement is flushed from library cache
> > 12:13:21 - statement executes for the second time and is reloaded into
> > the library cache
> > 12:14:52 - statement finishes first execution and is no longer pinned
> > in library cache
> > 13:00:00 - statement is still in the library cache and is captured in
> > the snapshot
> >
> 6 minutes before the statement is flushed from the shared pool looks
> very, very improbable, unless Ian is running PeopleSoft with 1GB of SGA.
> Even if we assume moderately small SGA around 16GB, it should take more
> than 6 minutes for a SQL belonging to an OLTP application to get thrown
> out from shared pool. The timeline above looks as if constructed by
> Hercules Poirot. PeopleSoft is an OLTP book keeping application which
> usually keeps a set of around 300 SQL statements in the shared pool. To
> tell the truth, I thought of that myself, which is why I mentioned
> V$SQLSTATS, but discarded that idea as extremely improbable, like
> Russell's teapot in an orbit around Sun.
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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