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

[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.