Hi Dominic,
Looks very similar to a set of old bugs
Bug 11814428 - Poor cardinality estimate from join with a union-all view -
superseded (Doc ID 11814428.8)*Bug 23249829 - Incorrect cardinality
estimate for join involving union all view (Doc ID 23249829.8)*
which should be fixed :)
On Tue, May 11, 2021 at 6:05 PM Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
From 10053:
First for T1:
Table Stats::
Table: T1 Alias: T1
Column (#7): YN(VARCHAR2)
AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.500000
Column (#8): BUSINESS_DATE(DATE)
AvgLen: 8 NDV: 10 Nulls: 0 Density: 0.100000 Min: 2459336.000000 Max:
2459345.000000
Then later in same trace for inline view:
Table Stats::
Table: X Alias: X (NOT ANALYZED)
Column (#8): BUSINESS_DATE(DATE) NO STATISTICS (using defaults)
AvgLen: 7 NDV: 0 Nulls: 0 Density: 0.000000
Column (#7): YN(VARCHAR2)
AvgLen: 8 NDV: 10 Nulls: 0 Density: 0.100000 Min: 2459336.000000 Max:
2459345.000000
Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
Windows 10
*From: *Dominic Brooks <dombrooks@xxxxxxxxxxx>
*Sent: *11 May 2021 12:34
*To: *ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Subject: *RE: Wrong column stats in 10053
Ok – got enough for Oracle Support now.
As suspected, seems to be related to inline views and UNION ALL, this
combination leads to a single table access predicate section in the 10053
for the inline view where the column stats mappings seem to get shifted
after the virtual column.
Change UNION ALL to UNION and no problem.
This comes from my colleague’s investigation into wider performance
problems with such a view used in a bunch of reporting.
Demo – see top level SELECT estimate of Rows = 1 etc.
drop table t1;
create table t1
(id number not null
,version number not null
,create_ts timestamp not null
,modify_ts timestamp
,status varchar2(24) generated always as
(NVL2("MODIFY_TS",'SUPERSEDED','LATEST'))
,id2 number not null
,yn varchar2(1) not null
,business_date date not null);
insert into t1
(id, version, create_ts, id2, yn, business_date)
select rownum
, 1
, systimestamp
, rownum
, case when mod(rownum,2) = 1 then 'Y' else 'N' end
, trunc(sysdate,'MON') + mod(rownum,10)
from dual
connect by level <= 1000;
exec dbms_stats.gather_table_stats(USER,'T1');
explain plan for
with x as
(select * from t1
union all
select * from t1)
select *
from x
where yn = 'Y';
select * from table(dbms_xplan.display);
Plan hash value: 3505968351
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 6 (0)|
00:00:01 |
| 1 | VIEW | | 1000 | 40000 | 8 (0)|
00:00:01 |
| 2 | UNION-ALL | | | | |
|
|* 3 | TABLE ACCESS STORAGE FULL| T1 | 500 | 20000 | 4 (0)|
00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| T1 | 500 | 20000 | 4 (0)|
00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("T1"."YN"='Y')
filter("T1"."YN"='Y')
4 - storage("T1"."YN"='Y')
filter("T1"."YN"='Y')
Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
Windows 10
*From: *Dominic Brooks <dombrooks@xxxxxxxxxxx>
*Sent: *11 May 2021 11:49
*To: *ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Subject: *RE: Wrong column stats in 10053
Possibly related to a virtual column, all column stats in the 10053 before
the virtual column are correct, then there seems to be a shift/misplacement
after the VC.
But only when there is an inline view and a UNION ALL involved!
Still under investigation.
Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
Windows 10
*From: *Dominic Brooks <dombrooks@xxxxxxxxxxx>
*Sent: *11 May 2021 11:34
*To: *ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Subject: *Wrong column stats in 10053
Has anyone observed wrong column stats (avg len, ndv, nulls, density, min,
max) being used in 10053 trace?
This is in 19.6
For example, 10053 reports stats for column #8 BUSINESS DATE which are
actually the stats for column #9, some ID.
Still under investigation. Just asking early.
Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
Windows 10
