[20230225]12c Real-time materialized view 实时物化视图的应用.txt

--//http://www.ludatou.com/?p=3101

在12.2之前，如果你想获得实时的数据，那么在利用query rewrite前，你必须得用on commit的刷新方式刷新物化视图。但是on commit
的刷新方式有众多限制。所以，以往的方式中需要采用on command的方式来进行刷新(不管是全量刷新还是增量刷新)。

在使用on command刷新的时候，通过设置job来定时的刷新物化视图，一次job运行之后，下一次job到来之前，如果基表有数据变化是不
会体现到物化视图的查询结果里的，实时物化视图就是解决这种问题的，实时获取数据而且免去频繁刷新mv。

1.环境:
SCOTT@test01p> @prxx_win.sql
==============================
PORT_STRING                   : IBMPC/WIN_NT64-9.1.0
VERSION                       : 12.2.0.1.0
BANNER                        : Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SCOTT@test01p> show parameter rewrite
PARAMETER_NAME          TYPE   VALUE
----------------------- ------ --------
query_rewrite_enabled   string TRUE
query_rewrite_integrity string enforced

2.测试例子:

SCOTT@test01p> create table t2  (x not null primary key, y not null) tablespace users as select rownum x, mod(rownum, 10) y from dual connect by level <= 1e5 ;
Table created.

--//分析表.
@ gts lis.t2 '' '' ''

SCOTT@test01p> create materialized view log on t2 with rowid (x, y) including new values;
Materialized view log created.

create materialized view mv_new
refresh fast on demand
enable on query computation
enable query rewrite
as
select y , count(*) c1
from t2
group by y;

SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1   COUNT(*)
------------ ----------
           1      10000
           6      10000
           2      10000
           4      10000
           5      10000
           8      10000
           3      10000
           7      10000
           9      10000
           0      10000
10 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bns44pywqdv33, child number 0
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 496717744
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |        |       |     3 (100)|          |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_NEW |     10 |    60 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$E1B4E35D / MV_NEW@SEL$DB93197A
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
24 rows selected.
--//查询直接视图物化视图.

SCOTT@test01p> insert into t2 select 1e5+rownum, mod(rownum, 3) from dual connect by level <= 999;
999 rows created.

SCOTT@test01p> commit ;
Commit complete.

3.继续测试:
SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1   COUNT(*)
------------ ----------
           6      10000
           4      10000
           5      10000
           8      10000
           3      10000
           7      10000
           9      10000
           1      10333
           2      10333
           0      10333
10 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bns44pywqdv33, child number 1
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 1607751112
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |        |       |    22 (100)|          |       |       |          |
|   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 |  2078K|  2078K|  899K (0)|
|   5 |      VIEW                  |                |      1 |     7 |     6  (17)| 00:00:01 |       |       |          |
|   6 |       HASH GROUP BY        |                |      1 |    22 |     6  (17)| 00:00:01 |  1214K|  1214K|  707K (0)|
|*  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 |  1214K|  1214K|  754K (0)|
|* 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)|          |       |       |          |
|* 19 |      HASH JOIN             |                |      1 |    35 |     7  (15)| 00:00:01 |  1743K|  1743K|  659K (0)|
|  20 |       VIEW                 |                |      1 |    29 |     6  (17)| 00:00:01 |       |       |          |
|  21 |        HASH GROUP BY       |                |      1 |    22 |     6  (17)| 00:00:01 |  1214K|  1214K|  756K (0)|
|* 22 |         TABLE ACCESS FULL  | MLOG$_T2       |    999 | 21978 |     5   (0)| 00:00:01 |       |       |          |
|  23 |       MAT_VIEW ACCESS FULL | MV_NEW         |      1 |     6 |     1   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1        / from$_subquery$_003@SEL$1
   2 - SET$1
   3 - SEL$EC770CBB / from$_subquery$_009@SEL$7
   4 - SEL$EC770CBB
   5 - SEL$EC77BF7C / AV$0@SEL$2
   6 - SEL$EC77BF7C
   7 - SEL$EC77BF7C / MAS$@SEL$5
   8 - SEL$F065AEA4 / SNA$0@SEL$2
   9 - SEL$F065AEA4 / MV_NEW@SEL$3
  10 - SET$2        / from$_subquery$_011@SEL$8
  11 - SET$2
  12 - SEL$0A26C4AD
  14 - SEL$196A2F92 / AV$0@SEL$2
  15 - SEL$196A2F92
  17 - SEL$196A2F92 / MAS$@SEL$5
  18 - SEL$0A26C4AD / MV_NEW@SEL$3
  19 - SEL$0B44CC95
  20 - SEL$7286615E / AV$0@SEL$2
  21 - SEL$7286615E
  22 - SEL$7286615E / MAS$@SEL$5
  23 - SEL$0B44CC95 / MV_NEW@SEL$3
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(' 2023-02-24 21:49:03', '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(' 2023-02-24 21:49:03', 'syyyy-mm-dd hh24:mi:ss'))
  18 - access("MV_NEW"."SYS_NC00003$"=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  19 - access(SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
       filter("MV_NEW"."C1"+"AV$0"."D0">0)
  22 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2023-02-24 21:49:03', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
82 rows selected.

--//Real time mv利用原来的已经stale的物化视图，结合mv log，通过计算后，帮你获取实时的数据。即能获得实时数据，又避免频繁的刷
--//新mv。

SCOTT@test01p> select mview_name,staleness,REFRESH_METHOD from user_mviews  where MVIEW_NAME='MV_NEW';
MVIEW_NAME           STALENESS           REFRESH_
-------------------- ------------------- --------
MV_NEW               STALE               FAST
--//我定义的物化视图不会自动刷新.

SCOTT@test01p> exec dbms_mview.refresh(list=>'MV_NEW',method=>'F');
PL/SQL procedure successfully completed.

SCOTT@test01p> select mview_name,staleness,REFRESH_METHOD from user_mviews  where MVIEW_NAME='MV_NEW';
MVIEW_NAME           STALENESS           REFRESH_
-------------------- ------------------- --------
MV_NEW               FRESH               FAST

SCOTT@test01p> select y as y_new_parse1, count(*) from t2 group by y;
Y_NEW_PARSE1   COUNT(*)
------------ ----------
           1      10333
           6      10000
           2      10333
           4      10000
           5      10000
           8      10000
           3      10000
           7      10000
           9      10000
           0      10333
10 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bns44pywqdv33, child number 0
-------------------------------------
select y as y_new_parse1, count(*) from t2 group by y
Plan hash value: 496717744
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |        |       |     3 (100)|          |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_NEW |     10 |    60 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$E1B4E35D / MV_NEW@SEL$DB93197A

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
24 rows selected.
--//刷新后不再访问mv log.

--//修改为1分钟刷新1次.
SCOTT@test01p> alter materialized view mv_new refresh start with sysdate next sysdate + interval '1' minute;
Materialized view altered.

SCOTT@test01p> delete from t2 where y=0 and rownum<=333;
333 rows deleted.

SCOTT@test01p> commit ;
Commit complete.

--//第一次看结合mv log,执行计划不再贴出.
--//等上一段时间.执行计划就仅仅访问物化视图了.

SCOTT@test01p> select * from user_mviews  where MVIEW_NAME='MV_NEW'
  2  @ prxx_win
==============================
OWNER                         : SCOTT
MVIEW_NAME                    : MV_NEW
CONTAINER_NAME                : MV_NEW
QUERY                         : select y , count(*) c1
from t2
group by y
QUERY_LEN                     : 41
UPDATABLE                     : N
UPDATE_LOG                    :
MASTER_ROLLBACK_SEG           :
MASTER_LINK                   :
REWRITE_ENABLED               : Y
REWRITE_CAPABILITY            : GENERAL
REFRESH_MODE                  : DEMAND
REFRESH_METHOD                : FAST
BUILD_MODE                    : IMMEDIATE
FAST_REFRESHABLE              : DIRLOAD_DML
LAST_REFRESH_TYPE             : FAST
LAST_REFRESH_DATE             : 2023-02-24 22:15:31
LAST_REFRESH_END_TIME         : 2023-02-24 22:15:31
STALENESS                     : FRESH
AFTER_FAST_REFRESH            : FRESH
UNKNOWN_PREBUILT              : N
UNKNOWN_PLSQL_FUNC            : N
UNKNOWN_EXTERNAL_TABLE        : N
UNKNOWN_CONSIDER_FRESH        : N
UNKNOWN_IMPORT                : N
UNKNOWN_TRUSTED_FD            : N
COMPILE_STATE                 : VALID
USE_NO_INDEX                  : N
STALE_SINCE                   :
NUM_PCT_TABLES                : 0
NUM_FRESH_PCT_REGIONS         :
NUM_STALE_PCT_REGIONS         :
SEGMENT_CREATED               : YES
EVALUATION_EDITION            :
UNUSABLE_BEFORE               :
UNUSABLE_BEGINNING            :
DEFAULT_COLLATION             : USING_NLS_COMP
ON_QUERY_COMPUTATION          : Y
PL/SQL procedure successfully completed.