Paul,
Looking at the latest version of the plan - the cost for that fast full scan is
1, which looks a lot lower than it probably ought to be - which still leaves me
thinking that Oracle is using a "first_rows(n)" type of calculation. Since
you're running with ALL_ROWS optimisation it might be an effect of part of the
code being an existences (or similar) subquery - as these too are optimised
with first_rows_1.
Since you've got a temporary workaround I won't pursue it. You might search the
bugfixes for 18.3 and 19.3 for expressions like "first_rows", "fkr", as they
might give you a clue what you're seeing. The only other thing about
cardinality estimates is that there are a couple of pq_filter() hints in the
outline - and I think these are usually associated with Bloom filters - so
maybe part of the problem is some arithmetic relating to the hash join being
applied early due to the anticipated use of a Bloom filter that then doesn't
happen.
(A Bloom filter is generally engineered to eliminate rows from a tablescan /
index fast full scan which is the second table of a hash join- which is what
you're index fast full scan is.)
Regards
Jonathan Lewis
________________________________________
From: Paul Houghton <Paul.Houghton@xxxxxxxxxxxxx>
Sent: 06 September 2019 16:57
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: RE: SQL Plan confusion
Thanks Jonathan
The actual example I posted was run in sql developer. I am not sure if that
sets first_rows, so I added the hint, and that didn't change the estimate. I
have pasted this plan, including cost and outline format at the top of the file:
http://people.ds.cam.ac.uk/psh35/xplan.txt
I also investigated Lothar's suggestion of checking the sql plan directives. He
seems to be able to list the directives used when doing
dbms_xplan.display(format=>'metrics'), but it doesn't work for me. Possibly
because I am on 12.2, and the document appears to be about 12.1. I can't see
any way to display which directives are being used by a plan. The word
"directive" doesn't appear on the documentation page in the Database PL/SQL
Packages and Types Reference. Also the format of metrics doesn't seem to exist
any more unless it is an undocumented feature.
I am not sure I have the patience to raise this with Oracle. We have improved
performance enough by adding a functional index on the function call. This is a
workaround which we intend to fix properly later.
Thanks for your help
Paul Houghton
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Jonathan Lewis
Sent: 05 September 2019 18:30
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL Plan confusion
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Paul Houghton <Paul.Houghton@xxxxxxxxxxxxx>
Sent: 05 September 2019 17:09
To: oracle-l@xxxxxxxxxxxxx
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.
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@! 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
