oracle物化视图理论与测试
1、物化视图
(1)oracle物化视图基本概念
物化视图首先需要创建物化视图日志, oracle依据用户创建的物化视图日志来创建物化视图日志表,物化视图日志表的名称为mlog$_后面跟基表的名称,如果表名的长度超过20位,则只取前20位,当截短后出现名称重复时,oracle会自动在物化视图日志名称后面加上数字作为序号。
创建物化视图日志在建立时有多种选项:可以指定为rowid、primary key、object id 、sequence或明确指定列名。
产生的物化视图日志的结构都不相同。
任何物化视图都会包括的列:
(1)snaptime:用于表示刷新时间。当基本表发生dml操作时,会记录到物化视图日志中,这时指定的时间4000年1月1日0时0分0秒(物化视图未被刷新)。
(2)dmltype:用于表示dml操作类型,i表示insert,d表示delete,u表示update。
(3)old_new:用于表示这个值是新值还是旧值。n(ew)表示新值,o(ld)表示旧值,u表示update操作。
(4)change_vector表示修改矢量,用来表示被修改的是哪个或哪几个字段。
如果with后面跟了primary key,则物化视图日志中会包含主键列。
如果with后面跟了rowid,则物化视图日志中会包含: m_row:用来存储发生变化的记录的rowid。
如果with后面跟了object id,则物化视图日志中会包含:sys_nc_oid$:用来记录每个变化对象的对象id。
如果with后面跟了sequence,则物化视图日子中会包含:sequence:给每个操作一个sequence号,从而保证刷新时按照顺序进行刷新。
with primary key 和 with rowid 应用场景:
with primary key:主要用在分布式环境实现master table与远程库MV建立一对一的复制关系,master table上必须有主键(主键必须是enabled的),定义MV的select语句中必须包含构成主键的所有字段,在此基础上也可以包含其它非主键字段,唯一的限制是这些字段上不能使用聚合函数;DSS环境下实现统计汇总的MV对应的MV log创建时也可以使用primary key,但不是一定要使用,如果用了那么primary key所包含的字段就不能再重复定义于with (col1,col2…)里了,
with rowid:DSS环境下当MV的Select语句里带有聚合函数,必须使用rowid,此外还必须用上including new values,且要将select list里的所有字段都定义在with (col1,col2…)里,如果同时使用了with primary key那么要注意primary key所包含的字段就不能再重复定义于with (col1,col2…)里。分布式环境下with rowid主要用于master table没有主键的情况,因为实现的是MV和master table间一对一的复制,所以无需定义with (col1,col2…)
本次主要测试with primary key 和 with rowid,未涉及object id 、sequence或明确指定列名 。
(2)物化视图二种刷新模式:
在创建时refresh mode是 on demand 还是 on commit。
-
on demand 按需刷新,仅在该物化视图“需要”被刷新,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;
-
on commit 提交触发,一旦基表有了commit,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
创建物化视图时未作指定,则Oracle默认按 on demand 模式来创建。
(3)物化视图三种刷新方法:
-
完全刷新(COMPLETE): 会删除表中所有的记录(如果是单表刷新,可能会采用TRUNCATE的方式),然后根据物化视图中查询语句的定义重新生成物化视图。
-
快速刷新(FAST): 采用增量刷新的机制,只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。
Oracle物化视图的快速刷新机制是通过 物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
FAST必须创建基于主表的视图日志。对于增量刷新选项,如果在子查询中存在分析函数,则物化视图不起作用。
-
强制刷新(FORCE):这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件,如果满足则进行快速刷新,否则进行完全刷新。
(4)物化视图两种创建方式:
创建方式(BuildMethods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。
BUILD IMMEDIATE是在创建物化视图的时候就生成数据。
BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。
(5)物化视图两种查询重写模式:
查询重写(QueryRewrite):包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
分别指出创建的物化视图是否支持查询重写。
查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。
(6)相关动态性能视图
最有用的是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)版本
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、物化视图实践测试
(0)测试环境
mv用户有访问TB_PRIMARY和TM_ROWID权限
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;
实际应用场景,通常是通过数据库链路访问,在不同数据库之间刷新同步数据。
TEST用户(源数据) | MV用户(目标数据) | 测试内容 |
---|---|---|
TB_PRIMARY | 主键物化视图 | complete刷新 |
TM_ROWID | rowid物化视图 | fast/force刷新 |
删除物化视图日志 和 物化视图。
DROP MATERIALIZED VIEW LOG ON TB_PRIMARY;
DROP MATERIALIZED VIEW MV_TB_PRIMARY;
准备数据,在TB_PRIMARY表创建PK 。
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)以主键建立物化视图
先在test用户下源表上建立物化视图日志,指定为主键类型
CREATE MATERIALIZED VIEW LOG ON TB_PRIMARY
TABLESPACE TB_TEST
WITH PRIMARY KEY;
在mv用户下执行,建立在test用户下tm表的物化视图。
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 --建立物化视图时不立刻刷新
- REFRESH FAST – 快速刷新模式,必须有物化视图日志
- ON DEMAND – 按需刷新
- START WITH SYSDATE --立刻生效
- NEXT SYSDATE+1/24/60 --下次刷新时间间隔1分钟
- WITH PRIMARY KEY --按主键物化视图
- DISABLE QUERY REWRITE AS -不能更新物化视图数据
- SELECT * FROM TEST.TB_PRIMARY; --选择数据源表
执行刷新:
EXEC DBMS_MVIEW.REFRESH(LIST => ‘MV_TB_PRIMARY’,METHOD => ‘F’);
第一次增量刷新,不成功,提示必须全量刷新
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 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)));
查看日志信息,有5条记录,DMLTYPE是I ,insert类型变化。OLD_NEW是N,新增记录
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
再次执行增量刷新,增加5条记录。
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
查询刷新后的状态
SQL> select t.mview_name ,t.LAST_REFRESH_TYPE, t.LAST_REFRESH_DATE from user_mviews t;
MVIEW_NAME LAST_REFRESH_TYPE LAST_REFRESH_DATE
------------------------------ ----------------- -----------------
MV_TB_PRIMARY NA
MV_TB_PRIMARY_BEIJING FAST 2021-10-22 18:10:
MV_TB_PRIMARY_SHANGHAI COMPLETE 2021-10-22 16:57:
三个视图的刷新情况:
MV_TB_PRIMARY :从未刷新过数据;
MV_TB_PRIMARY_BEIJING:已经完成全量刷新,在快速刷新状态,按创建时设置:NEXT SYSDATE+1/24/60 ,每分钟都要更新一次;
MV_TB_PRIMARY_SHANGHAI:完成全量更新,没有快速刷新过的状态。
(2)以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;
发现使用on commit 参数,就报错,提示没有权限。
有人用过此方式,告知我,感谢!
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
不能通过数据链路访问,建立物化视图日志!
思考一下,也合理。
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
--完全刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TM',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_TM','C');
--快速刷新,增量刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_TM',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_TM','F');
增加数据脚本:
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)物化视图刷新组
查看物化视图默认在刷新组,两个视图,user_refresh_children 、user_refresh 。
select * from user_refresh_children;
select ROWNER, RNAME, REFGROUP from user_refresh;
将以下四个物化视图从刷新组里删除,使用dbms_refresh.subtract方法:
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;
用dbms_refresh.make方法,创建新的刷新组,创建刷新组时可以不指定组内的物化视图,但是next_date和 interval 需要指定,可以为空。如下:
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
可以看到GROUP_TB_PRIMARY组,把需要刷新的物化视图加入到刷新组中,用dbms_refresh.add方法。
注意: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
可以看到四个物化视图都在GROUP_TB_PRIMARY组中。
通过刷新组一次刷新包括的四个物化视图,大大简化刷新操作。
可以将相类似的物化视图打包成组,一次刷新,方便了不少。
SQL> exec dbms_refresh.refresh(name => 'GROUP_TB_PRIMARY');
PL/SQL procedure successfully completed
Executed in 0.08 seconds
(4)不能在同义词上建物化视图日志
原因是同义词不是物理表
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)分区表上建物化视图日志
在test用户下建物化视图日志:
SQL> CREATE MATERIALIZED VIEW LOG ON TB_PART_RANGE TABLESPACE TB_TEST WITH ROWID;
Materialized view log created
在mv用户下对分区表建物化视图:
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;
结果和普通表一样。
(6)物化视图分区表
物化视图也支持分区表。
在test用户下授权给mv用户
grant select,insert,update on TB_PART_RANGE to mv;
在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分区是定义的,其他SYS分区都是自动按年创建的,同理分区上的索引也就是同分区表。
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用户可以通过数据链路dbl_orc建立物化视图:
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;
最后想先创建一个分区表,然后物化视图重名,结果是不能在已有表上建物化视图,提示对象存在。
此方法不可行!
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)并行刷新
在dbms_mview中的刷新过程,可以使用并行刷新的特性,理论上可以减少完全刷新的时间。
测试对比非并行和并行刷新的效果,建物化视图先不刷新数据。
为了测试的准确性,每次全量刷新后,都删除物化视图,重建,保证一致性。
--删除物化视图
DROP MATERIALIZED VIEW MV_TB_PART_RANGE;
--建立物化视图
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;
测试并行度为4 和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
结果是并行度2的速度要快,远远超过并行度4的速度。
SQL> exec dbms_mview.refresh(LIST =>'MV_TB_PART_RANGE',METHOD=>'C');
PL/SQL procedure successfully completed
Executed in 9.454 seconds
结果是正常刷新速度更快,比并行度2的速度要快。数据量不算大。
SQL> select count(*) from MV_TB_PART_RANGE;
COUNT(*)
----------
2103000
Executed in 0.388 seconds
与理论不符!!!
重新强制设置会话的并行参数:
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
然后再分别测试并行度2和4的效果:
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
并行度2大于没有并行参数,效果正常,与理论相符。
并行度4小于没有并行参数,可能如果数据量更大,还有考虑CPU的数量,才能体现出效果。