Can't parallelize creation of a Materiazed View

Question

I',m tryng to create a Materialized View referencing a remote table with over 8 million records. I put the clausole "PARALLEL 8" in DDL MVIEW statement and the hint "/*+ PARALLEL(8) */ in the MVIEW QUERY.

When creating, no parallel slave process was activated for the process.

What's the matter ?

Thanks in advice.

These are DOP parameter of the target server:

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     80
parallel_min_percent                 integer     0
parallel_min_servers                 integer     8
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     32
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

I tried setting the parameter "parallel_degree_policy" from MANUAL to AUTO, and altering session (before launching the MVIEW creation statement") with:

ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

...but with the same result..

Here's the MVIEW DDL:

CREATE MATERIALIZED VIEW MYDB.MYTABLE(FIELD1,FIELD4,FIELD3,FIELD4,FIELD5)
TABLESPACE MY_TBS
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
               INITIAL          1M
               NEXT             30M
       MINEXTENTS       1
       MAXEXTENTS       UNLIMITED
       PCTINCREASE      0
       BUFFER_POOL      DEFAULT
       )
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL 8
BUILD IMMEDIATE
USING INDEX
      TABLESPACE MY_TBS
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
          INITIAL          1M
          NEXT             50M
              MINEXTENTS       1
          MAXEXTENTS       UNLIMITED
          PCTINCREASE      0
          BUFFER_POOL      DEFAULT
          FLASH_CACHE      DEFAULT
          CELL_FLASH_CACHE DEFAULT
      )
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS 
SELECT
       /*+ PARALLEL(8) */
       "MYTABLE"."FIELD1",
       "MYTABLE"."FIELD2",
       "MYTABLE"."FIELD3",
       "MYTABLE"."FIELD4",
       "MYTABLE"."FIELD5" 
FROM
       "MYTABLE"@"MYDBLINK" "MYTABLE";

score 1 · Answer 1 · answered Jun 14 '19 at 13:39

You put the parallel on mview DDL and on mview query, maybe you hitting some bug

Parallel DML Not Used For Mview Refresh (Doc ID 2529168.1)

Check the MOS notes, and do some solutions.

Other approach is to alter the master table do degree>1, and then your refresh will be in parallel.

"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, doesn't matter."

How to Refresh a Materialized View in Parallel (Doc ID 577870.1)

I would first, trying the second approach, and alter the degree of your master table to do a test. If not resolve your issue, check the MOS note

Can't parallelize creation of a Materiazed View

1 Answers1