Materialized View Refresh is very slow in Oracle Database 12.2
This fix improves refresh performance when statistics-collection level, as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT, is TYPICAL.With this fix, Cardinality Collection is turned on only when STATISTICS_LEVEL = ADVANCED.Rediscovery Notes When stats collection, as set by as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT is TYPICAL, "select count(*)" queries are used to collect cardinalities of tables and MVs. This causes refresh slowdown. |
Same behavior since upgrading from 12.2 to 19c.
Regarding this MOS "12.2: Drop Materialized View Is Very Slow or Hangs (Doc ID 2401976.1)"I had to disable the statistic collection and truncate the statistic tables. Now it works.
