Re: Stats collection issue in 19C

From: Nenad Noveljic <nenad.noveljic_at_gmail.com>
Date: Tue, 4 Oct 2022 10:05:10 +0300
Message-Id: <2F1A6992-A660-4A23-A92B-82172E2E9DA3_at_gmail.com>

Could you check if new histograms were created:

SELECT *
FROM table(dbms_stats.diff_table_stats_in_history(

              ownname => 'USER',
              tabname => 'TABLE',
              time1 => localtimestamp,
              time2 => localtimestamp-to_dsinterval('5 00:00:15'),
              pctthreshold => 0
            ));

New histograms could have triggered the recreation of the synopses.

Best regards,
Nenad

Von meinem iPhone gesendet

> Am 04.10.2022 um 09:34 schrieb Lok P <loknath.73_at_gmail.com>:
> 
> 
> Hi Pap, I can't remember exactly the discussion (mostly from Jonathan Lewis) sometimes in past I read , you have to do it something as below.. 
> 
> Export your existing stats manually to a table and then update the global_stats flag to NO there and then import it back to the data dictionary back.
> 
> Or else you can pass the granularity parameter as 'partition' such that the underlying SQL taking time I. E global column stats won't trigger. So your stats collection will be faster. But then your partition stats will be accurate but it won't gets rolled up to global level automatically. Thats an issue for queries relying on global statistics. 
> 
> Regarding your incremental stats, the global_stats flag will be YES only. But as you mentioned it's still going for scanning full table and gathering column stats each time it triggers. So it may be because of change in behaviour of granularity parameter ALL from 11g vs 19C, so I would suggest you try with AUTO. 
> 
> 
> 
> 
>> On Tue, 4 Oct, 2022, 2:11 am Pap, <oracle.developer35_at_gmail.com> wrote:
>> Thank You Lothar.
>> 
>> Something odd I am noticing. Because we have many partitioned tables and this issue we are noticing for a handful of them and another thing i notice, for the table which has no table level preferences set and also we are just passing a hard coded partition_name without any incremental, the Global_stats column in dba_tables is showing "YES". Ideally it should show Global_stats as 'NO'. Not sure how it happened and if it's responsible for making the stats to be collected as global each time.  How to fix this ?
>> 
>> And also the synopsis you mentioned is related to incremental stats only, and for the partitioned table with incremental set as TRUE, i see the Granularity set as 'ALL', so is that the cause for making it go for collecting global column stats each time and we should turn it to AUTO then? But my worry is if just tweaking the 'Granularity" to AUTO will make the oracle go scanning/collecting synopsis from scratch for all the partitions again for the first stats run? 
>> 
>> Also if i remember correctly , in the 11G period we encountered an issue in which Granularity =>AUTO was not collecting stats on the subpartitions, so we have set it to ALL and it was working perfectly fine with that setup in the 11.2 version. Not sure if some changes happened in 19C with respect to that.
>> 

>>> On Tue, Oct 4, 2022 at 1:59 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>>> Hi,
>>>
>>> I am trying some educated guess right now. In order to do incremental
>>> stats, an auxiliary table called synopsis must be generated.
>>> Basically the synopsis is a sample of the NDV of a partition.
>>> When you set a table to incremental, the synopsis must be build for all
>>> existing partitions once. So this first run still scans all partitions,
>>> only the next runs from there on will be incremental.
>>> The synopsis from version 11g differs from that in 19c IMHO. Thus, it
>>> might be that after the upgrade a new, full size synopsis must be generated.
>>> That could look similar to global stats.
>>> If that is correct, things should switch bach to normal by themselves.
>>>
>>> Thanks
>>>
>>> Lothar
>>>
>>> Am 03.10.2022 um 22:14 schrieb Pap:
>>> > 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-l

Received on Tue Oct 04 2022 - 09:05:10 CEST