Re: Wrong column stats in 10053
Date: Tue, 11 May 2021 19:23:26 +0300
Message-ID: <CAOVevU435sD=xWSJRox47rzq+wx4zYZcBjh6XASn5KyfCE4P=g_at_mail.gmail.com>
Forgot to describe why it helps: making this column invisible and visible again changes columns order: after the change this virtual column becomes "last" column in the table:
SQL> create table t1
2 (id number not null 3 ,status int generated always as (decode(yn,'Y',1,2)) 4 ,yn varchar2(1) not null5 );
SQL> select column_name,column_id,segment_column_id,internal_column_id from user_tab_cols tc where table_name='T1';
COLUMN_NAME COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------ ---------- ----------------- ------------------ ID 1 1 1 STATUS 2 2 YN 3 2 3 SQL> alter table t1 modify status invisible;SQL> alter table t1 modify status visible; SQL> select column_name,column_id,segment_column_id,internal_column_id from user_tab_cols tc where table_name='T1';
COLUMN_NAME COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------ ---------- ----------------- ------------------ ID 1 1 1 STATUS 3 2 YN 2 2 3
3 rows selected.
*As you can see its INTERNAL_COLUMN_ID is still 2, but COLUMN_ID is 3 now. * *And you will get another column order in case of using ** *Just another reason to do not use * without dynamic cursor definition :)*
SQL> select * from t1 where rownum=1;
ID YN STATUS
---------- -- ----------
1 Y 1
On Tue, May 11, 2021 at 7:11 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
> 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
>
-- 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 - 18:23:26 CEST