Re: Execute Count in AWR Report

The version is 18.9 i.e the January 2020 database release update has been applied. The plan changed due to dynamic sampling.

Plan hash value: 3515813421

|   0 | INSERT STATEMENT      | | | |   170K(100)| |
|   1 |  LOAD TABLE CONVENTIONAL      | PS_SL_PROJ_RES | | |      | |
|   2 |   NESTED LOOPS ANTI      | |  1957K|  1053M|   170K  (1)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| PS_PROJ_RESOURCE |  1958K|   971M|   169K  (1)| 00:00:07 |

|   4 |     INDEX RANGE SCAN      | PSDPROJ_RESOURCE |  1958K| |  5246   (1)| 00:00:01 |
|   5 |    INDEX UNIQUE SCAN      | PS_SL_PROJ_RES |   796 | 35024 |     0   (0)| |
----------------------------------------------------------------------------------------------------------

Ian A. MacGregor
SLAC National Accelerator Laboratory
Computing Division
To offer the best IT service at the lab and be the IT provider of choice.

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