先来谈谈为什么要有这个real time mv。
在12.2之前，如果你想获得实时的数据，那么在利用query rewrite前，你必须得用on commit的刷新方式刷新物化视图。但是on commit的刷新方式有众多限制，如sql的复杂度，如频繁对系统的压力等等。所以，我们不得不采用on command的方式来进行刷新（不管是全量刷新还是增量刷新）。那么在使用on command刷新的时候，必须得有个job来定时的刷，那么，在一次job运行之后，下一次job到来之前，如果基表有数据变化，那么此时的数据肯定不是最新的。
real time mv就是为了解决这个问题而生的。它即可以帮你获取实时的数据，且不用频繁的刷新mv。
我们来看一下这是怎么实现的。
传统mv的创建方式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> create table t1 (x not null primary key, y not null) as 2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000; Table created. SQL> create materialized view log on t1 with rowid (x, y) including new values; Materialized view log created. SQL> SQL> create materialized view mv_old 2 refresh fast on demand 3 enable on query computation 4 enable query rewrite 5 as 6 select y , count(*) c1 7 from t1 8 group by y; Materialized view created. SQL> SQL> |
Real time mv的创建方式：
注意在create mv时的关键字：enable on query computation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> create table t2 (x not null primary key, y not null) as 2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000; Table created. SQL> create materialized view log on t2 with rowid (x, y) including new values; Materialized view log created. SQL> SQL> create materialized view mv_new 2 refresh fast on demand 3 enable on query computation 4 enable query rewrite 5 as 6 select y , count(*) c1 7 from t2 8 group by y; Materialized view created. SQL> SQL> |
我们来比较一下传统mv和real time mv的差别：
相关参数：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> show parameter rewr NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ query_rewrite_enabled string TRUE query_rewrite_integrity string enforced SQL> SQL> SQL> SQL> SQL> set autotrace on explain stat SQL> |
初始状态：
传统mv：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
SQL> select y as y_new_parse1, count(*) from t1 2 group by y; Y_NEW_PARSE1 COUNT(*) ------------ ---------- 1 100000 6 100000 2 100000 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 0 100000 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2738786661 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 60 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_OLD | 10 | 60 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1029 recursive calls 2 db block gets 1587 consistent gets 76 physical reads 0 redo size 739 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 86 sorts (memory) 0 sorts (disk) 10 rows processed SQL> SQL> |
Real time mv:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> select y as y_new_parse1, count(*) from t2 2 group by y; Y_NEW_PARSE1 COUNT(*) ------------ ---------- 1 100000 6 100000 2 100000 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 0 100000 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 496717744 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 60 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_NEW | 10 | 60 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 170 recursive calls 13 db block gets 248 consistent gets 7 physical reads 2008 redo size 739 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 21 sorts (memory) 0 sorts (disk) 10 rows processed SQL> |
看到此时2个物化视图，数据都是最新的，staleness显示是fresh：
|
1 2 3 4 5 6 7 8 |
SQL> select mview_name,staleness,on_query_computation from user_mviews; MVIEW_NAME STALENESS O ---------------------------------------- ------------------- - MV_OLD FRESH N MV_NEW FRESH Y SQL> |
物化视图日志里面也没有记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select count(*) from MLOG$_T1; COUNT(*) ---------- 0 SQL> select count(*) from MLOG$_T2; COUNT(*) ---------- 0 SQL> |
我们对基表insert数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> insert into t1 2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999; 999 rows created. SQL> SQL> insert into t2 2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999; 999 rows created. SQL> commit; Commit complete. SQL> |
可以看到2个表的staleness已经变成need compile，且物化视图日志表里面，也与了日志的记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> select mview_name,staleness,on_query_computation from user_mviews; MVIEW_NAME STALENESS O ---------------------------------------- ------------------- - MV_OLD NEEDS_COMPILE N MV_NEW NEEDS_COMPILE Y SQL> SQL> select count(*) from MLOG$_T1; COUNT(*) ---------- 999 SQL> select count(*) from MLOG$_T2; COUNT(*) ---------- 999 SQL> |
我们来见证一下奇迹的时刻。我们先重复上面第一个查询，可以看到，由于数据stale，且没有set query_rewrite_integrity=stale_tolerated，传统mv没有进行query write。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SQL> select y as y_new_parse1, count(*) from t1 2 group by y; Y_NEW_PARSE1 COUNT(*) ------------ ---------- 1 100333 6 100000 2 100333 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 0 100333 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 136660032 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 30 | 515 (4)| 00:00:01 | | 1 | HASH GROUP BY | | 10 | 30 | 515 (4)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 498 (1)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1975 recursive calls 30 db block gets 4167 consistent gets 1786 physical reads 5440 redo size 754 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 131 sorts (memory) 0 sorts (disk) 10 rows processed SQL> SQL> |
我们看到，real time mv，进行了query rewrite，且查到的数据是最新实时数据！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
SQL> select y as y_new_parse1, count(*) from t2 2 group by y; Y_NEW_PARSE1 COUNT(*) ------------ ---------- 6 100000 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 1 100333 2 100333 0 100333 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 542978159 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 12 | 312 | 22 (14)| 00:00:01 | | 1 | VIEW | | 12 | 312 | 22 (14)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 10 | 290 | 9 (12)| 00:00:01 | |* 4 | HASH JOIN FULL OUTER | | 10 | 240 | 9 (12)| 00:00:01 | | 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 | | 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 | |* 7 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 | | 8 | VIEW | | 10 | 170 | 3 (0)| 00:00:01 | | 9 | MAT_VIEW ACCESS FULL | MV_NEW | 10 | 60 | 3 (0)| 00:00:01 | | 10 | VIEW | | 2 | 52 | 13 (16)| 00:00:01 | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 1 | 32 | 6 (17)| 00:00:01 | | 14 | VIEW | | 1 | 26 | 6 (17)| 00:00:01 | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 | |* 17 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | 6 | 0 (0)| 00:00:01 | | 19 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 | | 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 | | 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 | |* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 | |* 23 | MAT_VIEW ACCESS BY INDEX ROWID| MV_NEW | 1 | 6 | 1 (0)| 00:00:01 | |* 24 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."Y")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 7 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-12 14:35:01', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(1)>0) 17 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-12 14:35:01', 'syyyy-mm-dd hh24:mi:ss')) 18 - access("MV_NEW"."SYS_NC00003$"(+)=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 22 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-07-12 14:35:01', 'syyyy-mm-dd hh24:mi:ss')) 23 - filter("MV_NEW"."C1"+"AV$0"."D0">0) 24 - access(SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) Note ----- - dynamic statistics used: dynamic sampling (level=2) - this is an adaptive plan Statistics ---------------------------------------------------------- 906 recursive calls 64 db block gets 1232 consistent gets 14 physical reads 10548 redo size 744 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 64 sorts (memory) 0 sorts (disk) 10 rows processed SQL> |
我们看到，在查t2的时候，优化器会根据成本决定是否使用query rewrite。
我们的这个例子中CBO选择使用query rewrite。可以看到query rewrite到物化视图之后，不是取的是过期的物化视图的值，而是最新的值。结合执行计划，可以看到，是结合了stale的物化视图，再union all和hash join outer了物化视图日志。得到了最新的结果。
可以看到，使用的物化视图日志是”MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’)之后的。
对比直接从table取值，到利用real time物化视图取值，consistent get从4167变成了1232。
注意我们的mv log还是没有被刷新的。还是需要去定期的job刷新：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select count(*) from MLOG$_T1; COUNT(*) ---------- 999 SQL> select count(*) from MLOG$_T2; COUNT(*) ---------- 999 SQL> |
另外再提一下，有个/*+ fresh_mv */的hint，可以直接查询real time mv的实时结果：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> select * from mv_new; Y C1 ---------- ---------- 1 100000 6 100000 2 100000 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 0 100000 10 rows selected. SQL> SQL> select /*+ fresh_mv */* from mv_new; Y C1 ---------- ---------- 6 100000 4 100000 5 100000 8 100000 3 100000 7 100000 9 100000 1 100333 2 100333 0 100333 10 rows selected. |
综上，Real time mv利用原来的已经stale的物化视图，结合mv log，通过计算后，帮你获取实时的数据。你即能获得实时数据，又不必那么频繁的刷新mv。
参考：
https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2#real-time-mv
https://blog.dbi-services.com/12cr2-real-time-materialized-view-on-query-computation/
https://uhesse.com/2017/01/05/real-time-materialized-views-in-oracle-12c/
https://docs.oracle.com/database/122/SQLRF/CREATE-MATERIALIZED-VIEW.htm#SQLRF01302
一条评论
如果变化的数据量特别大的情况下，查询性能是不是有影响呢？