Re: Execute Count in AWR Report

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Sun, 19 Jul 2020 08:17:47 +0100
Message-ID: <CAGtsp8nbwt5D7fG=p=0RtP+VXbc59cWceZJO=5QPc2zTFAjsjw_at_mail.gmail.com>



This lends weight to my original observation or option (b) of the follow-up. Either the entire statement was flushed from memory, or the plan was flushed from memory and recreated.

If the new plan has appeared as a result of some adaptive/dynamic feature of 12c+ then I think the note would have said one of two things:

“Statistics feedback used for this statement”

or

“One SQL Directive used, dynamic statistics used”.

The change in plan would then have occurred (despite my previous comment about discounting the option) because of the dynamic sampling (which doesn't seem to have been very effective anyway give that operation 5 shows an "index unique scan" with an estimated far from unique 796 rows per start).

I think option (b) would mean it's possible for some of the statistics from the first run to be included in the total. I would use the "rows processed" statistic as the benchmark to check whether the rest of the stats (i.e. buffer gets, disc reads) summed the two executions or covered just the second. The "rows processed" is only reported against "SQL ordered by Executions" in the basic AWR report - and with one execution it won't be there so you'll have to get the AWR SQL report for that SQL_ID and interval to find the numbers you want to see.

Regards

Jonathan Lewis

On Sat, Jul 18, 2020 at 11:33 PM MacGregor, Ian A. < dmarc-noreply_at_freelists.org> wrote:

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

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jul 19 2020 - 09:17:47 CEST

Original text of this message