Stats collection issue in 19C
Date: Tue, 4 Oct 2022 01:44:24 +0530
Message-ID: <CAEjw_fjQU5AFifFFsYQ+cdC+9cChGD84-wToVPxftWMXaSHS2g_at_mail.gmail.com>
It's a 19.15 version oracle customer database. We are seeing the stats gathering is running a lot longer than it used to happen in the same database when it used to be in version 11G. And it seems to be only happening for partitioned tables.
When trying to look into the underlying sql when the stats gather was
running , I am seeing one type of sqls. It seems to be gathering the column
level stats for the whole table even if we have just passed the exact
partition_name as parameter to the stats gather block as below and also its
happening irrespective of whether the table has INCREMENTAL set as TRUE or
not.
So my question is even if INCREMENTAL is set as TRUE for TAB2, but still
each time it should not collect column level stats in the whole table
level. So why is it doing so? And in case of TAB1 even if we have no
preference set in table level, then Oracle should only gather stats for
that partition, so why is it gathering for global level column stats each
time?
exec
Dbms_stats.gather_table_stats(ownname=>'SCHEMA_APP',tabname=>'TAB1',partname=>'DAY_20221104');
exec
Dbms_stats.gather_table_stats(ownname=>'SCHEMA_APP',tabname=>'TAB2',partname=>'TAB2_11182022_P');
Below is the sql monitoring report for two of the underlying sqls for above two calls. Here TAB2 is defined as INCREMENTAL as TRUE in the dba_tab_stat_prefs. And TAB1 has no preference set in table level.
https://gist.github.com/oracle9999/c2268195a01a11479e18fb488c4bfd69
Regards
Pap
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 03 2022 - 22:14:24 CEST