Schema Stats gathering is taking too much time in oracle version 12.2.0.1

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

Oracle DB version-12.2.0.1.0

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

SCHEMA_SIZE_IN_GB
-----------------
       160.828857

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


Updated July 27, 2015 17:02 PM

Updated December 06, 2018 12:06 PM

Updated June 20, 2015 02:02 AM

Updated July 04, 2019 10:06 AM

Updated October 18, 2017 12:06 PM