I've faced unexpected behavior of materialized view fast refresh. It turns to be that at a background database queries unrelated m.view logs.
Imagine we have two tables with m.view logs and one m.view for one of the tables:
- table1 - used in m.view and fast refreshable
- table2 - no m.view, just m.view log (no m.view for simplification, in our project we have several independent mat.views)
Initially fast refresh for table1 works fast. But if we fill table2 in another session with some big amount of data in one transaction, then fast refresh of m.view for table1 degradates. Most of the time is spent on select over m.view log for table2, which has no relations to refreshing m.view.
My database version:
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 - Create tables/m.view logs/m.views:
drop materialized view mv_table1;
drop table table1 purge;
drop table table2 purge;
create table table1(
a integer primary key,
b date,
c varchar2(16)
);
create table table2(
a 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. Sesion 1 - check the performance of fast refresh:
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 - upload 3 mio of records into table2, don't commit
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 - check the performance of fast refresh:
SQL> set timing on
SQL> exec dbms_mview.refresh('MV_TABLE1','F');
PL/SQL procedure successfully completed
Executed in 5,367 seconds
In a v$session we see that Session 1 spends most of time for selecting from m.view log for table2:
select 1 from "SCOTT"."MLOG$_TABLE2" where rownum=1
This is information from the trace file (separate run):
SQL ID: 89s2c53j480vz Plan Hash: 1479141652
select 1
from
"SCOTT"."MLOG$_TABLE2" where rownum=1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 5.88 5.89 0 3058867 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 5.88 5.89 0 3058871 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 COUNT STOPKEY (cr=3058867 pr=0 pw=0 time=5891187 us)
0 0 0 TABLE ACCESS FULL MLOG$_TABLE2 (cr=3058867 pr=0 pw=0 time=5891181 us cost=7990 size=0 card=1)
Additionally, in the trace file I see that there are queries to ALL the m.view logs in the db, even those that are located in anoter schemas.
Has anyone else faced such unexpected behavior ? How did you solve it ? Do you have ideas for workaround ?
WORKAROUND:
Use timestamp based mat.view logs instead of commit scn based ones.
