Re: Stats gather for big tables

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