Theory and test of oracle materialized view

Theory and test of oracle materialized view

1. Materialized view

(1) Basic concepts of oracle materialized view

 Materialized views first need to create materialized view logs, oracle Create a materialized view log table based on the materialized view log created by the user. The name of the materialized view log table is mlog$_Followed by the name of the base table. If the length of the table name exceeds 20 digits, only the first 20 digits will be taken. When the name is repeated after truncation, oracle The number will be automatically added after the materialized view log name as the sequence number.
 There are several options for creating materialized view logs: you can specify as rowid,primary key,object id ,sequence Or specify the column name explicitly.
 The resulting materialized view logs have different structures.
 Columns that will be included in any materialized view:
  (1)snaptime$$: Used to indicate the refresh time. When the base table occurs dml During operation, it will be recorded in the materialized view log. At this time, the specified time is 0:0:0 on January 1, 4000(Materialized view not refreshed). 
  (2)dmltype$$: Used to represent dml Operation type, i express insert,d express delete,u express update. 
  (3)old_new$$: Used to indicate whether this value is a new value or an old value. n(ew)Represents a new value, o(ld)Represents the old value, u express update Operation.
  (4)change_vector$$Represents the modification vector, which is used to indicate which field or fields are modified.

 If with Followed by primary key,The materialized view log will contain the primary key column.
 If with Followed by rowid,The materialized view log will contain: m_row$$: Used to store changed records rowid. 
 If with Followed by object id,The materialized view log will contain: sys_nc_oid$: The object used to record each change object id. 
 If with Followed by sequence,The materialized view will include: sequence$$: One for each operation sequence Number, so as to ensure that the refresh is carried out in order.

Application scenario of with primary key and with rowid:

with primary key: it is mainly used to establish a one-to-one replication relationship between the master table and the remote library MV in a distributed environment. The master table must have a primary key (the primary key must be enabled). The select statement defining the MV must contain all the fields constituting the primary key. On this basis, it can also contain other non primary key fields, The only limitation is that aggregate functions cannot be used on these fields; The primary key can also be used when creating the MV log corresponding to the MV that implements statistical summary in the DSS environment, but it is not necessary. If it is used, the fields contained in the primary key can no longer be repeatedly defined in with (col1,col2...),

With rowid: in DSS environment, when the MV Select statement contains an aggregate function, rowid must be used. In addition, including new values must be used, and all fields in the select list must be defined in with (col1,col2...). If with primary key is used at the same time, it should be noted that the fields contained in the primary key cannot be defined in with (col1,col2...). In the distributed environment, with rowid is mainly used when the master table does not have a primary key. Because it implements one-to-one replication between MV and master table, there is no need to define with (col1,col2...)

This test mainly tests with primary key and with rowid, without involving object id, sequence or explicitly specifying column name.

(2) There are two refresh modes for Materialized Views:

Whether the refresh mode is on demand or on commit at the time of creation.

  • on demand refresh: refresh only when the materialized view "needs" to be refreshed, that is, update the materialized view to ensure consistency with the base table data;

  • on commit is triggered. Once the base table has a commit, that is, transaction commit, it will be refreshed immediately and the materialized view will be updated immediately to make the data consistent with the base table. Generally, this method is slow when operating the base table.
    If it is not specified when creating materialized views, Oracle creates them in on demand mode by default.

(3) There are three refresh methods for Materialized Views:

  • COMPLETE refresh: all records in the table will be deleted (TRUNCATE may be used in case of single table refresh), and then the materialized view will be regenerated according to the definition of query statements in the materialized view.

  • FAST refresh: the incremental refresh mechanism is adopted to refresh all operations on the base table since the last refresh to the materialized view.

    The fast refresh mechanism of Oracle materialized view is completed through materialized view log. Oracle can also support the rapid refresh of multiple materialized views through a materialized view log. Materialized view logs can be created as ROWID or PRIMARY KEY according to the needs of rapid refresh of different materialized views. You can also choose whether to include SEQUENCE, INCLUDING NEW VALUES, and a list of specified columns.

    FAST must create a view log based on the master table. For the incremental refresh option, if there is an analysis function in the subquery, the materialized view does not work.

  • FORCE refresh: This is the default data refresh method. Oracle will automatically judge whether the conditions for fast refresh are met. If yes, fast refresh will be performed, otherwise full refresh will be performed.

(4) Materialized views can be created in two ways:

Build methods: including BUILD IMMEDIATE and BUILD DEFERRED.

BUILD IMMEDIATE generates data when creating materialized views.

BUILD DEFERRED does not generate data at the time of creation, but later generates data as needed. The default is BUILD IMMEDIATE.

(5) Materialized view has two query rewriting modes:

Query rewrite: includes ENABLE QUERY REWRITE and DISABLE QUERY REWRITE.

Indicate whether the created materialized view supports query rewriting.

Query rewriting means that when querying the base table of the materialized view, Oracle will automatically judge whether the results can be obtained by querying the materialized view. If so, aggregation or join operations are avoided and data is directly read from the calculated materialized view. The default is DISABLEQUERY REWRITE.

(6) Related dynamic performance views

The most useful is user_mviews user_mview_logs user_mview_refresh_times

select * from user_mview_aggregates;
select * from user_mview_analysis;
select * from user_mview_comments;
select * from user_mview_detail_partition;
select * from user_mview_detail_relations;
select * from user_mview_detail_subpartition;
select * from user_mview_joins;
select * from user_mview_keys;
select * from user_mview_logs;
select * from user_mview_refresh_times;
select * from user_mviews;

(7) Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

2. Materialized view practice test

(0) test environment

mv users have access to TB_PRIMARY and TM_ROWID permissions

grant connect to mv;
grant resource to mv;
grant create materialized view to mv;
grant global query rewrite to mv;
grant select,insert,update on tm_rowid to mv;
grant select,insert,update on tb_primary to mv;

The actual application scenario is usually accessed through the database link to refresh the synchronization data between different databases.

TEST user (source data)MV user (target data)Test content
TB_PRIMARYMaterialized view of primary keycomplete refresh
TM_ROWIDrowid materialized viewfast/force refresh

Delete materialized view log and materialized view.

   DROP MATERIALIZED VIEW LOG ON TB_PRIMARY;

   DROP MATERIALIZED VIEW MV_TB_PRIMARY;

Prepare data in TB_ Create PK from primary table.

SQL> CREATE TABLE TB_PRIMARY AS SELECT * FROM TB_NORMAL SAMPLE(0.1);

Table created

SQL> ALTER TABLE TB_PRIMARY ADD CONSTRAINT PK_TB_PRIMARY PRIMARY KEY (ORDER_ID);

Table altered

SQL> CREATE TABLE TB_ROWID AS SELECT * FROM TB_NORMAL SAMPLE(0.1);

Table created

(1) Create materialized view with primary key

First, create a materialized view log on the source table under the test user and specify it as the primary key type

CREATE MATERIALIZED VIEW LOG ON TB_PRIMARY  
TABLESPACE TB_TEST          
WITH PRIMARY KEY; 

Execute under the mv user and establish the materialized view of the tm table under the test user.

CREATE MATERIALIZED VIEW MV_TB_PRIMARY
TABLESPACE TB_TEST
BUILD DEFERRED
REFRESH FAST
   ON DEMAND
   START WITH SYSDATE		
   NEXT SYSDATE+1/24/60
WITH PRIMARY KEY
DISABLE QUERY REWRITE AS
SELECT *
FROM TEST.TB_PRIMARY;

  • BUILD DEFERRED -- do not refresh immediately when creating materialized views
  • REFRESH FAST – fast refresh mode. Materialized view logs are required
  • ON DEMAND – refresh ON DEMAND
  • START WITH SYSDATE -- effective immediately
  • NEXT SYSDATE+1/24/60 -- next refresh interval 1 minute
  • WITH PRIMARY KEY -- materialize the view by primary key
  • DISABLE QUERY REWRITE AS - materialized view data cannot be updated
  • SELECT * FROM TEST.TB_PRIMARY; -- Select data source table

Perform refresh:

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TB_PRIMARY',METHOD => 'F');

If the first incremental refresh is unsuccessful, the prompt must be refreshed in full

SQL> EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TB_PRIMARY',METHOD => 'F');  
begin DBMS_MVIEW.REFRESH(LIST => 'MV_TB_PRIMARY',METHOD => 'F'); end;

ORA-12057: materialized view "MV"."MV_TB_PRIMARY" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

SQL> EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TB_PRIMARY',METHOD => 'C');

PL/SQL procedure successfully completed


SQL> SELECT COUNT(*) FROM MV_TB_PRIMARY;

  COUNT(*)
----------
      2082


Insert multiple pieces of data.

INSERT INTO  TB_PRIMARY
        (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
      VALUES
        (DBMS_RANDOM.STRING('A', 16),
         SYSDATE,
         DBMS_RANDOM.STRING('U', 16),
         'SHANGHAI',
         ROUND(DBMS_RANDOM.VALUE(1, 10000)));

Check the log information. There are 5 records. DMLTYPE is I and the insert type changes. OLD_NEW is N, new record

SQL> SELECT * FROM MLOG$_TB_PRIMARY;

ORDER_ID                       SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$                                                                       XID$$
------------------------------ ----------- --------- --------- -------------------------------------------------------------------------------- ----------
puAKAbVXzFctUnta               2021-10-18  I         N         FE                                                                               1.12598151
gLExORrondhHgqmd               2021-10-18  I         N         FE                                                                               1.12598151
xSBLRmMdgCogYkYb               2021-10-18  I         N         FE                                                                               1.12598151
JrIYqqEDHBkgegYD               2021-10-18  I         N         FE                                                                               1.12598151
oxbPgKsrucmSBZiM               2021-10-18  I         N         FE                                                                               1.12598151

Perform incremental refresh again and add 5 records.

SQL> EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TB_PRIMARY',METHOD => 'F');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) FROM MV_TB_PRIMARY;

  COUNT(*)
----------
      2087

(2) Create materialized view with rowid

SQL> CREATE MATERIALIZED VIEW LOG ON TM_ROWID TABLESPACE TB_TEST  WITH ROWID;

Materialized view log created

CREATE MATERIALIZED VIEW MV_TM_ROWID
TABLESPACE TB_TEST  
BUILD IMMEDIATE
REFRESH FAST
   START WITH SYSDATE		
   NEXT SYSDATE+1/24/60
WITH ROWID
DISABLE QUERY REWRITE AS
SELECT *
FROM TEST.TM_ROWID;

If you find that the on commit parameter is used, an error will be reported and you will be prompted that you do not have permission.???

CREATE MATERIALIZED VIEW MV_TM_ROWID
TABLESPACE TB_TEST
BUILD IMMEDIATE
REFRESH FAST
   ON COMMIT
WITH ROWID
AS
SELECT *
FROM TEST.TM_ROWID

ORA-01031: insufficient privileges

Cannot access through data link. Create materialized view log!

Think about it, it's reasonable.

SQL> CREATE MATERIALIZED VIEW LOG ON SYS_ORG@SJCK TABLESPACE SJCK WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON SYS_ORG@SJCK TABLESPACE SJCK WITH ROWID

ORA-00949: illegal reference to remote database
--complete refresh   
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TM',METHOD => 'c');  
EXEC DBMS_MVIEW.REFRESH('MV_TM','C');  
   
--Incremental refresh  
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TM',METHOD => 'f');  
EXEC DBMS_MVIEW.REFRESH('MV_TM','F');  

Add data script:

DECLARE 
i  NUMBER;
BEGIN
i:=0;
WHILE i<8 LOOP
    INSERT INTO  TB_PRIMARY
            (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
          VALUES
            (DBMS_RANDOM.STRING('A', 16),
             SYSDATE,
             DBMS_RANDOM.STRING('U', 16),
             'GUIYANG',
             ROUND(DBMS_RANDOM.VALUE(1, 10000)));
  	i :=i+1;
END LOOP;
COMMIT;

END;
/

--------------------------------------------------------
DECLARE 
i  NUMBER;
BEGIN
i:=0;
WHILE i<8 LOOP

    INSERT INTO  TM_ROWID
            (ORDER_ID, ORDER_DATE, CONTRACT_ID, CITY, ORDER_NUM)
          VALUES
            (DBMS_RANDOM.STRING('A', 16),
             SYSDATE,
             DBMS_RANDOM.STRING('U', 16),
             'SHANGHAI',
             ROUND(DBMS_RANDOM.VALUE(1, 10000)));
     i :=i+1;
END LOOP;
COMMIT;

END;
/

(3) Materialized view refresh group

The view materialized view is in the refresh group by default. There are two views, user_refresh_children ,user_refresh .

select * from user_refresh_children;

select ROWNER, RNAME, REFGROUP from user_refresh;

Delete the following four materialized views from the refresh group and use dbms_refresh.subtract method:

MV_TB_PRIMARY MV_TB_PRIMARY_SHANGHAI MV_TB_PRIMARY_GUIYANG MV_TB_PRIMARY_HEFEI

exec  dbms_refresh.subtract(name => 'MV_TB_PRIMARY',list => 'MV_TB_PRIMARY');

exec  dbms_refresh.subtract(name => 'MV_TB_PRIMARY_SHANGHAI',list => 'MV_TB_PRIMARY_SHANGHAI');

exec  dbms_refresh.subtract(name => 'MV_TB_PRIMARY_GUIYANG',list => 'MV_TB_PRIMARY_GUIYANG');

exec  dbms_refresh.subtract(name => 'MV_TB_PRIMARY_HEFEI',list => 'MV_TB_PRIMARY_HEFEI');
commit; 

Using dbms_refresh.make method to create a new refresh group. When creating a refresh group, you can not specify the materialized view in the group, but next_date and interval need to be specified and can be empty. As follows:

exec  dbms_refresh.make(name => 'GROUP_TB_PRIMARY',list => '',next_date => NULL,interval =>  NULL);
commit;

SQL> select ROWNER, RNAME, REFGROUP from user_refresh;

ROWNER                         RNAME                            REFGROUP
------------------------------ ------------------------------ ----------
MV                             MV_TM_ROWID                            61
MV                             MV_TM_ROWID_HEFEI                      84
MV                             GROUP_TB_PRIMARY                      101

Executed in 0.05 seconds

You can see GROUP_TB_PRIMARY group, add the materialized view to be refreshed to the refresh group, and use dbms_refresh.add method.

Note: the list is separated by commas to materialize the view list

exec  dbms_refresh.add(name => 'GROUP_TB_PRIMARY',list => 'MV_TB_PRIMARY,MV_TB_PRIMARY_SHANGHAI,MV_TB_PRIMARY_GUIYANG,MV_TB_PRIMARY_HEFEI');
 
SQL> select NAME,RNAME,REFGROUP from user_refresh_children where rname='GROUP_TB_PRIMARY';

NAME                           RNAME                            REFGROUP
------------------------------ ------------------------------ ----------
MV_TB_PRIMARY                  GROUP_TB_PRIMARY                      101
MV_TB_PRIMARY_SHANGHAI         GROUP_TB_PRIMARY                      101
MV_TB_PRIMARY_GUIYANG          GROUP_TB_PRIMARY                      101
MV_TB_PRIMARY_HEFEI            GROUP_TB_PRIMARY                      101

Executed in 0.057 seconds

You can see that all four materialized views are in group_ TB_ In the primary group.

The refresh operation is greatly simplified by refreshing the four materialized views included in the refresh group at one time.

Similar materialized views can be packaged into groups and refreshed at one time, which is much more convenient.

SQL> exec  dbms_refresh.refresh(name => 'GROUP_TB_PRIMARY');

PL/SQL procedure successfully completed


Executed in 0.08 seconds

(4) Cannot create materialized view logs on synonyms

The reason is that the synonym is not a physical table

SQL> CREATE MATERIALIZED VIEW LOG ON syn_tc  WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON syn_tc  WITH ROWID

ORA-00942: table or view does not exist

(5) Create materialized view log on partition table

Create materialized view log under test user:

SQL>  CREATE MATERIALIZED VIEW LOG ON TB_PART_RANGE TABLESPACE TB_TEST  WITH ROWID;

Materialized view log created

Create a materialized view of the partition table under mv user:

SQL> CREATE MATERIALIZED VIEW MV_TB_PART_RANGE
  2  TABLESPACE TB_TEST
  3  BUILD IMMEDIATE
  4  REFRESH FAST
  5     START WITH SYSDATE
  6     NEXT SYSDATE+1/24/60
  7  WITH ROWID
  8  DISABLE QUERY REWRITE AS
  9  SELECT *
 10  FROM TEST.TB_PART_RANGE;

Materialized view created
SQL> select * from user_part_tables;

The result is the same as the ordinary table.

(6) Materialized view partition table

Materialized views also support partitioned tables.

Authorize mv user under test user

grant select,insert,update on TB_PART_RANGE to mv;
SQL> create materialized view MV_TB_PART_RANGE_TEST
  2      partition by range(order_date)
  3          interval(numtoyminterval(1, 'YEAR'))
  4          (
  5          PARTITION P2015 VALUES LESS THAN (TO_DATE('20150101', 'YYYYMMDD'))
  6          )
  7      tablespace TB_TEST
  8      build immediate refresh FAST
  9      WITH ROWID
 10      on demand
 11      enable query rewrite
 12      as SELECT * from TEST.TB_PART_RANGE;

Materialized view created


Executed in 6.846 seconds

P2015 partitions are defined. Other SYS partitions are automatically created annually. Similarly, indexes on partitions are the same partition tables.

SQL> select TABLE_NAME, PARTITION_NAME,HIGH_VALUE from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
MV_TB_PART_RANGE_TEST          SYS_P161                       TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          SYS_P162                       TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          SYS_P163                       TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          SYS_P164                       TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          SYS_P165                       TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          SYS_P166                       TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
MV_TB_PART_RANGE_TEST          P2015                          TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected

mv users can DBL through the data link_ Orc create materialized view:

create materialized view MV_TB_PART_RANGE_TEST
    partition by range(order_date)
        interval(numtoyminterval(1, 'YEAR'))
        (
        PARTITION P2015 VALUES LESS THAN (TO_DATE('20150101', 'YYYYMMDD'))
        ) 
    tablespace TB_TEST
    build immediate refresh FAST
    WITH ROWID
    on demand
    enable query rewrite
    as SELECT * from TB_PART_RANGE@dbl_orc;

Finally, you want to create a partitioned table first, and then rename the materialized view. As a result, you can't create materialized views on existing tables, prompting that the object exists.

This method is not feasible!

SQL> create table MV_TB_PART_RANGE as select * from test.TB_PART_RANGE where 1<>1;

Table created


SQL> CREATE MATERIALIZED VIEW MV_TB_PART_RANGE
  2  TABLESPACE TB_TEST
  3  BUILD IMMEDIATE
  4  REFRESH FAST
  5     START WITH SYSDATE
  6     NEXT SYSDATE+1/24/60
  7  WITH ROWID
  8  DISABLE QUERY REWRITE AS
  9  SELECT *
 10  FROM TEST.TB_PART_RANGE;
CREATE MATERIALIZED VIEW MV_TB_PART_RANGE
TABLESPACE TB_TEST
BUILD IMMEDIATE
REFRESH FAST
   START WITH SYSDATE
   NEXT SYSDATE+1/24/60
WITH ROWID
DISABLE QUERY REWRITE AS
SELECT *
FROM TEST.TB_PART_RANGE

ORA-00955: name is already used by an existing object

(7) Parallel refresh

In DBMS_ The refresh process in Mview can use the feature of parallel refresh, which can theoretically reduce the time of complete refresh.

Test and compare the effects of non parallel and parallel refresh. The materialized view does not refresh the data first.

In order to test the accuracy, after each full refresh, the materialized view is deleted and reconstructed to ensure consistency.

--Delete materialized view
DROP MATERIALIZED VIEW MV_TB_PART_RANGE;
--Create materialized view
CREATE MATERIALIZED VIEW MV_TB_PART_RANGE
TABLESPACE TB_TEST
BUILD DEFERRED
REFRESH FAST
   ON DEMAND
   START WITH SYSDATE		
   NEXT SYSDATE+1/24/60
WITH ROWID 
DISABLE QUERY REWRITE AS
SELECT *
FROM TEST.TB_PART_RANGE;

Test the effect of parallelism of 4 and 2:

SQL>  exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C',PARALLELISM=>4);

PL/SQL procedure successfully completed


Executed in 56.893 seconds

SQL>  exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C',PARALLELISM=>2);

PL/SQL procedure successfully completed


Executed in 12.383 seconds

As a result, the speed of parallelism 2 is much faster than that of parallelism 4.

SQL> exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C');

PL/SQL procedure successfully completed


Executed in 9.454 seconds


As a result, the normal refresh speed is faster, faster than parallelism 2. The amount of data is not large.

SQL> select count(*) from MV_TB_PART_RANGE;

  COUNT(*)
----------
   2103000

Executed in 0.388 seconds

Inconsistent with the theory!!!

Re force the parallel parameters of the session:

SQL> show parameter parallel
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
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     40
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0


SQL> Alter session force parallel DML;

Session altered

Then test the effects of parallelism 2 and 4 respectively:

SQL> exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C',PARALLELISM=>2);

PL/SQL procedure successfully completed


Executed in 7.928 seconds


SQL>  exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C',PARALLELISM=>4);

PL/SQL procedure successfully completed


Executed in 12.959 seconds

The parallelism degree 2 is greater than that without parallel parameters, and the effect is normal, which is consistent with the theory.

Parallelism 4 is less than that without parallel parameters. Maybe if the amount of data is larger and the number of CPU s is considered, the effect can be reflected.

Tags: Database Oracle Optimize DBA

Posted on Thu, 21 Oct 2021 00:28:17 -0400 by theflea912