Database refresh from higher to lower is not possible but from lower to higher is possible.
Uwe Hesse
about Database Technology
Real-Time Materialized Views in #Oracle 12c
In 12cR2, a Materialized View that is STALE can still speed up queries while delivering correct results. The data from the stale MV is then on the fly combined with the change information from MV logs in an operation called ON QUERY COMPUTATION. The result is delivered slightly slower as if the MV were FRESH, so there is some overhead involved in the process. But it should be noticeable faster than having to do Full Table Scans as it was required in versions before 12c in that situation.
Operationally, that means that REFRESH can be done less frequently while keeping satisfactory query performance all the time. Let’s see that in action:
[oracle@uhesse ~]$ sqlplus adam/adam@pdb1
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 14:31:00 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Thu Jan 05 2017 10:57:35 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
1 4000000
2 4000000
4 4000000
3 4000000
0 4000000
Elapsed: 00:00:03.47
SQL> set timing off
The query takes more than three seconds without an MV initially.
SQL> create materialized view log on sales
with rowid, sequence(channel_id,amount_
The FRESH MV speeds up the query – not yet new. The same kind of execution plan would have been used in 11g:
SQL> @lastplan PLAN_TABLE_OUTPUT ------------------------------------------------------------ ------------------------- SQL_ID 9wwp2am6pm4dz, child number 1 ------------------------------ ------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2958490228 ------------------------------ ------------------------------ ------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------ ------------------------------ ------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 5 | 30 | 3 (0)| 00:00:01 | ------------------------------ ------------------------------ ------------------------- 13 rows selected.
Now I change something in the sales table, making the MV STALE:
SQL> update sales set amount_sold=2 where rownum<2; 1 row updated. SQL> commit; Commit complete. SQL> select mview_name,staleness,on_query_computation from user_mviews; MVIEW_NAME STALENESS O ---------- ------------------- - MV1 NEEDS_COMPILE Y
In spite of the STALE MV, the next query is still fast, although not as fast as with the FRESH MV:
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 4000000
3 4000000
4 4000000
0 4000000
1 4000001
Elapsed: 00:00:00.12
SQL> set timing off
So what happens is roughly this:
That there’s some work been done under the covers is revealed by looking at the (rather scary) execution plan now:
SQL> @lastplan PLAN_TABLE_OUTPUT ------------------------------------------------------------ ------------------------------ --------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------ ------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 ------------------------------ ------------------------------ ------------------------------ --------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------ ------------------------------ ------------------------------ --------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| 00:00:01 | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| 00:00:01 | | 5 | VIEW | | 5 | 160 | 3 (0)| 00:00:01 | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| 00:00:01 | | 7 | VIEW | | 100 | 1100 | 4 (25)| 00:00:01 | | 8 | HASH GROUP BY | | | | 4 (25)| 00:00:01 | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| 00:00:01 | | 10 | VIEW | | 263 | 6838 | 11 (28)| 00:00:01 | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| 00:00:01 | | 14 | VIEW | | 100 | 5200 | 4 (25)| 00:00:01 | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| 00:00:01 | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| 00:00:01 | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| 00:00:01 | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| 00:00:01 | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| 00:00:01 | | 24 | VIEW | | 100 | 5500 | 4 (25)| 00:00:01 | | 25 | SORT GROUP BY | | | | 4 (25)| 00:00:01 | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (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"."CHANNEL_ID")=SYS_OP_ MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$">TO_ DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$ "."OLD_NEW$$",'N','I','D'),'I' ,1,(-1)))>0) 17 - filter("MAS$"."SNAPTIME$$">TO_ DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss')) 18 - access("MV1"."SYS_NC00005$"= SYS_OP_MAP_NONNULL("AV$0"." GB0")) 22 - filter("MV1"."COUNT(*)"+"AV$0" ."D0">0) 23 - access("MV1"."SYS_NC00005$"= SYS_OP_MAP_NONNULL("AV$0"." GB0")) filter("MV1"."SYS_NC00005$"= SYS_OP_MAP_NONNULL("AV$0"." GB0")) 26 - filter("MAS$"."SNAPTIME$$">TO_ DATE(' 2017-01-05 14:32:07', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic statistics used: dynamic sampling (level=2) 60 rows selected.
But the query delivers the correct result – so that is not simply using the STALE MV only like QUERY_REWRITE_INTEGRITY=STALE_
SQL> show parameter query_rewrite_integrity NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ query_rewrite_integrity string enforced
Still REFRESH should be done from time to time like here:
SQL> exec dbms_mview.refresh('MV1','F')
PL/SQL procedure successfully completed.
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
1 4000001
2 4000000
4 4000000
3 4000000
0 4000000
Elapsed: 00:00:00.06
Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? At least I think so
Watch me on YouTube explaining and demonstrating the above:
« Previous
Leave a Reply
- Hi Uwe,Do you know if this (or ON STATEMENT) is a suitable workaround for the problem that FAST REFRESH ON COMMIT cannot be used in a database that uses database links?Regards,
- Hi Kevan Gelling, what I can say is that “on query computation” is not supported together with “refresh fast on commit”, probably because both together makes no sense. I’m not aware of any limitations towards database links, but I did not test for that either. Maybe you do and let us know afterwards
Thanks and Regards
Rakesh
Rakesh
No comments:
Post a Comment