Parallel Refresh of a Materialized View

This is a quick post regarding materialized views refresh.

Refresh is invoked with a call to procedure DBMS_MVIEW.REFRESH. For large MVs it is highly desired that the refresh takes place in parallel. The REFRESH procedure parameter "parallelism" makes you think that this is the right tool for this job. Nope!

As much attractive as it might seen, this parameter does not help towards the parallelism of the refresh. You have to either include a parallel hint in the SELECT part of the definition statement of the MV (create materialized view) or/and include a PARALLEL DEGREE > 1 to the base tables of the MV.


Check out this article for all the gory details.

Enjoy!

Update

I' ve decided to add a simple example to show live what is described in this article. There are 4 different options that we will try in order to achieve a parallel MV refresh. Namely these are the following:

A.    The PARALLELISM parameter of the DBMS_MVIEW.REFRESH procedure
B.    PARALLEL attribute of the materialized view
C     PARALLEL attribute of the base table(s)
D.    PARALLEL hint in the query defining the materialized view

Initially, lets create a simple MV with a parallel degree of 1. Also the degree of all the underlying base tables are also 1.

nikos@NIKOSDB> create materialized view mv_sales
  2  as SELECT  ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  3  FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  4  WHERE s.time_id = t.time_id
  5     AND   s.cust_id = c.cust_id
  6     AND   s.channel_id = ch.channel_id
  7     AND   c.cust_state_province = 'CA'
  8     AND   ch.channel_desc in ('Internet','Catalog')
  9     AND   t.calendar_quarter_desc IN ('1999-01','1999-02')
 10  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
 11  /

Materialized view created.

nikos@NIKOSDB> -- Check DOP for MV and base tables
nikos@NIKOSDB> select owner, table_name, degree
  2  from dba_tables
  3  where
  4     owner in ('NIKOS', 'SH')
  5     AND table_name in ('MV_SALES', 'SALES', 'TIMES', 'CUSTOMERS', 'CHANNELS')
  6  /

OWNER                          TABLE_NAME                     DEGREE
------------------------------ ------------------------------ ----------
NIKOS                          MV_SALES                                1
SH                             TIMES                                   1
SH                             SALES                                   1
SH                             CUSTOMERS                               1
SH                             CHANNELS                                1
 

Listing 1: Create an MV with parallel degree of 1.

First, lets try to refresh the MV as is, i.e., with a degree of 1 for the MV and all its base tables. Also we will use the default value of the "parallelism" parameter of the REFRESH procedure which is 0.

nikos@NIKOSDB> exec DBMS_MVIEW.REFRESH(list => 'MV_SALES', method => 'C', atomic_refresh => FALSE)

PL/SQL procedure successfully completed.

nikos@NIKOSDB> -- from another session
nikos@NIKOSDB> @px_get_dop_sql

Session altered.

Elapsed: 00:00:00.01
Enter value for username: nikos
Enter value for sql_id:

no rows selected
 

Listing 2: An MV refresh with default parallelism and MV/base tables degree of 1. No parallel refresh takes place.

While the refresh is running, from another session we check for the parallelism by querying V$PX_SESSION. No rows are returned which means that no parallelism is used in the refresh.
By the way, the script we have used for checking if a parallel statement is executed by a specific user and/or sql_id, appears in Listing 3.

nikos@NIKOSDB> host cat px_get_dop_sql.sql
set pagesize 999
set lines 999
col username format a13
col prog format a30 trunc
col sql_text format a100 trunc
col sid format 9999
col child for 99999
col avg_etime for 999,999.99
break on sql_id
compute COUNT LABEL TotalSessions OF distinct sid on sql_id

alter session set NLS_DATE_FORMAT = 'dd-mm-yyyy HH24:mi:ss'
/

select t2.inst_id, t2.sid, t2.serial#, t2.username, t2.sql_id, t2.sql_child_number, t1.DEGREE, t1.REQ_DEGREE,
        t2.status, t2.logon_time, t2.program prog, t2.machine, sql_text
from gv$PX_SESSION t1 join gv$session t2 on (t1.inst_id = t2.inst_id and t1.sid = t2.sid  and t1.serial# = t2.serial#)
                left outer join gv$sql t3 on (t2.inst_id = t3.inst_id and t2.sql_id = t3.sql_id and t2.sql_child_number = t3.child_number)
where
username = nvl(upper(trim('&username')), username)
and t2.sql_id = nvl(trim('&sql_id'),t2.sql_id)
order by t2.sql_id, t2.sql_child_number
/
 

Listing 3: A simple script based on V$PX_SESSION for checking parallelism in statements.

Now, that we know that the "default" refresh is not executed in parallel, lets try the four options one by one.

Option A.    The PARALLELISM parameter of the DBMS_MVIEW.REFRESH procedure

In this option we will use the "parallelism" parameter of the REFRESH procedure. Lets give it a try with a value of 4.

nikos@NIKOSDB> exec DBMS_MVIEW.REFRESH(list => 'MV_SALES', method => 'C', parallelism => 4, atomic_refresh => FALSE)

PL/SQL procedure successfully completed.

-- from another session

nikos@NIKOSDB> @px_get_dop_sql

Session altered.

Elapsed: 00:00:00.01
Enter value for username: nikos
Enter value for sql_id:

no rows selected
 

Listing 4: An MV refresh with a value of 4 for "parallelism" parameter. No parallel refresh takes place.

As you can see in Listing 4, the parallelism  parameter of the DBMS_MVIEW.REFRESH procedure does not help in invoking a parallel refresh of an MV.

Option B.    PARALLEL attribute of the materialized view

In this option, we will drop and recreate the MV, but this time with a PARALLEL DEGREE of 4. Then we will try to refresh the MV and check once more for parallelism.

nikos@NIKOSDB> drop materialized view mv_sales
  2  /

Materialized view dropped.

nikos@NIKOSDB> create materialized view mv_sales
  2  parallel 4
  3  as SELECT  ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  4  FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  5  WHERE s.time_id = t.time_id
  6     AND   s.cust_id = c.cust_id
  7     AND   s.channel_id = ch.channel_id
  8     AND   c.cust_state_province = 'CA'
  9     AND   ch.channel_desc in ('Internet','Catalog')
 10     AND   t.calendar_quarter_desc IN ('1999-01','1999-02')
 11  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
 12  /

Materialized view created.

nikos@NIKOSDB> -- Check DOP for MV and base tables
nikos@NIKOSDB> select owner, table_name, degree
  2  from dba_tables
  3  where
  4     owner in ('NIKOS', 'SH')
  5     AND table_name in ('MV_SALES', 'SALES', 'TIMES', 'CUSTOMERS', 'CHANNELS')
  6  /

OWNER                          TABLE_NAME                     DEGREE
------------------------------ ------------------------------ ----------
NIKOS                          MV_SALES                                4
SH                             TIMES                                   1
SH                             SALES                                   1
SH                             CUSTOMERS                               1
SH                             CHANNELS                                1

nikos@NIKOSDB> exec DBMS_MVIEW.REFRESH(list => 'MV_SALES', method => 'C', parallelism => 4, atomic_refresh => FALSE)

PL/SQL procedure successfully completed.

nikos@NIKOSDB> @px_get_dop_sql

Session altered.

Enter value for username: nikos
Enter value for sql_id:

no rows selected
 

Listing 5: The use of PARALLEL DEGREE > 1 for the MV does not invoke a parallel refresh.

As we can see, the use of PARALLEL DEGREE > 1 for the MV does not invoke a parallel refresh. Although, parallelism is used during the creation of the MV. Lets proceed with the next option.

Option C     PARALLEL attribute of the base table(s)

In this option, we will alter the base tables of the MV in order to set a PARALLEL DEGREE > 1. Specifically, we will alter one of the base tables (SALES) to set a degree of 4. Then we will go for a parallel refresh...

nikos@NIKOSDB> alter table sh.sales parallel 4
  2  /

Table altered.

nikos@NIKOSDB> exec DBMS_MVIEW.REFRESH(list => 'MV_SALES', method => 'C', parallelism => 4, atomic_refresh => FALSE)

PL/SQL procedure successfully completed.

-- from another session
nikos@NIKOSDB> @px_get_dop_sql

Session altered.

Elapsed: 00:00:00.00
Enter value for username: nikos
Enter value for sql_id:

   INST_ID   SID    SERIAL# USERNAME      SQL_ID               SQL_CHILD_NUMBER     DEGREE REQ_DEGREE STATUS   LOGON_TIME          PROG                           MACHINE                                                  SQL_TEXT
---------- ----- ---------- ------------- -------------------- ---------------- ---------- ---------- -------- ------------------- ------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
         1    80        764 NIKOS         6hpw9ht9rp425                       0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P000)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1    12       1377 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P001)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   131       1784 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P002)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   203        434 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P003)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   202        221 NIKOS                                             0                       ACTIVE   14-02-2015 19:33:24 sqlplus.exe                    CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   143        954 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P005)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   199       1023 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P006)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1    17        445 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P007)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1     9       2126 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:19:23 ORACLE.EXE (P004)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
           -----                          ********************
               9                          TotalSessions

9 rows selected.
 

Listing 6: A Parallel MV refresh due to a DEGREE of 4 in one of the base tables.

Voila! A parallel MV refresh! As we can see, by setting the PARALLEL DEGREE attribute of the base table (Sales) helped parallelism to kick in during the refresh.

Option D.    PARALLEL hint in the query defining the materialized view

Finally, lets try out our last option, which is the use of a parallel hint in the query that defines the MV.

-- First return the DOP of SALES back to 1
nikos@NIKOSDB> alter table sh.sales parallel 1
  2  /

Table altered.
nikos@NIKOSDB> drop materialized view mv_sales
  2
nikos@NIKOSDB>; drop materialized view mv_sales
  2  /

Materialized view dropped.

Elapsed: 00:00:00.15
nikos@NIKOSDB> create materialized view mv_sales
  2  as SELECT /*+ parallel(s, 4) */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  3  FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  4  WHERE s.time_id = t.time_id
  5     AND   s.cust_id = c.cust_id
  6     AND   s.channel_id = ch.channel_id
  7     AND   c.cust_state_province = 'CA'
  8     AND   ch.channel_desc in ('Internet','Catalog')
  9     AND   t.calendar_quarter_desc IN ('1999-01','1999-02')
 10  GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
 11  /

Materialized view created.

nikos@NIKOSDB> exec DBMS_MVIEW.REFRESH(list => 'MV_SALES', method => 'C', parallelism => 4, atomic_refresh => FALSE)

PL/SQL procedure successfully completed.

-- from another session
nikos@NIKOSDB> @px_get_dop_sql

Session altered.

Elapsed: 00:00:00.00
Enter value for username: nikos
Enter value for sql_id:

   INST_ID   SID    SERIAL# USERNAME      SQL_ID               SQL_CHILD_NUMBER     DEGREE REQ_DEGREE STATUS   LOGON_TIME          PROG                           MACHINE                                                  SQL_TEXT
---------- ----- ---------- ------------- -------------------- ---------------- ---------- ---------- -------- ------------------- ------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
         1    71        944 NIKOS         ajq9xvyn9kjzs                       0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P000)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1     9       2468 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P001)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   143       1042 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P002)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   199       1028 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P003)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   202        221 NIKOS                                             0                       ACTIVE   14-02-2015 19:33:24 sqlplus.exe                    CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   131       1792 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P005)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1   203        438 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P006)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1    17        449 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P007)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
         1    12       1382 NIKOS                                             0          4          4 ACTIVE   14-02-2015 21:30:43 ORACLE.EXE (P004)              CENTRAL-DOMAIN\LAP-8KVBBT1                                       /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "NIKOS"
           -----                          ********************
               9                          TotalSessions

9 rows selected.

Listing 7: A Parallel MV refresh due to a Parallel hint in the definition query of the MV.

As we can see, the use of a parallel hint in the definition query of the MV has caused a parallel refresh of the MV.

Summary

In this post, we have tried four different options in order to achieve a parallel refresh of a materialized view. As we have shown, the use of the "parallelism" parameter of the DBMS_MVIEW.REFRESH procedure (option A) does not help towards a parallel refresh. Neither does the PARALLEL DEGREE > 1 attribute of the MV (option B), although parallelism is used during the creation of the MV.

The two options that work are the use of a PARALLEL DEGREE > 1 for the base tables of the view, or/and the use of a parallel hint in the definition query of the MV.

We hope that the above are helpful in clarifying the parallelism during the refresh of materialized views.