Re: Stats gather for big tables

Tried creating a sample table having ~130million rows and ~18GBin size holding 8 range partitions without any indexes. And what i found is the time it takes to gather stats on this table with INCREMENTAL TRUE in a single thread is ~3.5minutes. And when I gathered stats for the first time, after adding a new column with method_opt as "for columns new_column size 1", it took the same time. I have also verified in dba_part_col_statstics the last_analyzed was only updated for the new column though, but it seems oracle still has to scan all the partitions and thus no relaxation in overall run time of the stats gather even if I restrict the gather to one column only.

Regards
Pap

On Tue, Mar 30, 2021 at 5:15 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> I would test something like the following:
>
> After adding the column set a table preference the table's method_op that
> includes the phrase "for column {new column name} size 1" so that Oracle
> doesn't (at least initially) gather a histogram.
> Then - before the next auto gather - issue an explcit gather_table_stats
> restricting the stats collection to just that one column.
>
> The entire table will be scanned, the work load and update MIGHT restrict
> itself only to a sysnopsis gathering on the one column, and that might be
> something you can fit into the time-frame you need to avoid a massive
> collection across the whole table.
>
> I can't give you a detailed strategy - there are so many differences in
> what goes on in the different versions in different circumstances that it's
> always a question of coming up with an idea, thinking of possible
> drawbacks, then modelling (usually with extended tracing enabled).
>
> Regards
> Jonathan Lewis
>
>
> P.S. If you are adding columns on a fairly regular basis, the way you
> populate them may make a big difference to how efficiently you can gather
> stats, and how long it will take to get to steady state. And if you have
> added several columns there's a fair chance that you are introduing a huge
> number of chained or migrated rows to your data and need to do something to
> clean up a very big mess.
>
>
>
>
>
>
> On Mon, 29 Mar 2021 at 19:56, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> We are using incremental stats collection approach for gathering
>> statistics on some big partition tables(having size ~20-30 TB). And thus we
>> just have to pass table name to the stats gather block and rest is taken
>> care by oracle from the synopsys information. Now we encountered a
>> situation multiple times in which the team endup adding new columns and the
>> stats collection job started from scratch running for days and then
>> impacting other jobs. So want to understand if there is any better way of
>> handling this situation? If we can by some way restrict Oracle to not scan
>> all the data again and create synopsis from scratch rather utilize existing
>> synopsis and just scan those delta column information which was added to
>> the table? (Oracle version - 11.2.0.4)
>>
>> Regards
>> Pap
>>
>

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