Stats gather for big tables
From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 30 Mar 2021 00:26:02 +0530
Message-ID: <CAEjw_fhVp3cSUDj0E7qUQ7gDYrz-vR_VDduzEi4c5dzrzUHEtg_at_mail.gmail.com>
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)
Date: Tue, 30 Mar 2021 00:26:02 +0530
Message-ID: <CAEjw_fhVp3cSUDj0E7qUQ7gDYrz-vR_VDduzEi4c5dzrzUHEtg_at_mail.gmail.com>
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-lReceived on Mon Mar 29 2021 - 20:56:02 CEST