Re: Wrong column stats in 10053
Date: Tue, 11 May 2021 18:13:41 +0300
Message-ID: <CAOVevU7E4oG1y5rvPRSkhivpCSxmwLj0QvzdCGm5L+eUhaovJw_at_mail.gmail.com>
On Tue, May 11, 2021 at 6:05 PM Dominic Brooks <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://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *Dominic Brooks <dombrooks_at_hotmail.com>
> *Sent: *11 May 2021 12:34
> *To: *ORACLE-L <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 <dombrooks_at_hotmail.com>
> *Sent: *11 May 2021 11:49
> *To: *ORACLE-L <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 <dombrooks_at_hotmail.com>
> *Sent: *11 May 2021 11:34
> *To: *ORACLE-L <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
>
>
>
>
>
>
>
>
>
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 11 2021 - 17:13:41 CEST