11gR2: new algorithm for fast refresh of on-commit materialized views

This post investigates the improvements that have been made in 11gR2 to the fast refresh engine of materialized views (MVs) that are set to be automatically refreshed at commit time. We speak about join-only materialized views only in this post, as always with the help of a test case.

As noted in the post of mine "11gR2: materialized view logs changes", in 11gR2 a new column, xid$$, is now part of materialized view logs; this column records the id of the transaction that logged the changes of the base table which the log is defined on. It is important to stress that this column is added regardless of the type of the MV log, that is, to both the brand-new "commit SCN-based" logs and the old fashioned "timestamp-based" ones. That means that both types of MV logs can take advantage of the new improvements - albeit I haven't tested whether MVs (logs) migrated from a previous version are automatically upgraded by the migration scripts and get the new xid$$ column added.

algorithm before 11gR2

In versions before 11gR2, the refresh algorithm for on-commit MVs was the same as the one for on-demand ones, with only minor variants. That is, the algorithm was almost completely the same, just triggered by the commit event instead of by the user.

For an in-depth analysis of the algorithm, I will refer the reader to the discussion about the on-demand algorithm in the post "fast refresh of join-only materialized views - algorithm summary"; in passing, the test case for this post is in fact the very same three-table join MV, just redefined as "on commit" instead of "on demand". To recap, the "old" algorithm (until 11.1.0.7) was:

1) new log rows are inserted with snaptime$$=4000 A.D;
2) at refresh time (commit time), a snapshot of the new rows is taken, that is, all new rows are marked with snaptime$$= "commit time", using the statement

update MLOG$_TEST_T1
   set snaptime$$ = :1
 where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

3) all modifications whose snaptime$$ is between the date of the last refresh (excluded) and the commit date(included) are propagated to the MV. The propagation consists of two steps.
First a DEL step:

/* MV_REFRESH (DEL) */
delete from test_mv
 where test_t1_rowid in
       (
select * from
       (
select chartorowid (m_row$$)
  from mlog$_test_t1
 where snaptime$$ > :1
       ) -- no "as of snapshot (:2)" clause
       )

Then an INS one:

/* MV_REFRESH (INS) */
insert into test_mv
select jv.j1_2, jv.x1, jv.pk1, jv.rid$,
       mas2.j2_1, mas2.j2_3, mas2.x2, mas2.pk2, mas2.rowid,
       mas3.j3_2, mas3.x3, mas3.pk3, mas3.rowid
  from (
select log.rowid rid$, log.*
  from test_t1 log
 where rowid in
       (
select chartorowid(log.m_row$$)
  from mlog$_test_t1
 where snaptime$$ > :1
       )
       ) jv, -- no "as of snapshot (:2) jv" clause
       test_t2 as of snapshot (:2)  mas2,
       test_t3 as of snapshot (:2)  mas3
 where   jv.j1_2 = mas2.j2_1
   and mas2.j2_3 = mas3.j3_2

Note that the only small difference from the on-demand case is the absence of the "as of snapshot" clause, but the statements are otherwise identical. Note also that the rows in the MV log are identified in both statements by snaptime, using the subquery

select chartorowid(log.m_row$$)
  from mlog$_test_t1
 where snaptime$$ > :1

4) all obsolete log rows are deleted, that is, all rows whose snaptime$$ is less than or equal the lowest of all refresh times are removed from the log, using the the statement

delete from mlog$_test_t1
 where snaptime$$ <= :1

algorithm starting from 11gR2

In 11gR2, the on-commit algorithm is still almost the same as the on-demand one; the "only" change is how modified rows to be propagated are identified, and in general, how logs are managed. Not surprisingly, log rows are now directly identified by the transaction id, which is logged in xid$$. In detail:

1) new log rows are inserted with xid$$ = transaction id;
2) at refresh time (commit time), no snapshot is taken, that is, the MV log is not updated at all;
3) all modifications made by the committing transaction are propagated to the MV, still using the same two steps.
The DEL step is now:

/* MV_REFRESH (DEL) */
delete from test_mv
 where test_t1_rowid in
       (
select * from
       (
select chartorowid (m_row$$)
  from mlog$_test_t1
 where xid$$ = :1
       )
       )

The INS one is:

/* MV_REFRESH (INS) */
insert into test_mv
select jv.j1_2, jv.x1, jv.pk1, jv.rid$,
       mas2.j2_1, mas2.j2_3, mas2.x2, mas2.pk2, mas2.rowid,
       mas3.j3_2, mas3.x3, mas3.pk3, mas3.rowid
  from (
select log.rowid rid$, log.*
  from test_t1 log
 where rowid in
       (
select chartorowid(log.m_row$$)
  from mlog$_test_t1
 where xid$$ = :1
       )
       ) jv, -- no "as of snapshot (:2) jv" clause
       test_t2 as of snapshot (:2)  mas2,
       test_t3 as of snapshot (:2)  mas3
 where   jv.j1_2 = mas2.j2_1
   and mas2.j2_3 = mas3.j3_2

Hence, the big difference from the previous versions case is that rows in the MV log are identified very simply by the transaction that logged them (the committing transaction, of course), by the subquery

select chartorowid(log.m_row$$)
  from mlog$_test_t1
 where xid$$ = :1

4) all obsolete log rows are deleted, that is, the rows logged by the committing transaction are removed, using the the statement

delete from mlog$_test_t1
 where where xid$$ = :1

The new algorithm is for sure much simpler and more elegant. Performance is improved since the snapshot step has been removed, and the other steps are more or less as expensive as before.

practical implications: an example

I strongly believe that studying the internals is the best way to learn how to make the best use of any feature. Let's see an example of how the few bits of "internal knowledge" I shared here can be used in practice - that is, how a little investment in investigation makes for huge savings in effort afterwards, and huge gains in effectiveness of your work as well.

It is well-known that it can be sometimes beneficial, in pre-11gR2, to place an index on the log (indexing the log is even suggested by support note 258252 "MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring"). The scenario that benefits the most from such an index is when the log is composed of mostly-empty blocks, and hence an index access is preferable over a full table(log) scan; you get mostly-empty blocks, for example, when there are peeks in activity on the master tables that keep the log High Water Mark very high.

From the above discussion, it is obvious that in pre-11gR2, the best index for join-only MVs was on (snaptime$$, m_row$$) - not on snaptime$$ alone as it is sometimes suggested - to make the refresh operation an index-only one.

Starting from 11gR2, the best index is now on (xid$$, m_row$$). Not only that, but having no snapshot step, and hence no update on the index, makes the indexing option even more attractive.

Could you see these implications so easily, without knowing the internals? I don't.


  1. Igor

    Friday, December 4, 2009

    Hi Alberto,

    I agree with your point of view. (just don’t tell to tkyte about it. Just kidding… :-)

    Thank you for these insights.

  2. Blogroll Report 20/11/2009-27/11/2009 « Coskan’s Approach to Oracle

    Saturday, December 12, 2009

    [...] 4-How does fast refresh of on-commit materialized views works on 11GR2? Alberto Dell’era-11gR2: new algorithm for fast refresh of on-commit materialized views [...]

  3. Joaquin

    Thursday, December 17, 2009

    Hi Alberto,

    It seems that materialized views are refreshed using only deletes and inserts, am I right? If that’s true, then there’s no reason for the pctfree of the materialized view to be not equal to zero, right?

    Thank you!

    Joaquin Gonzalez

  4. Alberto Dell'Era

    Thursday, December 17, 2009

    @Joaquin

    for join-only MVs, that is, the kind of MV investigated in this post, sure.

    Anyway, I always check the actual algorithm using the actual SQL statement and the exact database version I am using for important MVs; better safe than sorry… you can easily adapt my test case for that purpose.

    I don’t think that setting pctfree to zero is going to improve the performance too much – at most it might reduce the resource consumption of full table scans by 10%, which is not that much.

  5. Taral Desai

    Wednesday, January 6, 2010

    Hello Alberto,

    I have some problem with mv where i am using 10.2.0.4. I saw your trace file and in that it uses

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          2  TABLE ACCESS BY INDEX ROWID TEST_T1 (cr=21 pr=0 pw=0 time=4362 us)
          5   NESTED LOOPS  (cr=20 pr=0 pw=0 time=4307 us)
          2    NESTED LOOPS  (cr=18 pr=0 pw=0 time=4217 us)
          3     VIEW  (cr=14 pr=0 pw=0 time=3887 us)
          3      HASH JOIN SEMI (cr=14 pr=0 pw=0 time=3865 us)
        100       TABLE ACCESS FULL TEST_T3 (cr=7 pr=0 pw=0 time=308 us)
          6       TABLE ACCESS FULL MLOG$_TEST_T3 (cr=7 pr=0 pw=0 time=105 us)
          2     TABLE ACCESS BY INDEX ROWID TEST_T2 (cr=4 pr=0 pw=0 time=120 us)
          2      INDEX RANGE SCAN TEST_T2_J2_3_IDX (cr=2 pr=0 pw=0 time=67 us)(object id 60236)
          2    INDEX RANGE SCAN TEST_T1_J1_2_IDX (cr=2 pr=0 pw=0 time=43 us)(object id 60230)
    

    Hash join semi. Where i have problem uses hash join right semi. What is different between them ? This i think is causing performace issue in my case

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  NESTED LOOPS  (cr=55325 pr=53317 pw=0 time=22980681 us)
          1   NESTED LOOPS  (cr=55324 pr=53317 pw=0 time=22980657 us)
          1    VIEW  (cr=55321 pr=53317 pw=0 time=22980562 us)
          1     HASH JOIN RIGHT SEMI (cr=55321 pr=53317 pw=0 time=22980548 us)
          2      TABLE ACCESS FULL MLOG$_S_R (cr=3 pr=0 pw=0 time=135 us)
    8264128      TABLE ACCESS FULL S_R (cr=55318 pr=53317 pw=0 time=8272772 us)
          1    TABLE ACCESS BY INDEX ROWID SERVICE_REQUESTS (cr=3 pr=0 pw=0 time=86 us)
          1     INDEX UNIQUE SCAN xxxx_PK (cr=2 pr=0 pw=0 time=40 us)(object id 52121)
          1   INDEX UNIQUE SCAN PK_Sxxxxx (cr=1 pr=0 pw=0 time=12 us)(object id 87853)
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        5      0.01       0.01          0          0          0           0
    Execute      5     50.73      61.04     266390     276650         80           5
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       10     50.74      61.06     266390     276650         80           5
    

    Table and index name are changed due to policy. So, i think most time is spent on hash join right semi. Can you please guide me how to improve this

  6. Alberto Dell'Era

    Wednesday, January 6, 2010

    @Taral

    I concurr on the performance problem being the Hash Join; there your statement spends the vast majority of time (22980548 microseconds), that is almost completely CPU or unaccounted-for time (since the two child FTS accounts for only 135+8272772 microseconds).

    About the question about the semi-join, you have a SQL semi-join since a fragment of the statement used for the INS phase of the fast refresh is

    select .. from test_t1 where rowid in ( select … from mlog$_test_t1 )

    There are (conceptually) two ways to calculate this fragment using an hash table:

    a) load test_t1 as an hash table “in memory”, then read mlog$_test_t1, and mark the rows in the hash table that match; then, return the marked rows

    b) load mlog$_test_t1 as an hash table “in memory”, then read test_t1; whenever a match occurs, return the row, and mark the hash table row as “already returned”, and never return it again even if another match occurs [or, simply remove the row from the hash table].

    I’m almost sure that (a) is how an “HASH JOIN SEMI” works, and (b) is how an “HASH JOIN RIGHT SEMI” works, even if I am only 95% sure right now. If that’s true, in the common scenario where the log contains only a few rows and the table a lot of rows (as it seems to be your case), (b) would seem to be the more efficient way (small hash table).

    It would be interesting to compare two tkprofs of the fragment, one using (a) and another (b) (you can use the swap_join_inputs and no_swap_join_inputs to force one or another), and see whether it makes any difference.

    Of course one would argue that the most efficient way to compute that fragment would be to get the table rows “pointed to” by the log rowids, thus avoiding a very expensive FTS, not by an expensive hash join. The hash join was chosen in my tests because the tables were tiny; in general, I do not expect that path to be chosen very often.

    Might you check that the CBO was working with up-to-date information, by checking that
    a) table S_R had up-to-date statistics collected
    and
    b) the log MLOG$_S_R had either up-to-date statistics or no statistics (both scenarios are possible).

  7. Taral Desai

    Thursday, January 7, 2010

    Thanks Sir for update and also explaining both things. As usual i learned many things from you today thank you for that. Now, coming to the issue this is actually a bug and i tested using the method provide in document 578720.1 and now it’s using path

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.01       0.00          0          8          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.02       0.01          0          8          0           0
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  NESTED LOOPS  (cr=8 pr=0 pw=0 time=432 us)
          1   NESTED LOOPS  (cr=7 pr=0 pw=0 time=402 us)
          1    VIEW  (cr=4 pr=0 pw=0 time=269 us)
          1     NESTED LOOPS  (cr=4 pr=0 pw=0 time=265 us)
          1      SORT UNIQUE (cr=3 pr=0 pw=0 time=235 us)
          2       TABLE ACCESS FULL MLOG$_xxxx (cr=3 pr=0 pw=0 time=143 us)
          1      TABLE ACCESS BY USER ROWID S_XXXX (cr=1 pr=0 pw=0 time=23 us)
          1    TABLE ACCESS BY INDEX ROWID S_XXXX (cr=3 pr=0 pw=0 time=127 us)
          1     INDEX UNIQUE SCAN SXXXXX_PK (cr=2 pr=0 pw=0 time=55 us)(object id 52121)
          0   INDEX UNIQUE SCAN PK_S_XXXXX (cr=1 pr=0 pw=0 time=25 us)(object id 87853)
    
  8. Alberto Dell'Era

    Thursday, January 7, 2010

    @Taral

    thanks for coming back and letting me know about your solution.

Leave a Comment

Please enclose SQL code inside the tag pair [sql] ... [/sql]

Subscribe without commenting

Links (alphabetical order)

Blogroll (alphabetical order)

Blog Aggregators (alphabetical order)


Switch to our mobile site