Materialized Views: how can I find the number of updates, inserts, and deletes applied during refresh?

Question

I have a data mart mastered from our OLTP Oracle database using basic Materialized Views with on demand fast refresh capability. Refresh is working fine. What I am interested in adding are some statistics about the refresh of each Materialized View, such as the number of inserts, updates, and deletes that were applied to the master table since the last refresh like that data I can find in user_tab_modifications. Is this possible for Materialized Views?

Answer 1

Prior to doing the refresh, you could query the materialized view log to see what sort of change vectors it stores. Those will be the change vectors that need to be applied to the materialized view during the refresh process (assuming that there is just one materialized view that depends on this materialized view log).

For example, if I create my table, my materialized view log, and my materialized view.

SQL> create table foo( col1 number primary key);

Table created.

SQL> create materialized view log on foo;

Materialized view log created.


SQL> ed
Wrote file afiedt.buf

  1  create materialized view mv_foo
  2    refresh fast on demand
  3  as
  4  select *
  5*   from foo
SQL> /

Materialized view created.

SQL> insert into foo values( 1 );

1 row created.

SQL> insert into foo values( 2 );

1 row created.

SQL> commit;

Commit complete.

Now, I refresh the materialized view and verify that the table and the materialized view are in sync

SQL> exec dbms_mview.refresh( 'MV_FOO' );

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name = 'MV_FOO';

no rows selected

SQL> select * from foo;

      COL1
----------
         1
         2

SQL> select * from mv_foo;

      COL1
----------
         1
         2

Since the two objects are in sync, the materialized view log is empty (the materialized view log will be named MLOG$_<<table name>>

SQL> select * from mlog$_foo;

no rows selected

Now, if I insert a new row into the table, I'll see a row in the materialized view log with a DMLTYPE$$ of I indicating an INSERT

SQL> insert into foo values( 3 );

1 row created.

SQL> select * from mlog$_foo;

      COL1 SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
     XID$$
----------
         3 01-JAN-00 I N
FE
2.2519E+15

So you could do something like this to get the number of pending inserts, updates, and deletes.

SELECT SUM( CASE WHEN dmltype$$ = 'I' THEN 1 ELSE 0 END ) num_pending_inserts,
       SUM( CASE WHEN dmltype$$ = 'U' THEN 1 ELSE 0 END ) num_pending_updates,
       SUM( CASE WHEN dmltype$$ = 'D' THEN 1 ELSE 0 END ) num_pending_deletes
  FROM mlog$_foo

Once you refresh the materialized view log, however, this information is gone.

On the other hand, USER_TAB_MODIFICATIONS should track the approximate number of changes that have been made to the materialized view since the last time that statistics were gathered on it just as it would track the information for a table. You'll almost certainly need to call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to force the data to be made visible if you want to capture the data before and after the refresh of the materialized view.

SELECT inserts, updates, deletes
  INTO l_starting_inserts,
       l_starting_updates,
       l_starting_deletes
  FROM user_tab_modifications
 WHERE table_name = 'MV_FOO';

dbms_mview.refresh( 'MV_FOO' );
dbms_stats.flush_database_monitoring_info;

SELECT inserts, updates, deletes
  INTO l_ending_inserts,
       l_ending_updates,
       l_ending_deletes
  FROM user_tab_modifications
 WHERE table_name = 'MV_FOO';

l_incremental_inserts := l_ending_inserts - l_starting_inserts;
l_incremental_updates := l_ending_updates - l_starting_updates;
l_incremental_deletes := l_ending_deletes - l_starting_deletes;