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
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 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 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
| 4 | INDEX RANGE SCAN | PSDPROJ_RESOURCE | 1958K| | 5246 (1)|
00:00:01 |
| 5 | INDEX UNIQUE SCAN | PS_SL_PROJ_RES | 796 | 35024 | 0
(0)| |
----------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
The statements are being executed by a single developer on a development
database.
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.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Mladen Gogala <gogala.mladen@xxxxxxxxx>
Sent: Saturday, July 18, 2020 2:18 PM
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
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
