加快物化视图刷新

How to speed up the MV refresh

1)PARALLELISM
Parallel execution enables multiple processes to work simultaneously to refresh the materialized view, resulting in speeding up the refresh process. Before you enable parallel execution in your database, you should ensure that you have enough resources (CPU and Memory) to run multiple processes in parallel. Setting the parameter PARALLEL_MAX_SERVERS to a value greater than 0 enables parallel

execution.

 

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',PARALLELISM=>4);

 

 

2)ATOMIC_REFRESH

Parameter:ATOMIC_REFRESH influenced DBMS_MVIEW.REFRESH refresh.

ATOMIC_REFRESH parameter for materialized view refresh is meant to control whether each materialized

view in a list is refreshed in its own transaction, or whether all materialized views are refreshed

together in one transaction to a single point in time.

In 10g and above, if ATOMIC_REFRESH is set to TRUE (the default):

+If a single materialized view is being refreshed, a row-by-row delete will be done prior to inserting

rows back into the materialized view. This makes the refresh slower, but also maintains a principle of

atomicity that implies that the materialized view should allow read-consistent queries against it

during the refresh for the refresh to be atomic. This is why the change in behavior of the

ATOMIC_REFRESH parameter was done.

+If a group of materialized views are being complete refreshed, each materialized view in the group

will be deleted from row-by-row prior to inserting the data back in. In addition, all materialized

views are refreshed together in the same transaction; either all refreshes succeed, or if one fails

all of the refreshes will be rolled back. This is the same behavior as in 9.2.

In 10g and above, if ATOMIC_REFRESH is set to FALSE, the behavior is the same as in 9.2, i.e:

+If a single materialized view is being complete refreshed, the materialized view will be truncated.

+If a group of materialized views are being complete refreshed, each one will be truncated and

refreshed individually in a separate transaction. The number of job queue processes must be set to 1

or greater if this parameter is false.

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'MV_PART_SALES',METHOD=>'C',atomic_refresh =>false);