The data in a materialized view is updated by either a complete or incremental refresh.
An incremental or fast refresh uses a log table to keep track of changes on the master table.
The client complained that a user process was running slow.
The speed of a fast refresh will be determined by how much data has changed since the last refresh.If the master table's data is updated very often, then the log table will have more recorded changes to process in order to update the materialized view.The most likely solution was that a complete refresh was happening.However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.One of the most useful replication and data warehousing features in Oracle is materialized views.
Materialized views, also known as snapshots, have been a feature of Oracle for several years.
There was no doubt that a fast refresh was occurring, there were no aggregations in the query, there was a small number of changes to the master table, and network issues were not the problem.
So what was causing this fast refresh to go so slow?
So, the two basic requirements for a fast refresh were confirmed.
Next, I tested the network bound by running copying 30,000 rows from all_objects from the master to the consumer site in 1-2 seconds.
Multiple simple snapshots can use the same snapshot log, meaning that records already used to refresh one snapshot might still be needed to refresh another snapshot. After verifying the existing snapshots on the consumer site by querying SYS.