How to Refresh a Materialized View in Parallel

Parallel execution enables multiple processes to work simultaneously to refresh the materializedview, 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. Once you decide to use parallel execution, you should set the initialization parameter PARALLEL_AUTOMATIC_TUNING to TRUE. This enables Oracle to perform automatic tuning of the parallel execution environment.

Now that you have enabled parallel execution in the database, you can employ it while

refreshing the materialized view. In the following sections, we will discuss various ways to

invoke parallel refresh.

• The PARALLELISM parameter of the DBMS_MVIEW.REFRESH procedure

• PARALLEL attribute of the materialized view

• PARALLEL attribute of the master table(s)

• PARALLEL hint in the query defining the materialized view

The PARALLELISM Parameter of the DBMS_MVIEW.REFRESH Procedure

The REFRESH procedure of the supplied package DBMS_MVIEW can be used to refresh a

materialized view. Among other parameters, this procedure takes one parameter

PARALLELISM. You can invoke this procedure as:

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

At the outset, it appears that the PARALLELISM parameter will invoke a parallel refresh of the

materialized view. However, it doesn’t. The dynamic performance views V$PX_PROCESS and

V$PX_SESSION provide information on the parallel execution processes and the sessions

using parallel execution respectively. When the above refresh process is running, if we query

these views, we will see that there are no parallel execution processes in use.

PARALLEL Attribute of the Materialized View

Now let’s create the materialized view with the PARALLEL attribute, and investigate the refresh

behavior.

CREATE MATERIALIZED VIEW MV_PART_SALES

PARALLEL 4

AS

SELECT PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

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

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

When the materialized view is created with the PARALLEL clause, the creation process is

parallelized, but the refresh process is not. Whether or not you specify the PARALLELISM

parameter in the REFRESH clause, it doesn’t matter. The refresh will be executed in serial.

PARALLEL Attribute of the Master Table

Now let’s examine the impact of the PARALLEL attribute of the master table, instead of the

materialized view. We will alter the master table to set the PARALLEL attribute to 4, and then

create the materialized view without a PARALLEL clause.

ALTER TABLE SALES_HISTORY PARALLEL (DEGREE 4);

• Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

DROP MATERIALIZED VIEW MV_PART_SALES;

CREATE MATERIALIZED VIEW MV_PART_SALES

AS

SELECT PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

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

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

When the master table of the materialized view has the PARALLEL attribute set to > 1, then the

creation as well as the refresh processes will be parallelized. Whether or not you specify the

PARALLELISM parameter in the REFRESH clause, it doesn’t matter.

PARALLEL Hint in the Query Defining the Materialized View

Now let’s examine the refresh behavior by putting a parallel hint in the materialized view

definition.

CREATE MATERIALIZED VIEW MV_PART_SALES

AS

SELECT /*+ PARALLEL(SALES_HISTORY, 4) */

 PART_ID, SALE_DATE, SUM(QUANTITY)

FROM SALES_HISTORY

GROUP BY PART_ID, SALE_DATE;

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

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

When the materialized view is created with a PARALLEL hint, then the creation as well as the

refresh processes will be parallelized. Whether or not you specify the PARALLELISM parameterin the REFRESH clause, it doesn’t matter.

Source: Sanjay Mishra's sql tuning book