When I am gathering schema stats with below options, 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.

Do I need to satisfy all these three above mentioned conditions then incremental statistics work?

Oracle DB version-

Storage: Oracle FS1-2 storage with 1.2TB /1.6 TB SAS disks.

XXXXX       TABLE PARTITION      14.66
XXXXX       INDEX PARTITION       9.84
XXXXX       INDEX                 0.06
XXXXX       TABLE                 0.03

The output of select dbms_stats.get_prefs('INCREMENTAL','SCHEMANAME','TABLENAME') from DUAL; is false. I have total 345 tables in schema, out of which 320 tables is partitioned(Each table have 11 partitions) . Out of 320 partitioned tables, 239 tables have 0 subpartitions and 81 tables have some random number of subpartitions.

So instead of setting incremental preference on each table, can't we set it on whole schema.I did find options are available but not sure about it,how this settings works at partitions and subpartition level.

  • What's the output of select dbms_stats.get_prefs('INCREMENTAL','SCHEMANAME','TABLENAME') from DUAL; for the tables involved? You may need to do a dbms_stats.set_table_prefs('SCHEMANAME','TABLENAME','INCREMENTAL','TRUE'); for each partitioned table if the first query returns FALSE – Philᵀᴹ Jun 12 '19 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.