1

Topic: Materialized view fast refresh - the strange behavior in 12c

All greetings;
Faced the strange behavior - at fast refresh on materialized view the basis behind coulisses checks all m.view logs, even what do not participate in refresh. Even on what are not present any m.view.
There are two tables:
1) table1 - on it it is constructed m.view mv_table1 with a sight on fast refresh
2) table2 - for it simply we create m.view log
Initially fast refresh for table1 works quickly.
But if in other session to launch change of great volume of the data in table2 in one transaction it influences on fast refresh table1 - the main time leaves on scanning m.view log for table2

select 1 from "SCOTT". "MLOG $ _ TABLE2" where rownum=1
bases wrote:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

1. session 1 . We create tables, m.view logs, m.view

drop materialized view mv_table1;
drop table table1 purge;
drop table table2 purge;
create table table1 (
an integer primary key;
b date;
c varchar2 (16)
);
create table table2 (
an integer primary key;
b date;
c varchar2 (16)
);
create materialized view log on table1
with rowid, sequence, commit scn (b, c) including new values;
create materialized view log on table2
with rowid, sequence, commit scn (b, c) including new values;
create materialized view mv_table1 (b, c, cnt)
refresh fast on demand
as
select b, c, count (*) cnt
from table1
group by b, c;

2. session 1 . We check speed fast refresh mv_table1 :

SQL> set timing on
SQL> exec dbms_mview.refresh (' MV_TABLE1 ', ' F ');
PL/SQL procedure successfully completed
Executed in 0,25 seconds

3. session 2 . We load great volume of new records in table2 , 3 million, not

insert into table2 (a, b, c)
with get_data (a, b, c, i) as (
select 1 a, trunc (sysdate) b, to_char (sysdate, ' yyyy-mm-dd ') || ' - ' || 1 c, 2 i from dual
union all
select i, trunc (sysdate) +mod (i, 10000), to_char (sysdate+mod (i, 10000), ' yyyy-mm-dd ') || ' - ' || mod (i, 10000), i + 1
from get_data
where i <= 3000000
)
select a, b, c
from get_data;

4. session 1 . We check speed of operation fast refresh for mv_table1

SQL> set timing on
SQL> exec dbms_mview.refresh (' MV_TABLE1 ', ' F ');
PL/SQL procedure successfully completed
Executed in 5,367 seconds

In v$session it is visible that the main time leaves on request on m.view log for table2

select 1 from "SCOTT". "MLOG $ _ TABLE2" where rownum=1

Tried to look for bugs (which much for m.views), anything sensible did not find. Unless  here it (did not help):
[quote =] Bug 22568177 - FAST MATERIALIZED VIEW REFRESH SLOWER IN 12C THAN 11.2.0.3 (Doc ID 22568177.8)

Question.
Somebody faced the similar? Than it is treated? What crutches it is possible to try?