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
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.
Do I need to satisfy all these three above mentioned conditions then incremental statistics work?
Oracle DB version-12.2.0.1.0
Storage: Oracle FS1-2 storage with 1.2TB /1.6 TB SAS disks.
OWNER SEGMENT_TYPE SIZE_IN_GB
================================================
XXXXX INDEX SUBPARTITION 87.50
XXXXX TABLE SUBPARTITION 48.71
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.
select dbms_stats.get_prefs('INCREMENTAL','SCHEMANAME','TABLENAME') from DUAL;
for the tables involved? You may need to do adbms_stats.set_table_prefs('SCHEMANAME','TABLENAME','INCREMENTAL','TRUE');
for each partitioned table if the first query returnsFALSE
– Philᵀᴹ Jun 12 '19 at 10:01