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_sold)
including new values;
Materialized view log created.
SQL> create materialized view mv1
refresh fast on demand
enable query rewrite
enable on query computation
as
select channel_id,
sum(amount_sold),
count(amount_sold),
count(*)
from sales
group by channel_id;
Materialized view created.
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:00.07
SQL> set timing off
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_TOLERATED does. Just to confirm:
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:
Pickleball spielen 
#1 von Kevan Gelling am Januar 6, 2017 - 14:18
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,
#2 von Uwe Hesse am Januar 6, 2017 - 14:38
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 🙂
#3 von Kevan Gelling am Januar 6, 2017 - 16:29
Here’s an example:
– https://orastory.wordpress.com/2013/01/15/fun-with-distributed-transactions/#comment-7697
I was wondering if ON QUERY COMPUTATION (or ON STATEMENT) could be used with FAST ON DEMAND to simulate FAST ON COMMIT and, therefore, get around the remote DB issue.
#4 von Mikhail Velikikh am Januar 10, 2017 - 03:57
Hi Uwe,
Does this kind of materialized views support COMMIT SCN based mat view logs ?
I do not see any restrictions to this according to the documentation: http://docs.oracle.com/database/122/DWHSG/advanced-materialized-views.htm#GUID-BB11FFAB-771D-44AD-89D7-0231031D7ECA
I have tried to check it through livesql.oracle.com, but it does not support creation of a materialized view.
My attempt was failed with an error:
PLS-00201: identifier ‚SYS.DBMS_SNAPSHOT_UTL‘ must be declared
#5 von Navinth Bakmeedeniya am April 20, 2017 - 10:35
Hi Uwe,
Your post is very helpful.
I have a problem with this ‚ON QUERY COMPUTATION‘ functionality.
As I understand, when the MV is fresh, we get a MAT_VIEW REWRITE ACCESS FULL run which is not because ON QUERY COMPUTATION but QUERY REWRITE option. This situation work fine for me. My MV is enabled for both QUERY REWRITE & ON QUERY COMPUTATION.
But when the MV is not FRESH, it does a full table scan instead of reading MV + MV Log (& table). Execution plan is not similar to your example as my one contains only a full table scan.
However apart from the main select statement, there is another qeury is visible for this run (in V$SQL) as below which is related to the corresponing MV Log.
select dmltype$$, max(snaptime$$) from „IFSAPP“.“MLOG$_CUSTOMER_ORDER_LINE_TAB“ where snaptime$$ <= :1 group by dmltype$$
My Parameter Settings:
query_rewrite_integrity = ENFORCED
query_rewrite_enabled = TRUE
Do you have any comments why ON QUERY COMPUTATION is not working for my situation?
Many Thanks,
Navi
#6 von Jayesh am April 20, 2017 - 22:34
I have a very large data set for a DW and I created a materialized view on
it to give me a quick access to group by and summarization results, I want
some way to be able to update the MV with just the delta after I perform an
incremental load to the base fact or dimension tables on which the MV is
built. From what I have played with so far, I don’t see a ready solution.
Fast refreshes don’t work when MV is on complex view. We are talking about
dimension tables with possibly close to a billion rows, and fact tables of
similar proportions, so the time taken to refresh the MV becomes a factor.
Oracle version we are using
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
We are using DBMS_SNAPSHOT.REFRESH(Viewname,C);
It is refreshing the view with quiet significant delay.
#7 von Chris am Februar 5, 2020 - 16:08
Are queries that don’t use GROUP PY supported as well ?