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

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> 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>
> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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