Re: SQL Plan confusion

Hi Paul,

you might want to check the sql plan directives used. It is possible that they cause the dynamic sampling. (see: https://de.slideshare.net/ludovicocaldara/adaptive-features-or-how-i-learned-to-stop-worrying-and-troubleshoot-the-bomb-79217014:) You might get rid of the directives.
If the issue remains I should be a bug.

Regards

Lothar

Am 05.09.2019 um 18:09 schrieb Paul Houghton:
> I am trying to help a developer tune a query, and am confused by something I found in the plan.
>
> Oracle 12.2.0.1 with April 2019 patch set update only. OS is Oracle Linux Server release 6.10
> Application is PeopleSoft. We have the following optimizer affecting parameters set:
>
> _gby_hash_aggregation_enabled=false
> _unnest_subquery=false
> Optimizer_adaptive_statistics=TRUE
> Optimizer_capture_sql_plan_baselines=FALSE
>
> The plan is quite large, and involves several views, so I have put it in a file here:
> http://people.ds.cam.ac.uk/psh35/xplan.txt
>
> I pasted some relevant snippets below. I hope I can get away without posting all the object definitions.
>
> The part I am confused by is line 82. It is doing an index fast full scan and thinks it will get one row, but actually gets 83K. I haven't done the maths, but I believe this is at least part of the reason a view is called 145M times, when the optimizer thinks it will be called once.
>
> I can't understand why the optimizer is so far wrong, because as I see it, this line is just doing in effect:
>
> Select some indexed columns from ps_adm_appl_plan E where "E"."ACAD_PLAN"='EGTX' AND "E"."ACAD_CAREER"='UGRD'
>
> When I look at that plan (Pasted at the end of xplan.txt), the optimizer correctly estimates the number of rows returned.
>
> 1) Is this poor cardinality estimate, where the database could reasonably be expected to get the correct number, a bug? Or is there a reason I don't understand?
>
> 2) It says it is using dynamic sampling, but with level=2 I thought this should only be done for tables without stats? There are no tables without stats in this schema.
>
> 3) We noticed on line 96 TO_NUMBER(SUBSTR("UC_APPL_YEAR",3,2))=20 which does seem to be part of the problem. There is a similar issue on line There is a similar issue on line 93.
>
> --------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows |
> --------------------------------------------------------------------------------------------------------
> ... Snip ...
> |* 75 | HASH JOIN | | 1 | 1 | 1297 |
> |* 76 | INDEX RANGE SCAN | PS3ADM_APPL_PROG | 1 | 1 | 10688 |
> | 77 | SORT AGGREGATE | | 11702 | 1 | 11702 |
> |* 78 | TABLE ACCESS BY INDEX ROWID BATCHED| PS_ADM_APPL_PROG | 11702 | 1 | 38172 |
> |* 79 | INDEX RANGE SCAN | PSAADM_APPL_PROG | 11702 | 5 | 42819 |
> | 80 | SORT AGGREGATE | | 10688 | 1 | 10688 |
> |* 81 | INDEX RANGE SCAN | PS_ADM_APPL_PROG | 10688 | 1 | 10787 |
> |* 82 | INDEX FAST FULL SCAN | PS_ADM_APPL_PLAN | 1 | 1 | 82677 |
> | 83 | TABLE ACCESS BY INDEX ROWID | PS_ADM_APPL_DATA | 1297 | 1 | 1297 |
> ... Snip ...
> 75 - access("E"."EMPLID"="D"."EMPLID" AND "E"."ACAD_CAREER"="D"."ACAD_CAREER" AND "E"."STDNT_CAR_NBR"="D"."STDNT_CAR_NBR" AND
> "E"."ADM_APPL_NBR"="D"."ADM_APPL_NBR" AND "E"."EFFDT"="D"."EFFDT" AND "E"."EFFSEQ"="D"."EFFSEQ")
> 76 - access("D"."CAMPUS"='JE' AND "D"."ACAD_CAREER"='UGRD')
> filter(("D"."ACAD_CAREER"='UGRD' AND "D"."EFFDT"= AND "D"."EFFSEQ"=))
> 78 - filter(("D_ED"."ADM_APPL_NBR"=:B1 AND "D_ED"."CAMPUS"<>'POOL' AND "D_ED"."EFFDT"<=SYSDATE_at_! AND "D_ED"."STDNT_CAR_NBR"=:B2))
> 79 - access("D_ED"."EMPLID"=:B1 AND "D_ED"."ACAD_CAREER"=:B2)
> 81 - access("D_ES"."EMPLID"=:B1 AND "D_ES"."ACAD_CAREER"=:B2 AND "D_ES"."STDNT_CAR_NBR"=:B3 AND "D_ES"."ADM_APPL_NBR"=:B4 AND "D_ES"."EFFDT"=:B5)
> filter("D_ES"."EFFDT"=:B1)
> 82 - filter(("E"."ACAD_PLAN"='EGTX' AND "E"."ACAD_CAREER"='UGRD'))
> ... Snip ...
> Note
> -----
> - dynamic statistics used: dynamic sampling (level=2)
> - this is an adaptive plan
> - 8 Sql Plan Directives used for this statement
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 




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