fast refresh of single-table materialized views – algorithm summary

Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single master table, containing no aggregate but, at most, filter predicates and additional column definitions:

create materialized view test_mv
build immediate
refresh fast on demand
with rowid
-- with primary key
as
select test_t1.*, x1+x2 as x1x2
  from test_t1
 where x1 != 0.42;

This kind of MVs might be considered a degenerate case of a join-only MV, a topic that we investigated in an earlier post, and one could expect the same algorithm. But that is not the case: the test case shows that the algorithm used is very different.
The two main differences are (as we are going to illustrate in detail) that UPDATEs are actually used in this case (as noted by Cristian Cudizio) instead of DELETE+INSERT only, and especially that row-by-row propagation is performed instead of using a couple of single SQL statements.

This kind of MV is frequently used for replication across a db-link (with a clause such as "from test_t1@db_link"); in this scenario, the MV used to be named SNAPSHOT in old releases. I have checked this scenario as well (not included in the test case) and the only difference is that, obviously, the master table test_t1 is referenced via a db-link and a few hints are injected by the refreshing engine.

In the test case, I have checked both the WITH ROWID and WITH PRIMARY KEY options for the MV DDL; the algorithm turns out as being identical, besides (obviously) that in the former the rowid and in the latter the primary key is used to identify rows.

I am going to follow the path of the previous discussion about join-only MVs referenced above, as both the test case format and some of the actual refresh steps are very similar. I have tested on 9.2.0.8, 10.2.0.4 and 11.1.0.7 for the most common DML on the base table (conventional INSERTs, UPDATEs and DELETEs). I have seen no difference in the algorithm for the three kernel versions.

Materialized view logs configuration

Even for this test case, I have configured the materialized view logs to "log everything" to check whether Oracle is able to take advantage of more information in the log:

create materialized view log on test_t1
with sequence, rowid, primary key (x1, x2)
including new values;

but even for single-table MVs the algorithm uses only the rowid or primary key information, hence the minimal (and hence optimal) log configuration is, for the WITH ROWID option:

create materialized view log on test_t1 with rowid;
 

and for the WITH PRIMARY KEY option:

create materialized view log on test_t1 with primary key;
 

Log snapshots

The first step in the refresh algorithm is to take a log snapshot, exactly as in the join-only case, by setting snaptime$$ = current time. Hence the marked log rows (the ones and only ones to consider for propagation) will be characterized by snaptime$$ <= current time and > last snapshot refresh time. See the previous post about the join-only case for a more in-depth discussion.

Note: actually, for the sake of precision, two (minor) differences with the join-only case are that the snapshot statement is exactly the same in all versions (there's no special version for 11.1.0.7) and that the log is not "inspected to count the number and type of the logged modifications".

Core algorithm: the DELETE and UPSERT steps

Then, the core replication starts. The propagation from the master table is composed of two simple steps, steps that I've named DELETE and UPSERT (UPDate + insERT).

The first DELETE step is a simple select-then-delete row-by-row processing, where each row returned by a select statement is passed to a single-row delete statement.
For the WITH ROWID option, the select statement of the DELETE step is (editing for readability: removing hints, unnecessary aliases, etc):

select distinct m_row$$
  from (
select m_row$$
  from mlog$_test_t1
 where snaptime$$ > :1
   and dmltype$$ != 'I'
       ) log
 where m_row$$ not in
       (
select rowid from test_t1 mas
 where (mas.x1 <> 0.42)
   and mas.rowid = log.m_row$$
       );

and the delete is a trivial

delete from test_mv where m_row$$ = :1;

The select+delete purpose is to delete all marked rows that are not in the master table anymore, or that are still there but that do not satisfy the MV defining SQL (here, x1 != 0.42) anymore.

In fact, the first in-line view fetches from the log the rowid of a subset (those whose dmltype$$ != 'I') of the marked rows, since :1 is set to the date of the previous refresh of the materialized view. Well actually - the SQL, as it is, would also get the log rows inserted after the snapshot was taken, which is obviously not acceptable since the propagation must operate on a stable set of rows. I'm not sure how the non-marked rows are excluded, but probably the various "select for update" on the log data dictionary tables might play a role by locking the commits on the logs, or maybe the serialization level is set to read-only or serializable (I will investigate this in the future). For now, let's make the conjecture that only the marked rows are selected.

The last correlated subquery simply filters out the rowid of the rows that are still in the master table. The condition dmltype$$ != 'I' ('I' stands for INSERT) is only an optimization, since an inserted row would be filtered out by the subquery anyway - unless it has not been deleted after being inserted, but that would be recorded with another log row with dmltype$$ = 'D'.

Why are updates (dmltype$$ = 'U') not optimized away as well? This is to delete rows from the MV that no longer belong to the current image of the MV defining SQL statement, since they used to satisfy the filter condition (here, x1 != 0.42) but no longer do after an update. Thanks to the filter condition (x1 != 0.42) being included in the subquery, any row that does not satisfy it anymore after an update will not be filtered out, and hence will be deleted.

Note that column m_row$$ of the MV is a hidden (but not virtual) column that records, for each MV row, the rowid of the corresponding master table row. It is automatically created when you define the MV with the WITH ROWID option; an index is automatically created on m_row$$ as well (unless you specify USING NO INDEX, something that does not make sense if you want to fast refresh the MV). Hence you do not need to create any additional index, neither on the master table nor on the MV, to optimize this step of the fast refresh.

Switching to the WITH PRIMARY KEY option, the select statement of the DELETE step is

select distinct pk1
  from (
select pk1
  from mlog$_test_t1
 where snaptime$$ > :1
   and dmltype$$ != 'I')
       ) log
 where pk1 not in
       (
select pk1
  from test_t1 mas
 where (mas.x1 <> 0.42)
   and log.pk1 = mas.pk1
       );

and the delete is simply

delete from test_mv where pk1 = :1;

That is, the statements are the same as in the WITH ROWID case, with the primary key instead of the rowid in all statements. Since the master table must have a primary key for the MV create to succeed, and since an index on the MV that spans the primary key column(s) is automatically created (unless you specify USING NO INDEX of course), even in the WITH PRIMARY KEY case you do not need to create any additional index for performance. Actually, for best performance, an index on the master table that combines the PK and the column(s) referenced by the MV filter condition - here on (pk1, x1) - might help a bit, since probably the optimal plan is a nested loop having test_t1 as the inner table. This would avoid a block get on the master tables for marked rows not satisfying the MV filter condition; the effectiveness of this index depends on whether you have a lot of updates on the column referenced in the filter condition.

The UPSERT step is a simple select-then-upsert row-by-row processing, where each row returned by a select statement (that calculates the current image of the row that needs to be propagated to the MV) is used to update the corresponding row in the MV; if the update finds no row, the row is inserted.

For the WITH ROWID option, the select statement of the UPSERT step is:

select current.x1, current.x2, current.pk1, current.x1x2,
       rowidtochar (current.rowid) m_row$$
  from (
select x1, x2, pk1, x1+x2 as x1x2
  from test_t1
 where (x1 <> 0.42)
       ) current,
       (
select distinct m_row$$
  from mlog$_test_t1
 where snaptime$$ > :1
   and dmltype$$ != 'D'
       ) log
 where current.rowid = log.m_row$$;

and the update and insert statements are simply:

update test_mv set x1=:1, x2=:2, pk1=:3, x1x2 = :4 where m_row$$ = :5;
insert into test_mv (x1,x2,pk1,x1x2,m_row$$) values (:1,:2,:3,:4,:5);

The select+upsert purpose is to calculate the new image of all marked rows that satisfy the MV defining SQL filter condition (here, x1 != 0.42) and then overwrite the old image in the MV with the new one. Note that an update on the master table might produce an insert if the old image did not satisfy the filter condition and the new one does.

The structure of the select statement should be obvious after the previous illustration of the DELETE step. Note of course the different optimization in the second inline view (dmltype$$ != 'D'). Even in this case, the automatically created index on the m_row$$ MV column optimizes the update statement, and no other index is necessary for performance on neither the base table nor the MV.

Switching to the WITH PRIMARY KEY option, the select statement of the UPSERT step is

select current.x1, current.x2, current.pk1, current.x1x2
  from (
select x1, x2, pk1, x1+x2 x1x2
  from test_t1
 where (x1 <> 0.42)
       ) current,
       (
select distinct pk1
  from mlog_test_t1
 where snaptime$$ > :1
   and dmltype$$ != 'D'
       ) log
 where current.pk1 = log.pk1;

and the update and insert statements are:

update test_mv set x1=:1, x2=:2, pk1=:3, x1x2=:4 where pk1=:3;
insert into test_mv  (x1, x2, pk1, x1x2) values (:1, :2, :3, :4);

And the same considerations about the substitution of rowid with the primary key hold. The index on the master table on (pk1, x1) might be of help here as well.

So here it is what the algorithm, essentially, is all about: a row-by-row propagation of all the modified (marked) rows to the MV, with a few optimizations.

Algorithm optimizations

Whatever the type of modifications, the algorithm is always the same: both the DELETE and UPSERT step are performed in all cases. Of course, in both cases, the select statement might select no row.


  1. Blogroll Report 07/08/2009 – 14/08/2009 « Coskan’s Approach to Oracle

    Friday, August 14, 2009

    [...] Alberto Dell’Era – Fast refresh of single-table materialized views – algorithm summary [...]

  2. materialized views

    Sunday, April 4, 2010

    [...] enterprise by Dan Chak on … Sample Chapter: Materialized Views from Enterprise Rails (O’Reilly …Alberto Dell’Era’s Oracle blog fast refresh of single-table …Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single [...]

  3. Reiner Kuehl

    Wednesday, March 28, 2012

    Thanks Alberto for this post.
    We have recently started working with materialized views. Our scenario (using Oracle 11.2.0.3) is:
    We have a master table with more than 200.000.000 rows. We have created two MVs based on this master table only, no aggregates. The only where-clause is ‘column1 != 0′ for the first MV and ‘column2 in (9,11)’ for the second.
    The materialized view log does contain this two columns. Although recommended, I have made bad experience with commit-scn-based MV log, therefore using timestamp based MV log.
    Both MVs are defined ‘refresh fast on commit’.
    Best
    Reiner
    Now my question:
    When updating a row in the master table with ‘column1=0′ I wouldn’t expect any refresh action on the first MV. Can you explain why Oracle performs deletes and upserts on the first MV although the master table record is not relevant for this MV (due to column1=0)?

  4. Alberto Dell'Era

    Wednesday, March 28, 2012

    Reiner,
    can you build a simple test case to reproduce ?

  5. Reiner Kuehl

    Wednesday, April 4, 2012

    Thanks Alberto for your quick response.
    No test case yet. I have recreated the MV log and MVs. Now performance is very good. In addition, I have locked the statistics of the MV log (always empty – all MVs are refresh on commit) and set “_mv_refresh_use_stats” to TRUE.
    I have two similar systems. One with these two fixes and the other without. Let’s see whether the issue will occur again.

  6. Reiner Kuehl

    Tuesday, July 3, 2012

    Hi Alberto,
    I have build a test case and reproduced the issue. To keep it simple you can just modify the update statements of your own test case:

    update test_t1 set pk1 = -pk1 where pk1 = 1 and x1 = 0.42;
    update test_t1 set x1  = -x1  where pk1 = 2 and x1 = 0.42;
    delete from test_t1           where pk1 = 3 and x1 = 0.42;
    

    It is obvious that these statements should not lead to any modifications in the materialized views records (where x1 != 0.42).
    Nevertheless, the algorithm does not change and performs the same delete and upsert statements.
    You have the x1-column in your materialized view log definition but it is not used.

    Our problem is: We have thousands of such updates in our application. Due to this algorithm our application is slowed down. I am always seeing the materialized view related ‘Delete’ and ‘Upsert’ in my top-sql list.

  7. satish

    Saturday, December 7, 2013

    How do I know how many changes are happening on an mview i.e. how many updates, how many deletes and how many inserts ?
    Appreciate your response.

    - Satish

  8. Alberto Dell'Era

    Saturday, January 25, 2014

    Satish,

    sorry for the long delay, my notification module has gone berserk again ;(

    There’s nothing specific about MVs as far as I know. You can achieve what you want by inspecting all_tab_modifications, by taking the difference of the values before and after the MV refresh. Be sure to call dbms_stats.flush_database_monitoring_info before reading that view in order to have the most current values inside all_tab_modifications, since they are flushed from the SGA lazily, and not immediately after the modifications have been performed, for efficiency reasons.

  9. Alberto Dell'Era

    Saturday, January 25, 2014

    Moreover: if the refresh is complete and non-atomic, you are not going to see any delete, simply because a truncate happens instead of regular deletes during the refresh. There’s column “truncated” in all_tab_modifications in 11.2 that will tell you that a truncated happened (it will be increased by one).

  10. Iudith Mentzel

    Wednesday, March 19, 2014

    Hello Alberto,

    My name in Iudith Mentzel and I am an Oracle developer at Zim Integrated Shipping Services in Haifa, Israel.

    I am following for some longer time your very interesting posts regarding the Materialized views internals so, though this thread is an older one, I still would like to share with you my findings,
    in the hope that they will highlight some points that you may find interesting.

    In research of a specific case we have encountered, I performed a test using your test script single_table_mv.sql, running it in version 11.2.0.3.0.

    What I have found is very interesting, namely:

    For a local single table materialized view
    ( that is, running your script exactly as is )
    ————————————————————————
    Oracle performs indeed the 3 steps, but they look different than in the previous versions, as follows:

    – the log snapshot (which is the same – 6 rows updated in our test case)

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

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 6 1 6
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 2 0.00 0.00 0 6 1 6

    – the delete

    DELETE FROM "TW"."TEST_MV" SNAP$
    WHERE
     "PK1" IN (SELECT DISTINCT MLOG$."PK1" FROM "TW"."MLOG$_TEST_T1" MLOG$ WHERE
      "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I'))
    

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 9 9 3
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 2 0.00 0.00 0 9 9 3

    Here we see the first changed behavior:

    The DELETE is performed as a single SQL operation, but without the condition

    WHERE (LOG$.”PK1″) NOT IN
    (SELECT MAS_TAB$.”PK1″ FROM “TEST_T1″ “MAS_TAB$” WHERE (“MAS_TAB$”.”X1″0.42) AND LOG$.”PK1″ = MAS_TAB$.”PK1″)

    that appeared in the SELECT cursor for this step in the previous versions.

    This means that ALL the MLOG$ rows corresponding to updates of non-PK columns in the base table ( those logged with DMLTYPE$$=’U’ ) are deleted from the materialized view !

    This behavior is not a side-effect of the sample materialized view containing a filtering condition
    ( in the sample case: “where x1 != 0.42″ ).
    Exactly the same happens without a filtering condition, as I checked on a real-life case.

    In the previous versions the deletions were performed row-by-row, but this step DID NOT include
    rows for DMLTYPE$$=’U’ whose PK-s are still present in the base table.

    – the upsert

    This step is also performed in a single SQL MERGE statement, and not as row-by-row INSERT or UPDATE statements,
    as it happened in previous versions:

    MERGE INTO "TW"."TEST_MV" "SNA$" USING (SELECT CURRENT$."X1",CURRENT$."X2",
      CURRENT$."PK1",CURRENT$."X1X2" FROM (SELECT "TEST_T1"."X1" "X1",
      "TEST_T1"."X2" "X2","TEST_T1"."PK1" "PK1","TEST_T1"."X1"+"TEST_T1"."X2"
      "X1X2" FROM "TEST_T1" "TEST_T1" WHERE "TEST_T1"."X1"<>0.42) CURRENT$,
      (SELECT DISTINCT MLOG$."PK1" FROM "TW"."MLOG$_TEST_T1" MLOG$ WHERE
      "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."PK1" =
      LOG$."PK1")"AV$" ON ("SNA$"."PK1" = "AV$"."PK1") WHEN MATCHED THEN UPDATE
      SET "SNA$"."X1" = "AV$"."X1","SNA$"."X2" = "AV$"."X2","SNA$"."PK1" =
      "AV$"."PK1","SNA$"."X1X2" = "AV$"."X1X2" WHEN NOT MATCHED THEN INSERT
      (SNA$."X1",SNA$."X2",SNA$."PK1",SNA$."X1X2") VALUES (AV$."X1",AV$."X2",
      AV$."PK1",AV$."X1X2")
    

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.01 0.00 0 0 0 0
    Execute 1 0.00 0.01 4 19 35 3
    Fetch 0 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 2 0.01 0.01 4 19 35 3

    This MERGE will of course perform an INSERT for those updated rows that were additionally deleted in the “delete” step.

    In summary, for ALL the UPDATE-s performed on the master table, the refresh does perform a DELETE + INSERT on the materailzied view, and not just for the updates that changed the PK columns, as it happened in previous versions.

    I suppose that this may slow down the refresh performance, for cases where most of the DML activity consists in updating non-PK columns.

    It is not clear to my why Oracle decided in 11.2.0.3.0 that performing a DELETE+INSERT is “cheaper” than performing an UPDATE, for local materialized views anyway.

    For a remote single table materialized view
    ——————————————-
    I performed an identical test as the above one, but where the single table materialized view is located on a remote site ( the usual replication materialized view ).

    create materialized view test_mv_r
    build immediate
    refresh fast on demand
    with &mv_with.
    as
    select test_t1.*, x1+x2 as x1x2
      from test_t1@dblink
     where x1 != 0.42
    ;
    

    Here the DELETE and UPDATE steps were performed row-by-row, just like in the previous versions, and an UPDATE of a non-PK column is performed indeed as an UPDATE on the materialized view,
    and not as a DELETE + INSERT.

    Just for the sake of art, it would be interesting to compare the performance of a refresh of a local materialized view
    ( doing single SQL statements ) to that of an identical materialized view, but defined using a loop-back database link, to make it appear as a remote materialized view, so expected to refresh row-by-row.

    It would be very strange to find out that the remote materialized view refresh can be faster than that of a local one.

    ————————————————————————-
    A second issue is related to the refresh having to use a “consistent data set” from the MLOG$ table, as you have mentioned in the post itself.

    As per our conclusions, based on a specific case that lead to these investigations, this seems *NOT* to be the case, anyway not in 11.2.0.3.0.

    That is, the log snapshot step is performed, but looks like this is done *ONLY* for making the SNAPTIME$$ usable for further PURGE-s of the MLOG$ table after each refresh.

    Otherwise, for both the local materialized view and for the remote one, the access to the MLOG$ table only contains the

    “SNAPTIME$$ > :last_refresh_time” condition

    but *NOT* the

    “SNAPTIME$$ <= :current_snapshot_time" condition !

    This effectively means that, for tables with high DML activity, the new rows added to the MLOG$ *BETWEEN* the time of the "delete" step and the time of the "upsert" step ( and in fact also between the "log snapshot"
    step and the "delete" step ) are used by the current refresh, so the refresh DOES NOT use a strictly consistent data set !!!

    We discovered this as a result of a very specific case we have:
    A table has a PK composed of four significant columns, where at least two of them are subject to UPDATE-s, and also has a UK composed of a single surrogate column, which is never updated
    ( a bad enough design, in my opinion ).

    The table is very large and "wide", with many columns.

    So, we have a local materialized view on this table, whose only purpose is to contain ALL the rows, but only a low number of basic columns from the very many of the base table.

    The materialized view has the same PK and UK columns as the base table.

    The UK constraint is defined as DEFERRABLE, so it is enforced only at the last step of the refresh.

    However, we do encounter sometimes refresh failures as a result of UK violations, and, in most cases, after such a failure, the next refresh is successful, because it "corrects" the "mlog$ data set inconsistency"
    that I described above.

    For a "normal" table design, where PK columns are not updated at all or anyway not frequently, this problem might not become visible, and each refresh practically uses "more logged data" from the MLOG$ table
    than the data it marks in the log snapshot phase.

    Occasionaly, though rarely, we do encounter also FK violations in refresh groups where all the materialized views involved in the constraint are in the same refresh group, the FK is DEFERRABLE,
    and these violations also do usually "correct themselves" on the next refresh.

    It looks to me that for really using a "strictly consistent MLOG$ data set", we should go for performing the refresh using SERIALIZABLE transactions only.

    As far as I was aware, the UPDATE of the MLOG$ table performed in the "log snapshot" phase is committed immediately, to avoid blocking locks on refreshes of other materialized views that may use the same
    MLOG$ table.
    I don't see such a COMMIT statement in the trace file of the tests that I performed, though.

    However, I think that using SERIALIZABLE transactions will make at least the "delete" and "upsert" steps see a consistent set of the MLOG$ data, though not necessarily consistent with the set seen by the "log snapshot" step, if that step is indeed committed as a separate transaction.

    In your original post you mentioned that you would investigate this "consistent data set" issue further,
    I would be very glad to read your conclusions.

    Again, all your posts are GREAT, GREAT, GREAT !

    Thanks a lot, TANTE GRAZIE !

    Iudith Mentzel
    ZIM Integrated Shipping Services Ltd.
    Haifa, Israel

  11. Alberto Dell'Era

    Saturday, March 22, 2014

    Iudith,

    a) first of all, many thanks for your kind words – I really appreciate that you find my work interestingly and have spent your time to let me know :)

    b) As an curious coincidence, I have actually noticed, a couple of weeks ago, that algorithm change in 11.2, and I’m very happy that you have described it here so that it can be shared, and I can further comment on it sharing my observations as well.

    I totally agree that this change can make the refresh much more expensive, especially if you have some indexes on the MV – in this (very common) scenario, when the update on the master table is propagated as a delete+insert, the corresponding entry on the indexes must be first removed and then re-inserted, even if the indexed values have not changed(!); in the old version of the algorithm, an update to the same value would have not triggered any index modification (since the kernel always checks the before/after value before considering changing the index).

    Worst, the delete+insert happens even if you update a row that is not referenced by the MV definition(!). The information about what columns have been changed are in the log, in the column CHANGE_VECTOR$$, which gets completely ignored.

    Getting two visits for each index, and for every updated rows, is going to be a bit expensive …

    c) I would conjure that the old algorithm was simply the SNAPSHOT’s one turned local, and that now development has “optimized” it to conform to the same strategy used by “join” and “group-by” MVs, that are mostly based on delete+insert.

    Your idea of using a loopback db-link looks promising – of course YMMV, but it is a nice idea to try.

    d) your description of the refresh not using a “strictly consistent MLOG$ data set” looks like a very bad bug to me, of the “wrong result” kind (the most severe one), since it makes appear in the MV a dataset that has never existed.
    In my opinion, the developers simply forgot to add the “as of snapshot(:b_scn)” clause that is present on all other kind of (local) MVs refreshes.

    e) about the commit not showing after the “log snapshot” – I notice that you use TKPROF to read the trace; have you tried inspecting it directly? Usually the kernel does not submit commit statements, but a “commit” api call instead, that TKPROF ignores since it is not associated to any statement.
    The XCTEND line traces the transaction end in both cases.
    For further info, check this great post by Christian Antognini:
    http://antognini.ch/2009/08/synthetic-commits-and-rollbacks/

    f) it looks like that you are trying to implement what I call a “mirror MV”, to just select only the column and rows you are interested into, without any data transformation. We are trying the same strategy, in our case using a custom trigger instead of the MV which is MUCH LESS costly – since a MV log implicitly installs a (kernel) trigger as well, and hence the very same optimizations that are disabled by the trigger are disabled by the MV log anyway.
    We loose the query rewrite though …
    I might blog about this “in the future” ;) but contact me at alberto.dellera@gmail.com if interested.

    Ciao!
    Alberto

  12. Iudith Mentzel

    Monday, March 24, 2014

    Hello Alberto,

    Thanks a lot for your so detailed answer, you are so kind :)

    Following Christian Antognini’s post, I looked into the trace file itself, and found there sereval lines with “XCTEND rlbk=0″.

    Two of them appear after the “DBMS_MVIEW.REFRESH” cursor and after a recursive “UPDATE DBMS_LOCK_ALLOCATED” statement issued on behalf
    of the REFRESH call.
    For each of these, an explicit cursor performing a COMMIT is added and there is also an explicit COMMIT appearing in the file after being processed by TKPROF.

    Several other “XCTEND rlbk=0″ rows appear in the trace file following recursive SELECT-s and UPDATE-s performed on internal mview maintenance tables like SYSTEM.DEF$_DESTINATION, SUMPARTLOG$, SUMDETAIL$, and these synthetic commits do not have explicit COMMIT-s included in the file processed by TKPROF.

    I guess that one of these “synthetic commits” should be the one that also commits the UPDATE issued during the “log snapshot” phase.

    Anyway, in the meantime I tried to perform the refresh using SERIALIZABLE transactions, by issuing the following:

    alter session set isolation_level = serializable;
    
    exec dbms_mview.refresh ( list => 'test_mv', method => 'f', atomic_refresh => true);
    

    Unfortunately, however, it failed with the following error:

    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-08177: can’t serialize access for this transaction
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2563
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2776
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2745
    ORA-06512: at line 1

    For the test case (based on your original test script, with the above ALTER SESSION added), it looks from the trace file that id DID NOT perform any of the 3 refresh steps, but rather failed before that.

    For my real case, though I got ultimately the same ORA-08177 error, the “log snaphshot” and “delete” phases statements still appear in the trace file, but the “upsert” step does not appear, so we can probably conclude that the error happened during the DELETE phase, though this is very unlikely logically, because the materialized view table is only updated by the refresh, and not by any other sessions or processes.

    Also, my tests were all performed in an environment where no other refreshes of other materialized views were running in other sessions.

    I was very confident that by using serializable transactions the refresh could work, except for possibly receiving an ORA-01555 error if the refresh takes too long and the MLOG$ table cannot be read consistently across the “delete” and “update” phases.

    But, unluckily, this is NOT the case, it looks like there are many other “internal subtleties” that prevent a serializable transaction to be suitable for this scenario.

    If the ORA-08177 did happen indeed as a result of one of the recursive operations performed during the refresh on one of the SYS tables, then this might be a problem when other materialized views are refreshing simultaneously, because all these refreshes do update the same SYS tables.

    Using an explicit trigger could have been an option, of course, but since Oracle introduced the “kernelized” trigger that performs the changes to the MLOG$ tables, it always seemed (and Oracle also stated it explicitly) that this internal trigger is more efficcient than the previous “external” trigger used by older versions.

    As you said, the MLOG$ data set inconsistency looks as a very severe problem.

    It also exists for replication materialized views containing db-links, so it was probably “borrowed” from there, where I guess it always worked like this, in previous versions as well, because I can hardly believe that somebody just “volunteered” to remove the “as of snapshot(:b_scn)”, if it was already there …

    I will try now to see how I can cope with the real-life problem by trying to disable and reenable the UK constraint that prevents the refresh from being successful.

    Reenabling the constraint after the refresh may be time-consumig, but, instead, I would spare the time spent by having to repeat the refresh several times after UK violation failures, in an attempt to reach a consistent MLOG$ data set.

    I am glad that you confirmed my supposition regarding the inefficiency of propagating UPDATE-s as “DELETE+INSERT”, you are perfectly right, there are also many indexes on the table and probably many of the updates are for non-indexed columns … the refresh does indeed take a long time.

    Probably using a loop-back db-link would have been faster, though, the MLOG$ data inconsistency should still be handled using some trick in this particular case.

    I am always glad to read your blogs on any of these and other topics :):)

    Some years ago I also followed passionately after your blogs and papers related to histogram internals, and I was also amazed :):)

    Yes, I definitely find your deep explorations as very valuable, and very clearly explained and I am delighted that you also took the time to blog about your findings in so much detail and with so many examples.

    Keep up with the GREAT WORK !

    Ciao e tante grazie ancora una volta !

    Iudith Mentzel
    Haifa, Israel

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