Oracle Scratchpad

March 26, 2015

12c MView refresh

Filed under: 12c,Infrastructure,Materialized view,Oracle — Jonathan Lewis @ 1:19 pm BST Mar 26,2015

Some time ago I wrote a blog note describing a hack for refreshing a large materialized view with minimum overhead by taking advantage of a single-partition partitioned table. This note describes how Oracle 12c now gives you an official way of doing something similar – the “out of place” refresh.

I’ll start by creating a matieralized view and creating a couple of indexes on the resulting underlying table; then show you three different calls to refresh the view. The materialized view is based on all_objects so it can’t be made available for query rewrite (ORA-30354: Query rewrite not allowed on SYS relations) , and I haven’t created any materialized view logs so there’s no question of fast refreshes – but all I intend to do here is show you the relative impact of a complete refresh.


create materialized view mv_objects nologging
build immediate
refresh on demand
as
select
        *
from
        all_objects
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'mv_objects',
		method_opt 	 => 'for all columns size 1'
	);
end;
/

create index mv_obj_i1 on mv_objects(object_name) nologging compress;
create index mv_obj_i2 on mv_objects(object_type, owner, data_object_id) nologging compress 2;

This was a default install of 12c, so there were about 85,000 rows in the view. You’ll notice that I’ve created all the objects as “nologging” – this will have an effect on the work done during some of the refreshes.

Here are the three variants I used – all declared explicitly as complete refreshes:


begin
	dbms_mview.refresh(
		list			=> 'MV_OBJECTS',
		method			=> 'C',
		atomic_refresh		=> true
	);
end;
/

begin
	dbms_mview.refresh(
		list			=> 'MV_OBJECTS',
		method			=> 'C',
		atomic_refresh		=> false
	);
end;
/

begin
	dbms_mview.refresh(
		list			=> 'MV_OBJECTS',
		method			=> 'C',
		atomic_refresh		=> false,
		out_of_place		=> true
	);
end;
/

The first one (atomic_refresh=>true) is the one you have to use if you want to refresh several materialized views simultaneously and keep them self consistent, or if you want to ensure that the data doesn’t temporarily disappear if all you’re worried about is a single view. The refresh works by deleting all the rows from the materialized view then executing the definition to generate and insert the replacement rows before committing. This generates a lot of undo and redo – especially if you have indexes on the materialized view as these have to be maintained “row by row” and may leave users accessing and applying a lot of undo for read-consistency purposes. An example at a recent client site refreshed a table of 6.5M rows with two indexes, taking about 10 minutes to refresh, generating 7GB of redo as it ran, and performing 350,000 “physical reads for flashback new”. This strategy does not take advantage of the nologging nature of the objects – and as a side effect of the delete/insert cycle you’re likely to see the indexes grow to roughly twice their optimal size and you may see the statistic “recursive aborts on index block reclamation” climbing as the indexes are maintained.

The second option (atomic_refresh => false) is quick and efficient – but may result in wrong results showing up in any code that references the materialized view (whether explicitly or by rewrite). The session truncates the underlying table, sets any indexes on it unusable, then reloads the table with an insert /*+ append */. The append means you get virtually no undo generated, and if the table is declared nologging you get virtually no redo. In my case, the session then dispatched two jobs to rebuild the two indexes – and since the indexes were declared nologging the rebuilds generated virtually no redo. (I could have declared them with pctfree 0, which would also have made them as small as possible).

The final option is the 12c variant – the setting atomic_refresh => false is mandatory if we want  out_of_place => true. With these settings the session will create a new table with a name of the form RV$xxxxxx where xxxxxx is the hexadecimal version of the new object id, insert the new data into that table (though not using the /*+ append */ hint), create the indexes on that table (again with names like RV$xxxxxx – where xxxxxx is the index’s object_id). Once the new data has been indexed Oracle will do some name-switching in the data dictionary (shades of exchange partition) to make the new version of the materialized view visible. A quirky detail of the process is that the initial create of the new table and the final drop of the old table don’t show up in the trace file  [Ed: wrong, see comment #1] although the commands to drop and create indexes do appear. (The original table, though it’s dropped after the name switching, is not purged from the recyclebin.) The impact on undo and redo generation is significant – because the table is empty and has no indexes when the insert takes place the insert creates a lot less undo and redo than it would if the table had been emptied by a bulk delete – even though the insert is a normal insert and not an append; then the index creation honours my nologging definition, so produces very little redo. At the client site above, the redo generated dropped from 7GB to 200MB, and the time dropped to 200 seconds which was 99% CPU time.

Limitations, traps, and opportunities

The manuals say that the out of place refresh can only be used for materialized views that are joins or aggregates and, surprisingly, you actually can’t use the method on a view that simply extracts a subset of rows and columns from a single table.  There’s a simple workaround, though – join the table to DUAL (or some other single row table if you want to enable query rewrite).

Because the out of place refresh does an ordinary insert into a new table the resulting table will have no statistics – you’ll have to add a call to gather them. (If you’ve previously been using a non-atomic refreshes this won’t be a new problem, of course). The indexes will have up to date statistics, of course, because they will have been created after the table insert.

The big opportunity, of course, is to change a very expensive atomic refresh into a much cheaper out of place refresh – in some special cases. My client had to use the atomic_refresh=>true option in 11g because they couldn’t afford to leave the table truncated (empty) for the few minutes it took to rebuild; but they might be okay using the out_of_place => true with atomic_refresh=>false in 12c because:

  • the period when something might break is brief
  • if something does go wrong the users won’t get wrong (silently missing) results, they’ll an Oracle error (probably ORA-08103: object no longer exists)
  • the application uses this particular materialized view directly (i.e. not through query rewrite), and the query plans are all quick, light-weight indexed access paths
  • most queries will probably run correctly even if they run through the moment of exchange

I don’t think we could guarantee that last statement – and Oracle Corp. may not officially confirm it – and it doesn’t matter how many times I show queries succeeding but it’s true. Thanks to “cross-DDL read-consistency” as it was called in 8i when partition-exchange appeared and because the old objects still exist in the data files, provided your query doesn’t hit a block that has been overwritten by a new object, or request a space management block that was zero-ed out on the “drop” a running query can keep on using the old location for an object after it has been replaced by a newer version. If you want to make the mechanism as safe as possible you can help – put each relevant materialized view (along with its indexes) into its own tablespace so that the only thing that is going to overwrite an earlier version of the view is the stuff you create on the next refresh.

 

16 Comments »

  1. Hi Jonathan,

    A quirky detail of the process is that the initial create of the new table and the final drop of the old table don’t show up in the trace file, although the commands to drop and create indexes do appear.

    I investigated this issue further, would like to share my findings.
    I enabled sql_trace for the session.
    CREATE TABLE RV$xxxxxx command was truncated:

    PARSING IN CURSOR #18446744071423187152 len=465 dep=1 uid=199 oct=1 lid=199 tim=37967112688496 hv=3514996935 ad='4467b0720' sqlid='28sa5sg8s5667'
    /* MV_REFRESH (CTB) *//* 
    END OF STMT
    PARSE #18446744071423187152:c=23385,e=23386,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=37967112688491
    EXEC #18446744071423187152:c=4382990,e=5375136,p=288,cr=4690,cu=226,mis=0,r=0,dep=1,og=1,plh=0,tim=37967118064399
    CLOSE #18446744071423187152:c=38,e=38,dep=1,type=0,tim=37967118066586
    

    But DROP TABLE is not:

    PARSING IN CURSOR #18446744071423187152 len=49 dep=1 uid=199 oct=12 lid=199 tim=37967139195946 hv=4044603557 ad='ffffffff77e1b9e0' sqlid='946xzavsj7h55'
    /* MV_REFRESH (DTB) */ drop table "TC"."RV$7C4E1"
    END OF STMT
    PARSE #18446744071423187152:c=2773,e=2773,p=0,cr=0,cu=1,mis=1,r=0,dep=1,og=1,plh=0,tim=37967139195940
    EXEC #18446744071423187152:c=4822349,e=5313100,p=92,cr=5382,cu=56,mis=0,r=0,dep=1,og=1,plh=0,tim=37967144510087
    CLOSE #18446744071423187152:c=18,e=18,dep=1,type=0,tim=37967144510816
    

    We could use event 10979 to obtain whole command.
    After I enabled event 10979 at level 1, additional information about refresh was written to the trace file.
    Such as:

    [MV REFRESH Executed]: 
    /* MV_REFRESH (CTB) *//* MV_REFRESH (CTB) */ CREATE TABLE "TC"."RV$7C4E1" 
       (    "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE, 
            "OBJECT_TYPE" VARCHAR2(23)
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS NOLOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" 
    [MV REFRESH Executing]:Complete:Out-Place:Insert
    

    As far as I see, SYS.SNAP_REFOP$ table is populated with and without event 10979 set:
    [spoiler]

    SQL> select operation#, sql_txt from sys.snap_refop$ where sowner='TC' and vname='MV_T';
    
    OPERATION# SQL_TXT
    ---------- --------------------------------------------------------------------------------
             7 INSERT INTO "TC"."MV_T" select object_name,
                      object_type
                 from t
                where owner='PUBLIC'
    
            21 /* MV_REFRESH (CTB) */ CREATE TABLE "TC"."%s"
                  ("OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,
                " OBJECT_TYPE" VARCHAR2(23)
                  ) SEGMENT CREATION IMMEDIATE
                 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
                NOCOMPRESS NOLOGGING
                 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                 TABLESPACE "USERS"
    
            22 INSERT INTO "TC"."%s"  select object_name,
                      object_type
                 from t
                where owner='PUBLIC'
    

    [/spoiler]

    Best regards,
    Mikhail.

    Comment by Velikikh Mikhail — March 27, 2015 @ 3:18 pm BST Mar 27,2015 | Reply

    • Mikhail,

      Thank you for those notes – I’m irritated that I didn’t think that the ‘create table’ might have been truncated, all I did was search the trace file for “create” and, when that failed, for the table name ;(

      Thanks also for the comments about 10979 and snap_refop$ – very useful.

      Comment by Jonathan Lewis — March 27, 2015 @ 3:30 pm BST Mar 27,2015 | Reply

  2. Jonathan,
    could you think of a reason why the insert in the out_of_place case is not done with the append hint? I fail to see it – but on the other hand I could not imagine a reason to exclude subsets of single tables from the new refresh option…

    Maybe I would still prefer the single-partition workaround outlined in your older article.

    Regards

    Martin

    Comment by Martin Preiss — March 28, 2015 @ 3:11 pm BST Mar 28,2015 | Reply

    • Martin,

      I can’t think of any good reason at present.
      Perhaps it has something (unspecified) to do with a future enhancement.

      Comment by Jonathan Lewis — April 1, 2015 @ 8:07 pm BST Apr 1,2015 | Reply

      • Hello,

        I suppose a patch for Bug 20933264 : OUT-OF-PLACE COMPLETE REFRESH NOT USING DIRECT LOAD IN POPULATING OUTSIDE TABLE has not been mentioned here yet.
        The patch adds direct load capability to out of place refresh to populate an interim table:

        PARSING IN CURSOR #140349705981072 len=112 dep=1 uid=169 oct=2 lid=169 tim=2941165585643 hv=753910940 ad='7e2456a8' sqlid='gp1pxhhqfzj4w'
        INSERT /*+ APPEND */ INTO "TC"."RV$18AE6"  select object_name,
               object_type
          from t
         where owner='PUBLIC'
        END OF STMT
        ...
        skip
        ...
        STAT #140349705981072 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD AS SELECT  (cr=3355 pr=21 pw=199 time=849449 us)'
        STAT #140349705981072 id=2 cnt=37028 pid=1 pos=1 obj=0 op='OPTIMIZER STATISTICS GATHERING  (cr=2639 pr=21 pw=0 time=44296 us cost=353 size=173124 card=4122)'
        STAT #140349705981072 id=3 cnt=37028 pid=2 pos=1 obj=101093 op='TABLE ACCESS FULL T (cr=1270 pr=0 pw=0 time=14389 us cost=353 size=173124 card=4122)'
        

        The usage of APPEND hint can also be seen using SYS.SNAP_REFOP$ table:

        SQL> select operation#, sql_txt from sys.snap_refop$ where sowner='TC' and vname='MV_T';
        
        OPERATION# SQL_TXT
        ---------- --------------------------------------------------------------------------------
        ...
        skip
        ...
        
                22 INSERT /*+ APPEND */ INTO "TC"."%s"  select object_name,
                          object_type
                     from t
                    where owner='PUBLIC'
        

        Comment by mvelikikh — August 3, 2016 @ 6:31 am BST Aug 3,2016 | Reply

  3. Jonathan,

    [q]
    you actually can’t use the method on a view that simply extracts a subset of rows and columns from a single table.
    There’s a simple workaround, though – join the table to DUAL (or some other single row table if you want to enable query rewrite).
    [/q]

    I suppose this is a documentation bug.
    My experiment showed that we can do it.
    A test case is below:

    create table t as 
    select * 
      from all_objects;
    
    create materialized view mv_t nologging
    build immediate
    refresh on demand
    enable query rewrite
    as
    select object_name, 
           object_type
      from t
     where owner='PUBLIC'
    /
    
    exec dbms_stats.gather_table_stats( '', 'mv_t')
    
    select last_analyzed, num_rows from tabs where table_name='MV_T';
    
    alter session set events 'sql_trace bind=true';
    
    exec dbms_mview.refresh( -
           list           => 'MV_T', -
           method         => 'C', -
           atomic_refresh => false, -
           out_of_place   => true)
    
    DOC
      MV_T container table was not analyzed after refresh, so
      out of place refresh was perfomed.
      The raw trace file confirms it.
      Also we could use SYS.MV_REFRESH_USAGE_STATS$ as a proof of out of place refresh.
    #
    
    select last_analyzed, num_rows from tabs where table_name='MV_T';
    
    explain plan for 
    select object_type, count(*) 
      from t 
     where owner='PUBLIC' 
     group by object_type;
    
    select /*+ gather_plan_statistics*/
           object_type, count(*) 
      from t 
     where owner='PUBLIC' 
     group by object_type;
    
    select plan_table_output from table(dbms_xplan.display_cursor( format=> 'rowstats last'));
    

    An out of place refresh is performed.
    The query rewrite works as expected:

    SQL> select plan_table_output from table(dbms_xplan.display_cursor( format=> 'rowstats last'));
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------
    SQL_ID  60whw88yfs0sf, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics*/object_type, count(*)   from t
    where owner='PUBLIC'  group by object_type
    
    Plan hash value: 4116476928
    
    -------------------------------------------------------------------------
    | Id  | Operation                     | Name | Starts | E-Rows | A-Rows |
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |      |      1 |        |      1 |
    |   1 |  HASH GROUP BY                |      |      1 |  38788 |      1 |
    |   2 |   MAT_VIEW REWRITE ACCESS FULL| MV_T |      1 |  38788 |  37031 |
    -------------------------------------------------------------------------
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    

    Best regards,
    Mikhail.

    Comment by Velikikh Mikhail — March 30, 2015 @ 3:05 pm BST Mar 30,2015 | Reply

    • Mikhail,

      Thanks for that.

      I was very surprised when I read the note, and did a quick test that resulted in an Oracle error message (in the 30,000 or 31,000 range, I think) on the call to refresh. Since that was “expected” behaviour I didn’t look more closely. I wonder if the error was something completely different – like Oracle complaining that it has a problem using the MV to run the SQL that was refreshing the MV ;) Unfortunately since it was such a Q&D to check the truth of a document I didn’t save the test as a script.

      Comment by Jonathan Lewis — April 1, 2015 @ 8:10 pm BST Apr 1,2015 | Reply

      • Hi Jonathan, hi Mikhail,

        I also saw this error, which Jonathan was talking about (see below). So I went into a new session and tried Mikhails code – it also works. My example uses a DB link and different schemas. Maybe there are special cases where it works and for other we have to “implement” the workaround of joining the table to dual…

        --===================================================================
        --======================== conn to master DB ===========================
        --===================================================================
        
        create table t_master (
         m_id number
        ,m_text varchar2(3)
        );
        
        begin
        for anz in (select 50000 rec from dual
                    union all
                    select 50300 rec from dual
                    union all
                    select 50500 rec from dual
                    union all
                    select 70000 rec from dual
                    union all
                    select 95400 rec from dual
                    union all
                    select 40930 rec from dual
                    union all
                    select 65800 rec from dual
                    union all
                    select 186560 rec from dual
                    union all
                    select 34500 rec from dual) 
        loop  
          insert into t_master (m_id,m_text)
          select 
             to_number(anz.rec || level) m_id 
           ,dbms_random.string('U',3) as m_text 
          from dual 
          connect by level < anz.rec;
        end loop;
        end;
        /
        commit;
        alter table t_master add primary key (m_id);
        
        --===================================================================
        --======================== conn remote DB =============================
        --===================================================================
        
        create database link db_link_master
        connect to &masterschema identified by &masterpwd
        using '
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = &host )(PORT = &port))
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = &servicename)
            )
          )';
        
        create materialized view MVOOP (m_id, m_text)
        tablespace users
        as select m_id, m_text
        from t_master@db_link_master;
        
        execute dbms_mview.refresh('MVOOP_SLAVE', method => '?', atomic_refresh => false, out_of_place => true);
        
        
        --gives me:
        --ORA-32354: cannot refresh materialized view C##MV_SLAVE.MVOOPF using out-of-place complete refresh
        --*Cause:    The materialized view did not qualify for out-of-place complete
        --           refresh.
        --*Action:   Set the 'out_of_place' parameter to 'false'.
        
        
        
        drop materialized view MVOOP; 
        
        create materialized view MVOOP (m_id, m_text)
          tablespace users 
          as select m_id, m_text
               from t_master@db_link_master
               join dual on 1=1;
               
        execute dbms_mview.refresh('MVOOP_SLAVE', method => '?', atomic_refresh => false, out_of_place => true);
        

        –it works…

        Comment by Jonas Gassenmeyer (@gassenmj) — November 8, 2015 @ 7:47 pm BST Nov 8,2015 | Reply

  4. Hi Jonathan!

    Is there a way to automatically purge dropped table from recyclebin?

    Best regards,
    Kresimir

    Comment by Kresimir — May 8, 2015 @ 9:12 am BST May 8,2015 | Reply

  5. Another observation: after OUT_OF_PLACE refresh, MV/table grants won’t be replicated to “new” table.

    Comment by Kresimir — May 8, 2015 @ 1:58 pm BST May 8,2015 | Reply

    • I can confirm this is the case as I had put this new feature in for a table that was running for 18-24 hours (now refreshes in 4 hours). However, the lack of grants on the table after the refresh is a big problem. I could add grants into the process, but this has the added side-effect of invalidating all the cursors (maybe that happens anyway with the internal rename/exchanges that take place). I would have expected Oracle to retain grants just like it is retaining the indexing. I’ll be opening an SR on this issue.

      Comment by Bradd Piontek — February 9, 2016 @ 4:49 pm BST Feb 9,2016 | Reply

  6. Hi Jonathan,

    again a great post!

    Regarding Your comment:
    “users won’t get wrong (silently missing) results, they’ll an Oracle error (probably ORA-08103: object no longer exists)”

    What I did in version 12.1.0.1.0. was…

    – A complete refresh atomic_refresh = false (without out_of_place refresh).
    – In another session, I did a loop which queried this MV.
    – Instead of wrong results, it still gave me ORA-08103: object no longer exists.
    – I would have expected, that only an out_of_place refresh would have thrown an error and the atomic_refresh=false (without out_of_place refresh) would have given me no error.

    So my question is: Do You know if this behaviour changed from version 11 to 12?

    Did You ever experienced (in previous versions) that Oracle presented a wrong result instead of an error? Would be great to have an example. Because in 12c I was not able to reproduce it…

    Thanks and best regards
    Jonas

    Comment by Jonas Gassenmeyer (@gassenmj) — November 1, 2015 @ 6:58 pm BST Nov 1,2015 | Reply

    • Jonas,

      I don’t have a prepared example but I’d try something like this:
      Create a refresh group with three materialized views that take some time (e.g. 30 seconds each) to refresh
      Start a refresh with atomic_refresh = false
      When the first materialized view has refreshed run a query that joins the first and third MVs.
      With careful timing I think you will be able to see that new data from the first MV successfully joined to the old data from the third MV.

      Comment by Jonathan Lewis — November 1, 2015 @ 9:08 pm BST Nov 1,2015 | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.