Others can comment but as per my understanding its 'bite the bullet' scene
and no way to get rid of it. Because although the synopsys already been
created for the rest of the columns , and Oracle should only scan these
newly added columns but as we basic principle says oracle stores rows in
blocks but not columns, so in that way it has to scan through all the rows
again for getting the data pattern pertaining to those newly added columns.
The only thing you can do is rather taking it as a surprise , the dev team
should inform beforehand regarding the new columns , so that you can plan
the first/one time gathering in a controlled fashion with higher parallel
degree. And this first time gather will create the base synopsys after
which the subsequent gather should happen only on the changed data/live
partitions without any issue.
On Tue, Mar 30, 2021 at 12:26 AM Pap <oracle.developer35@xxxxxxxxx> 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
