Nice one. Thanks.
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: Sayan Malakshinov<mailto:xt.and.r@xxxxxxxxx>
Sent: 11 May 2021 17:11
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Cc: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Wrong column stats in 10053
Huh, I've just found very funny solution:
alter table t1 modify status invisible;
alter table t1 modify status visible;
Full test case.
https://gist.github.com/xtender/e385961ed4ded3e5a9dda80520eeafb9
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgist.github.com%2Fxtender%2Fe385961ed4ded3e5a9dda80520eeafb9&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016387182%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=dSYgfEjDBGJsIqmBXQM8w9s3Xa%2Bf4hAyw2yb6yTJX%2FI%3D&reserved=0>
PS. From 10053 looks like CBO incorrectly calculates "Rounded cardinality":
kkecdn: Single Table Predicate:"X"."YN"='Y'
Table: X Alias: X
Card: Original: 2000.000000 Rounded: 20 Computed: 20.000000 Non
Adjusted: 20.000000
On Tue, May 11, 2021 at 6:13 PM Sayan Malakshinov
<xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>> wrote:
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<mailto: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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016387182%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=XQ3CuboTuEBFr%2FXLiIeQm%2Fjmcy5J4iJ8%2FHCouxJAlSg%3D&reserved=0>
for Windows 10
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 11 May 2021 12:34
To: ORACLE-L<mailto: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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016397174%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=2y%2F%2BhsB05%2FvYM1yGUdhxyesNhYGpDiELIpFRhaF2law%3D&reserved=0>
for Windows 10
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 11 May 2021 11:49
To: ORACLE-L<mailto: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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016397174%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=2y%2F%2BhsB05%2FvYM1yGUdhxyesNhYGpDiELIpFRhaF2law%3D&reserved=0>
for Windows 10
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 11 May 2021 11:34
To: ORACLE-L<mailto: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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016407168%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=RfOaqhrBV83IPLBJiGrO5O4FE3yZPPKdFfdF01R9dkE%3D&reserved=0>
for Windows 10
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016407168%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Ek%2FKxOp0wzFE8eTZfeXnZ6%2B356CDSmv%2FS%2BTGibhsslE%3D&reserved=0>
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7C%7C308cdce667894ff9917708d9149775e4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637563463016417161%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=n3yCCznv5MLC4xa0YWdKAHJjJsIGg%2FlJFhQk5ccTFa4%3D&reserved=0>
