Schema Stats gathering is taking too much time in oracle version

by Vimal   Last Updated June 12, 2019 07:06 AM - source

Oracle DB version-

When I am gathering schema stats with below options,Then it is taking too much time.

EXEC dbms_stats.gather_schema_stats (
   ownname => 'SCHEMA',
   granularity => 'AUTO',
   CASCADE=> true,
   estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   degree => 16

Elapsed: 05:36:38.76


As per oracle doc,

If incremental need to be used then there are several conditions which need to be true in order to take advantage of the incremental statistics collection:

When the following criteria are met, the database updates global statistics incrementally by scanning only the partitions that have changed:

1) The INCREMENTAL value for the partitioned table is true.

2) The PUBLISH value for the partitioned table is true.

3) The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

So my question is ,Do I need to satisfy all these three above mentioned conditions then incremental statistics work.

Related Questions

Oracle Optimizer refuses to use my index

Updated December 06, 2018 12:06 PM

how to force oracle 12c to create histograms

Updated June 20, 2015 02:02 AM

GoldenGate Statistics

Updated October 18, 2017 12:06 PM