The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only materialized view?
  2. Show me it working
  3. What’s it done to my commit time?
  4. How can this be avoided this with _mv_refresh_use_stats?
  5. Is it any different if I use MLOG stats?
  6. Why might I not want to mess with _mv_use_refresh_stats?


First, why am I looking at a fast refresh, on-commit join-only materialized view.

I have got two datasets which only produce a small resultset when they are joined.
But neither is, on their own, highly selective.

The model below might not be 100% representative of my real-world situation.
I’ve tried to sanitise and simplify but as a result I’ve then had to bump up the volumes a bit to make the point.

drop table t1;
drop table t2;

create table t1
as
select rownum oid
,      case when rownum <= 1000000 then 'Y' else 'N' end flag
,      rownum oid_t2
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t1_1 on t1 (oid);
create index i_t1_2 on t1(flag);
create index i_t1_3 on t1(oid_t2);

create table t2
as
select rownum oid
,      case when rownum >= 999901 then 'Y' else 'N' end flag
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t2_1 on t2 (oid);
create index i_t2_2 on t2(flag);

Currently, I can crunch my data in a few seconds:

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Gives

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:15.91 |     606K|    303K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:15.91 |     606K|    303K|   238M|  7667K|  292M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.54 |     303K|      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |      0 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:09.78 |     303K|    303K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But I want to query these tables quite frequently and preferably I want this driving logic to be subsecond.
And there will have reasonable inserts and updates incoming at various times.

So, one of the better options is a join-only materialized view.


So, let’s get going:

create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv_t1_t2
refresh fast on commit
enable query rewrite
as
select t1.rowid   t1_rowid
,      t1.oid     t1_oid
,      t1.flag    t1_flag
,      t1.oid_t2  t1_oid_t2
,      t1.padding t1_padding
,      t2.rowid   t2_rowid
,      t2.oid     t2_oid
,      t2.flag    t2_flag
,      t2.padding t2_padding
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

create index i_mv_t1_t2_1 on mv_t1_t2(t1_rowid);
create index i_mv_t1_t2_2 on mv_t1_t2(t2_rowid);

Tangent alert!
Now, if I wasn’t going to go off on this tangent because the article will be long enough anyway but when running up the article, a number of questions about
“hy was this not being rewritten?” came up so, we might as well cover more bases.

So, if we run our SELECT now, what happens?

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:08.22 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:08.22 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.69 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------  

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

Bother!

We could go digging around but let’s try the easy way out – DBMS_MVIEW.EXPLAIN_REWRITE

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message
  measure_in_msg        VARCHAR2(30),   -- Measure in current message
  join_back_tbl         VARCHAR2(30),   -- Join back table in message
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used
  reerved2              VARCHAR2(10))   -- currently not used;

DECLARE
l_sql CLOB :=
q'{select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y'}';
BEGIN
 DBMS_MVIEW.EXPLAIN_REWRITE(l_sql,'MV_T1_T2','mysql');
END;
/

select message from rewrite_table;
MESSAGE
-------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled

Yep – easy one.
Let’s set query_rewrite_enabled to true (would be system level if this was not just a test/demo) and repeat.

alter session set query_rewrite_enabled = true;
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    101 |00:00:00.01 |      16 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |      1 |    101 |    101 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------

Great – I have my subsecond response time.

But what about updates to the data?

First of all, for completeness, let’s observe what happens when we make an uncommitted change.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.27 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.27 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.63 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.65 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------   

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

I’ve lost my usage of the MV.
This is expected.
Why?
DBMS_MVIEW.EXPLAIN_REWRITE tells us:

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01279: query rewrite not possible because DML operation occurred on a table referenced by materialized view MV_T1_T2

We can’t use the materialized view because it’s stale.
To use it we have to consider whether we want to run with query_rewrite_integrity set to stale_tolerated.
In this case, not.

If I continue to leave the update uncommitted and check another session, the it continues to use the MV.

But, another tangent!!
In that other session, if I set statistics_level to all, then it won’t use the MV and DBMS_MVIEW.EXPLAIN_REWRITE can’t tell me why.
I’m going to conveniently leave that investigation for another time but just show the evidence:

alter session set statistics_level = all;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.10 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.10 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.49 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.72 |     303K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                                                         

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But

alter session set statistics_level = typical;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |    101 |
----------------------------------------------------------   

Note
-----
   - Warning: basic plan statistics not available.....

So… anyway… we have our fast-refresh on commit materialized view.


What happens when we commit my previous change?

set timing on
update t1 set flag = 'Y' where oid = 1000001;

1 rows updated.
Elapsed: 00:00:00.029

commit

committed.
Elapsed: 00:00:02.098

Gadzooks! My COMMIT now takes two seconds!

At this point, if you read a lot of technical articles, you might get lucky and have that vague sense of nagging familiarity about having read about this at some point…

Alberto Dell’Era has an excellent set of articles which helps understand what is going on here and what we might do about it.

If we trace my session, we can find out what is contributing to this unpleasant two seconds.
I’m going to cheat, flush the shared pool, do the update and commit and check v$sql
Here is a summary of the significant contributing sql snippets.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
dt1mmbxbp6uk7            1                          1                 1.9356 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE(
5qah9xjyhapg1            0       828077931          1               0.010237 /* MV_REFRESH (DEL) */ DELETE FROM "E668983_DBA"."MV_T1_T2" SNA$ WHERE "T1_ROWID
4agmycb29dd1p            0                          1               0.001747 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000589 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000518 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmlty

A couple of interesting observations.
First is that we can see the XID$$ mechanism which Alberto mentions in his articles linked to above.

Second is that the INSERT is the major contributor to our commit time, followed by the DELETE.
We shall focus on these.

These are the prettified statements:

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM   ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
         FROM "DOM"."T1" "MAS$"
         WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                         FROM "DOM"."MLOG$_T1" "MAS$"
                         WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE  "JV$"."FLAG"='Y'
AND    "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y';

With plan:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL         |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   2 |   NESTED LOOPS                   |            |      1 |        |      1 |00:00:01.92 |     303K|       |       |          |
|   3 |    NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|   4 |     VIEW                         |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|*  5 |      HASH JOIN RIGHT SEMI        |            |      1 |      1 |      1 |00:00:01.92 |     303K|   832K|   832K|  387K (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1         |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_2     |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN            | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------ 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   7 - access("MAS$"."XID$$"=:1)
   9 - access("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE
FROM   "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ *
                      FROM   (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"
                              WHERE "MAS$".XID$$ = :1 )MAS$)
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|*  2 |   HASH JOIN SEMI              |              |      1 |    101 |      1 |00:00:00.01 |       3 |   963K|   963K| 1252K (0)|
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------  

   2 - access("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

If you have read Alberto’s blogs then you will notice the problematic HASH JOIN RIGHT SEMI in the INSERT
And you will also be familiar with a couple of solutions which now present themselves.

It’s great that there are known workarounds to this problem but, as I hope to demonstrate, the fact that there are at least three separate codepaths through the fast refresh mechanism worries me particularly when we see that
at least one of these alternative code paths causes bugs with other variations on the MV refresh mechanism. What a mess!!


First solution to the problem is paramter _mv_refresh_use_stats.
Repeat update & commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

set timing on
update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.035

commit;
committed.
Elapsed: 00:00:00.174

That’s better – 100ms for my commit.

What do our INSERT and DELETE statements look like now?
Note the different SQL IDs – these are completely different statements.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
av53npjd112vx            1       374625119          1               0.013106 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
5a6ugwh5v1j5x            0       466548951          1               0.009088 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
gpk46p11kbp3d            0      1735585849          1               0.001665 SELECT OLD_NEW$$, COUNT(*)  FROM "DOM"."MLOG$_T1"   WHERE SNAPTIME$$ > :1 AND SN
4agmycb29dd1p            0                          1               0.001615 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000538 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000515 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$     

For both the INSERT and the DELETE, we’ve lost the HASH_SJ hint and we’ve gained a CARDINALITY hint and a NO_SEMIJOIN.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
       FROM   "DOM"."T1" "MAS$"
       WHERE  ROWID IN (SELECT /*+ CARDINALITY(MAS$ 0)  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                        FROM   "DOM"."MLOG$_T1" "MAS$"
                        WHERE  "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y'
AND   "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y'

Giving plan:

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL           |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                     |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                    |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                           |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       VIEW                         | VW_NSO_1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |        SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   8 |         TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |       TABLE ACCESS BY USER ROWID   | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN              | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID     | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("MAS$"."XID$$"=:1)
  10 - filter("MAS$"."FLAG"='Y')
  11 - access("JV$"."OID_T2"="MAS$0"."OID")
  12 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ *
                      FROM   (SELECT  /*+ CARDINALITY(MAS$ 0) */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)

with plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                         | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   2 |   NESTED LOOPS                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                         | VW_NSO_1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     SORT UNIQUE                 |              |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |    INDEX RANGE SCAN             | I_MV_T1_T2_1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("MAS$"."XID$$"=:1)
   7 - access("T1_ROWID"="RID$")

There’s no doubt that that is faster for our specific circumstances.

What about the second workaround available?

Let’s reset “_mv_refresh_use_stats” and look at locking stats on the MV logs.

alter session set "_mv_refresh_use_stats" = false;

session SET altered.

begin
  dbms_stats.gather_table_stats(USER,'MLOG$_T1');
  dbms_stats.gather_table_stats(USER,'MLOG$_T2');
  dbms_stats.lock_table_stats(USER,'MLOG$_T1');
  dbms_stats.lock_table_stats(USER,'MLOG$_T2');
end;
/

anonymous block completed

select table_name, num_rows from user_tables where table_name in ('MLOG$_T1','MLOG$_T2');

TABLE_NAME                     NUM_ROWS
------------------------------ --------
MLOG$_T1                              0
MLOG$_T2                              0

What happens then?

update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.026

commit;

committed.
Elapsed: 00:00:00.169

Very similar to the effects of “mv_refresh_use_stats”, but exactly the same? No.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
69xq38c4ts0j2            1       190791364          1               0.013053 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
dzzb43k6h3vhs            0                          1               0.009172 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
4agmycb29dd1p            0                          1               0.000924 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1                0.00064 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000632 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$

We’ve got completely different SQL IDs again.

In both INSERT and DELETE we’ve lost any hint to do or avoid a semi-join and there’s no CARDINALITY hint.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "DOM"."T1" "MAS$"
       WHERE ROWID IN (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                       FROM "DOM"."MLOG$_T1" "MAS$"
                       WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y' AND "JV$"."OID_T2"="MAS$0"."OID" AND "MAS$0"."FLAG"='Y'
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                    |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                   |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                          |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                 |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |       TABLE ACCESS BY USER ROWID  | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN             | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("MAS$"."XID$$"=:1)
   9 - filter("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  */ *
                      FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                            FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |      13 |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS SEMI           |              |      1 |    101 |      1 |00:00:00.01 |       5 |
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |    101 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |    101 |      1 |    101 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

So, to briefly summarise the above, we have at least three clear codepaths through the fast refresh mechanism which result in completely different internal SQL being generated.
Multiple code paths are added complexity when it comes to combining other features, upgrades, testing, etc

It’s then no great surprise when such multiple codepaths lead to other bugs.

So which approach are we meant to do?

Should we just take the default longer commit on the chin?

I’m not sure.

But I can come up with an illustration of why we should think twice about doing down the “_mv_refresh_use_stats” approach.
Firstly it’s an underscore parameter so we should really get Oracle Support approval before setting it (which means a fair bit of back and forth until you get someone who knows what you’re going on about)


Secondly, we can quickly find some bugs around the usage of this parameter with the newer COMMIT SCN (also covered in Alberto’s blogs above)

drop materialized view log on t1;
drop materialized view log on t2;
create materialized view log on t1 with rowid, commit scn;
create materialized view log on t2 with rowid, commit scn;

drop materialized view mv_t1_t2;
create materialized view mv_t1_t2...

Then go back to our update and commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated

commit;

SQL Error: ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

We don’t actually need COMMIT SCN in our ON-COMMIT MV because we’re using the XID.
It’s a newer mechanism for FAST REFRESH ON DEMAND

I know that I don’t need COMMIT SCN in my case above but the fact that it’s so easy to find an issue means that I’m reluctant to do down the parameter route.

Note that there are quite a few bug notes around this in Oracle Support.
A lot of the advice is conflicting, not least because the behaviour of “_mv_refresh_use_stats” changes mid 10.1 something.

At the moment, I think I’m going to go with the locked stats on the MLOGs but it’s worrying.

What a mess!

References:
Lots of Oracle doc ids…

Advertisements

One Response to The mess that is fast-refresh join-only Materialized Views

  1. Nice summary Dom – i agree it’s a bit of a dogs dinner of an implementation and I thought this stuff was quite widely used but perhaps not. Not sure if anything changed in v12 – i suspect not as this seems to have been an issue for many years

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: