Re: SQL Plan confusion
Date: Thu, 5 Sep 2019 17:30:16 +0000
Message-ID: <LNXP265MB10651A754A088BA022DFE7ABA5BB0_at_LNXP265MB1065.GBRP265.PROD.OUTLOOK.COM>
It looks like you're running with optimizer_mode = first_rows_1 (or maybe first_rows, or first_rows(1)) and this, combined with the other nasty optimizer settings you get with Peoplesoft has taken you through a path which is using the "first k rows" method at a point where Oracle HAS to get all rows back to continue.
Can you test the query forcing all_rows optimization, or first_rows_100, to see what changes.
When reporting plans with execution stats, it's worth including the 'cost' option in the format so we can see what the optimizer thinks the cost of (e.g.) that index fast full scan would be - I'm guessing it thinks the cost is tiny because it's expecting to find its first match almost immediately. You could also add the 'outline' format option so that you can see all the funny parameter settings that this query is running with.
Note - the difference in estimates when you run your standalone query could arise because the application changes the optimizer_mode on the fly but you're running from SQL*Plus with all_rows.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> Sent: 05 September 2019 17:09
To: oracle-l_at_freelists.org
Subject: SQL Plan confusion
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.
- 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?
- 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.
- 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 ...... 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 |
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-lReceived on Thu Sep 05 2019 - 19:30:16 CEST