物化视图性能测试

create table t_ta_textparameter nologging as select * from ta_textparameter;
select count(1) from t_ta_textparameter;

create materialized view log on t_ta_textparameter;
create materialized view mv_t_textparamter_nopara
refresh on demand fast
as
select * from t_ta_textparameter;

declare
cursor t_ta_textparameter_cur is select * from t_ta_textparameter where rownum<100000;
begin
for rec in t_ta_textparameter_cur loop
update t_ta_textparameter f set f.c_paramitem = f.c_paramitem || '-' where f.l_rowid = rec.l_rowid;   --物化视图是记录级别的，本身和一个大事务或者N个小事务无关。
commit;
end loop;
end;
--19.352 秒
begin
dbms_mview.refresh(list => 'mv_t_textparamter_nopara');
end;
--7.072 秒10w, 59秒100w
alter session enable parallel dml;
drop materialized view mv_t_textparamter_para;
create materialized view mv_t_textparamter_para
refresh on demand fast
as
select /*+ parallel(a 4) */* from t_ta_textparameter a;

begin
dbms_mview.refresh(list => 'mv_t_textparamter_para');
end;
--4.5 秒 10w, 50秒100w

select * from dba_segments where segment_name like 'MLOG$_T_TA_TEXTPARAMETER'; --1024块
update t_ta_textparameter f set f.c_paramitem = f.c_paramitem || '-' where rownum<1000000; -- 两个物化视图 129秒, 一个132，没有物化视图18秒
commit;
begin
dbms_mview.refresh(list => 'mv_t_textparamter_nopara');
end;
drop materialized view mv_t_textparamter_nopara;
drop materialized view mv_t_textparamter_para;
drop materialized view log on t_ta_textparameter;
--7.038 秒
select * from v$pq_sesstat;
select * from v$px_process;

select * from v$active_session_history;