Re: Execute Count in AWR Report
Date: Sat, 18 Jul 2020 22:32:20 +0000
Message-ID: <BYAPR07MB51600D68AD49AF75A20D575AE27D0_at_BYAPR07MB5160.namprd07.prod.outlook.com>
| 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_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