RE: Wrong column stats in 10053

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<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://go.microsoft.com/fwlink/?LinkId=550986> 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://go.microsoft.com/fwlink/?LinkId=550986> 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://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

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