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 - source

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



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