Incremental Statistics on Partitioned Objects

Ab Oracle 12.2 können die Statistiken z.B. partitionierter Tabellen "inkrementel" erstellt werden. Dadurch entfällt z.B. in DWH-Umgebungen der sehr aufwändige Full-Table-Scan nachdem nur eine Partition geladen wurde.

Um incremental statistics nutzen zu können, muss dieses für die Tabelle eingeschaltet werden:

Einstellungen prüfen:
select
    DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', 'HM19_STAGE', 'I_OKV_FALL_FZ') ESTIMATE_PERCENT
  , DBMS_STATS.GET_PREFS('GRANULARITY', 'HM19_STAGE', 'I_OKV_FALL_FZ') GRANULARITY
  , DBMS_STATS.GET_PREFS('INCREMENTAL', 'HM19_STAGE', 'I_OKV_FALL_FZ') INCREMENTAL
  , DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL', 'HM19_STAGE', 'I_OKV_FALL_FZ') INCREMENTAL_LEVEL
  , DBMS_STATS.GET_PREFS('PUBLISH', 'HM19_STAGE', 'I_OKV_FALL_FZ') PUBLISH
  , DBMS_STATS.GET_PREFS('APPROXIMATE_NDV_ALGORITHM', 'HM19_STAGE', 'I_OKV_FALL_FZ') APPROXIMATE_NDV_ALGORITHM
  , DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS', 'HM19_STAGE', 'I_OKV_FALL_FZ') INCREMENTAL_STALENESS
  from dual;
--> sollte folgendes liefern: DBMS_STATS.AUTO_SAMPLE_SIZE    AUTO    TRUE    PARTITION    TRUE

ggfs. sind die Einstellung zu setzen:
-- Bsp.: incremental stats einschalten
exec DBMS_STATS.SET_TABLE_PREFS('HM19_STAGE', 'I_OKV_FALL_FZ', 'INCREMENTAL', 'TRUE');

Nach dem setzen spezieller Eigenschaften können diese hier abgefragt werden:
-- Tbl-spezielle Einstellungen anzeigen:
select * from USER_TAB_STAT_PREFS;


Vorgehen:
1) Tabelleneigenschaften setzen (idealerweise gleich bei der Erstellung der Tabelle)
exec DBMS_STATS.SET_TABLE_PREFS('HM19_STAGE', 'I_OKV_FALL_FZ', 'INCREMENTAL', 'TRUE');

2) Tabellenstatistiken initial erstellen
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HM19_STAGE', tabname => 'I_OKV_FALL_FZ');

3) Neue Partition anlegen und laden
alter table I_OKV_FALL_FZ ADD PARTITION P_2 VALUES (2);
insert /*+ APPEND */ into I_OKV_FALL_FZ partition for (2) ...;

4) (nach jeden Laden einer neuen Partition): Tabellenstatistik erstellen
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HM19_STAGE', tabname => 'I_OKV_FALL_FZ');
(geht nun sauschnell! :-)



Das Ergebnis kann wie folgt geprüft werden:
- auf Tabellenebene:
select *
  from USER_TAB_COL_STATISTICS UTCC
 where UTCC.TABLE_NAME = 'I_OKV_FALL_FZ';
--> Spalte NOTES mit "INCREMENTAL  " zeigt an, dass Tbl-Statistiken incrementell erstellt wurden

- auf Partitionsebene:
select *
  from USER_PART_COL_STATISTICS UPCC
 where UPCC.TABLE_NAME = 'I_OKV_FALL_FZ';
--> Spalte NOTES mit "STATS_ON_LOAD " zeigt an, dass Statistiken direkt beim Laden erstellt wurden



Quelle u.a.: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/gathering-optimizer-statistics.html#GUID-63E195EB-10F4-428B-9912-3E98C2A957BF