Beiträge getaggt mit Materialized Views
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_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:
Materialized Views & Partition Change Tracking
During the 11g Data Warehouse Administration course that I delivered this week in Munich, I have shown this demonstration about Partition Change Tracking (PCT) that I’d like to share with the Oracle Community. You may have a look here for an Introduction into Materialized Views if that topic is new for you. Often, the Base Tables of Materialized Views are partitioned, which gives use additional options for the Refresh as well as for the Rewrite:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> create table sales (product varchar2(50), channel_id number, cust_id number, amount_sold number(10,2), time_id date) partition by list (channel_id) (partition c0 values (0), partition c1 values (1), partition c2 values (2), partition c3 values (3), partition c4 values (4) ); Table created. SQL> alter table sales nologging; Table altered. SQL> insert /*+ append */ into sales select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id, mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e6; 1000000 rows created. SQL> commit; Commit complete.
The above was my demo setup, done with 11.2.0.1 Most of the shown should work with lower versions also, though. We will now see a Materialized View that contains the Partition Key of the Base Table, which makes it easily possible for the system to associate the rows of the Materialized View with the Partitions. Otherwise, we would need to explain that with a Partition Marker.
SQL> create materialized view mv_pct enable query rewrite as select channel_id,sum(amount_sold) from sales group by channel_id; Materialized view created.
The MV is now fresh and may get used for Query Rewrite:
SQL> select mview_name,last_refresh_type,staleness from user_mviews; MVIEW_NAME LAST_REF STALENESS ------------------------------ -------- ------------------- MV_PCT COMPLETE FRESH SQL> set autotrace on explain SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 0 1000000000 1 1000000000 2 1000000000 3 1000000000 4 1000000000 Execution Plan ---------------------------------------------------------- Plan hash value: 470332451 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 130 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_PCT | 5 | 130 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
Now we modify one row:
SQL> set autotrace off SQL> update sales set amount_sold=1 where rownum<2; 1 row updated. SQL> commit; Commit complete.:
This makes the MV stale right? Well actually not all of it:
SQL> select mview_name,last_refresh_type,staleness from user_mviews; MVIEW_NAME LAST_REF STALENESS ------------------------------ -------- ------------------- MV_PCT COMPLETE NEEDS_COMPILE SQL> select detail_partition_name, freshness from user_mview_detail_partition; DETAIL_PARTITION_NAME FRESH ------------------------------ ----- C4 FRESH C3 FRESH C2 FRESH C1 FRESH C0 STALE
The second Dictionary View is new in 11g, not the behavior of the next query. Only Partition C0 is stale, which is why queries that do not need channel_id 0 can still use the MV:
SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
1000000000
Execution Plan
----------------------------------------------------------
Plan hash value: 1805549181
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_PCT | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MV_PCT"."CHANNEL_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
The Rewrite is still possible although part of the MV is stale. We call that PCT Rewrite. Furthermore, we can now do a PCT Fast Refresh, although there is no MV Log on the Base Table sales. The PCT Refresh does not need to scan the whole Base Table but only the stale partitions, which is in this case about 5 times faster than a Complete Refresh, because we have 5 equally sized partitions. The value ‚P‚ is new in 11g, but it also works with ‚F‘ or ‚?‘ in earlier versions.
SQL> set autotrace off SQL> exec dbms_mview.refresh('MV_PCT','P') PL/SQL procedure successfully completed. SQL> select mview_name,last_refresh_type,staleness from user_mviews; MVIEW_NAME LAST_REF STALENESS ------------------------------ -------- ------------------- MV_PCT FAST_PCT FRESH
Conclusion: Partitioning delivers some extra benefits when used together with Materialized Views. Especially, Rewrite & Refresh can be done on the Partition layer then. As always: „Don’t believe it, test it!“ 🙂
Brief introduction into Materialized Views
Materialized Views have the ability to speed up queries (even dramatically) while being transparent for the queries in a similar way as indexes resp. partitioned tables are. That means that we do not have to modify our queries in order to benefit from these structures. Unlike an ordinary view which is only a stored select statement that runs if we use the view, a materialized view stores the result set of the select statement as a container table. Let me demonstrate the benefit of that. I have a (for my little database) relatively large table and do an aggregation query on it:
SQL> desc sales Name Null? Type ----------------------------------------- -------- ---------------------------- PROD_ID NOT NULL NUMBER CUST_ID NOT NULL NUMBER TIME_ID NOT NULL DATE CHANNEL_ID NOT NULL NUMBER PROMO_ID NOT NULL NUMBER QUANTITY_SOLD NOT NULL NUMBER(10,2) AMOUNT_SOLD NOT NULL NUMBER(10,2) SQL> select count(*) from sales; COUNT(*) ---------- 7350744 SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES'; MB ---------- 286 SQL> show sga Total System Global Area 373293056 bytes Fixed Size 1219496 bytes Variable Size 134218840 bytes Database Buffers 234881024 bytes Redo Buffers 2973696 bytes SQL> set timing on SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002085 9 2219410.08 Elapsed: 00:00:04.51
As you can see, the aggregation takes round 5 seconds. Now I will speed that up using a Materialized View:
SQL> create materialized view mv1 enable query rewrite 2 as select channel_id,sum(amount_sold) from sales group by channel_id; Materialized view created. Elapsed: 00:00:05.69 SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002085 9 2219410.08 Elapsed: 00:00:00.01
The very same statement now takes way less time! Why is that so?
SQL> set autotrace on explain
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002085
9 2219410.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2958490228
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 4 | 104 | 3 (0)| 00:00:01
--------------------------------------------------------------------------------
Because the optimizer transparently rewrote the query on the Materialized View! But what if we now change the content of the base table sales?
SQL> update sales set amount_sold=amount_sold+1 where rownum commit; Commit complete.
The materialized view is now stale and will no longer be used for query rewrite (as we can already determine by query runtime):
SQL> select mview_name,staleness from user_mviews; MVIEW_NAME STALENESS ------------------------------ ------------------- MV1 NEEDS_COMPILE SQL> set timing on SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002086 9 2219410.08 Elapsed: 00:00:04.52
In order to get statements rewritten against the materialized view again, we must refresh it by some method.
One method is on demand with a procedure call like in this example:
SQL> exec dbms_mview.refresh(list=>'MV1',method=>'C') PL/SQL procedure successfully completed. Elapsed: 00:00:04.62 SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 210770739 4 109654416 3 463002086 9 2219410.08 Elapsed: 00:00:00.01
Now the MV1 can get used again, as we see by runtime. That was a complete refresh – which can take a long time. We would like to get this done faster.
One comfortable method to get a fast refreshable materialized view is the usage of the package DBMS_ADVISOR:
SQL> vari t varchar2(50) SQL> begin 2 dbms_advisor.tune_mview(task_name=>:t, 3 mv_create_stmt=>'create materialized view mv1' 4 || ' refresh fast as' 5 || ' select channel_id,sum(amount_sold)' 6 || ' from sales group by channel_id'); 7 end; 8/ PL/SQL procedure successfully completed. SQL> set long 5000 SQL> select statement from user_tune_mview where task_name=:t order by action_id; STATEMENT -------------------------------------------------------------------------------- CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID ","AMOUNT_SOLD") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "ADAM"."SALES" ADD ROWID, SEQUENCE ("CHANNE L_ID","AMOUNT_SOLD") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST WITH ROWID DISABLE QUERY REWRIT E AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUN T("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SA LES.CHANNEL_ID STATEMENT -------------------------------------------------------------------------------- DROP MATERIALIZED VIEW ADAM.MV1 SQL> exec dbms_advisor.delete_task(:t) PL/SQL procedure successfully completed.
Usually, we need Materialized View Logs on the base tables and special expressions in the materialized view query to be able to do a fast refresh.
I simply take the coding from above now:
SQL> CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; 2 3 Materialized view log created. SQL> CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST WITH ROWID enable QUERY REWRITE AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SALES.CHANNEL_ID; Materialized view created.
After again modifying the base table sales, I will then try a fast refresh of the MV1:
SQL> set timing off SQL> update sales set amount_sold=amount_sold*1 where rownum commit; Commit complete. SQL> select mview_name,staleness from user_mviews; MVIEW_NAME STALENESS ------------------------------ ------------------- MV1 NEEDS_COMPILE SQL> set timing on SQL> exec dbms_mview.refresh('MV1','F') PL/SQL procedure successfully completed. Elapsed: 00:00:00.62
It worked! But do I always have to refresh manually? No, we can also schedule a regular refresh like this:
SQL> alter materialized view mv1 refresh start with sysdate next sysdate + interval '1' minute; Materialized view altered. SQL> alter session set nls_date_format='hh24:mi:ss'; Session altered. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 10:40:05 SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 10:41:04
Of course, a one minute refresh interval is extraordinary short. An other possibility to refresh the MVs very comfortably is on commit:
CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST on commit WITH ROWID enable QUERY REWRITE AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SALES.CHANNEL_ID; Materialized view created. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 COMPLETE 11:16:28 SQL> update sales set amount_sold=amount_sold*1 where rownum commit; Commit complete. SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews; MVIEW_NAME LAST_REF LAST_REF ------------------------------ -------- -------- MV1 FAST 11:19:56
But this may (although done in asynchronous fashion) slow down the DML on the base table, so it requires testing whether the slow down is noticeable or not. So far for now about Materialized Views. There is of course much more to say, but I think that this is enough for a brief introduction of that topic.