RE: Wrong column stats in 10053

Nice one. Thanks.

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Sayan Malakshinov<mailto:xt.and.r_at_gmail.com> Sent: 11 May 2021 17:11
To: Dominic Brooks<mailto:dombrooks_at_hotmail.com> Cc: ORACLE-L<mailto:oracle-l_at_freelists.org> 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_at_gmail.com<mailto:xt.and.r_at_gmail.com>> 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_at_hotmail.com<mailto:dombrooks_at_hotmail.com>> 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_at_hotmail.com> Sent: 11 May 2021 12:34
To: ORACLE-L<mailto:oracle-l_at_freelists.org> 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_at_hotmail.com> Sent: 11 May 2021 11:49
To: ORACLE-L<mailto:oracle-l_at_freelists.org> 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_at_hotmail.com> Sent: 11 May 2021 11:34
To: ORACLE-L<mailto:oracle-l_at_freelists.org> 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>


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