ASH Analysis: Detecting and Profiling Adaptive Plans in 12c

Oracle 12c introduced adaptive SQL execution plans, this post by Maria Colgan introduced the concept in blog post: "The goal of adaptive plans is to avoid catastrophic behavior of bad plans on the first execution. If we detect during execution that the optimizer’s cardinality estimates were wrong, then some plan choices can be changed “on the fly” to better options. While we can’t completely change the plan during execution, there are certain local decisions that can be changed, like join method."
However, in this post, I will to look at how you detect whether a plan has adapted. I am going to look at a real-life example of a particular statement that I had to investigate. Plan adaption may or may not be a problem. In this case I think it wasn't, but I do think you need to know it is happening. There are some new columns on v$active_session_history and DBA_HIST_ACTIVE_SESS_HISTORY.

SQL> desc v$active_session_history
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
…
 SQL_ADAPTIVE_PLAN_RESOLVED                         NUMBER
 SQL_FULL_PLAN_HASH_VALUE                           NUMBER
…

Different adaptive sub-plans for the same overall plan will have different SQL_PLAN_HASH_VALUEs, but will share the same SQL_FULL_PLAN_HASH_VALUE.
The next query shows that a particular SQL_ID has two values of SQL_PLAN_HASH_VALUES. The column SQL_ADAPTIVE_PLAN_RESOLVED indicates that not only is this an adaptive plan, but both sub-plans have been used.

SELECT sql_plan_hash_Value, sql_full_plan_hash_Value, sql_adaptive_plan_resolved
, COUNT(DISTINCT sql_exec_id), COUNT(*)
FROM dba_hist_active_Sess_history
WHERE sql_id = '4dszd9dysry0c'
GROUP BY sql_plan_hash_value, sql_full_plan_hash_value, sql_adaptive_plan_resolved
/

SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_ADAPTIVE_PLAN_RESOLVED COUNT(DISTINCTSQL_EXEC_ID)   COUNT(*)
------------------- ------------------------ -------------------------- -------------------------- ----------
         4114868852               4059585501                          1                     253           283
         3412983073               4059585501                          1                     302           309
                  0               4059585501                          0                       0             5

Note that you must pass the SQL_PLAN_HASH_VALUE to DBMS_XPLAN. If you pass SQL_FULL_PLAN_HASH_VALUE you will not find the plan.

Error: cannot fetch plan for statement_id '4dszd9dysry0c'

I will now extract the execution plans from AWR with DBMS_XPLAN.  I am using DISPLAY rather DISPLAY_AWR because I have imported the AWR from another database, and created a view of the plan data called ASH_PLAN_TABLE).
Note that this plan only has line IDs 0 to 7.

select * from table(dbms_xplan.display('ASH_PLAN_TABLE','4dszd9dysry0c',null,'dbid=2783210685 and plan_hash_value = 3412983073'));

Plan hash value: 3412983073
---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |       |       |    13 (100)|          |
|   1 |  SORT ORDER BY                         |                |     9 |   747 |    13   (8)| 00:00:01 |
|   2 |   HASH JOIN                            |                |     9 |   747 |    12   (0)| 00:00:01 |
|   3 |    JOIN FILTER CREATE                  | :BF0000        |     9 |   747 |    12   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| PGRELS         |     9 |   585 |     5   (0)| 00:00:01 |
|   5 |      INDEX RANGE SCAN                  | LINKSOURCE_201 |     9 |       |     3   (0)| 00:00:01 |
|   6 |    JOIN FILTER USE                     | :BF0000        |   122 |  2196 |     7   (0)| 00:00:01 |
|   7 |     TABLE ACCESS STORAGE FULL          | USERGROUPS     |   122 |  2196 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan

If I used +ADAPTIVE option. DBMS_XPLAN will emit all the lines in the full plan, and mark those inactive in this adaptive plan with a minus in the ID column.  The statistics collector operation at line 6 shows where the optimizer collected statistics at run time.

select * from table(dbms_xplan.display('ASH_PLAN_TABLE','4dszd9dysry0c','+ADAPTIVE','dbid=2783210685 and plan_hash_value = 3412983073'));

Plan hash value: 3412983073
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                |       |       |    13 (100)|          |
|   1 |  SORT ORDER BY                            |                |     9 |   747 |    13   (8)| 00:00:01 |
|   2 |   HASH JOIN                               |                |     9 |   747 |    12   (0)| 00:00:01 | 
|   3 |    JOIN FILTER CREATE                     | :BF0000        |     9 |   747 |    12   (0)| 00:00:01 |
|-  4 |     NESTED LOOPS                          |                |     9 |   747 |    12   (0)| 00:00:01 |
|-  5 |      NESTED LOOPS                         |                |       |       |            |          |
|-  6 |       STATISTICS COLLECTOR                |                |       |       |            |          |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| PGRELS         |     9 |   585 |     5   (0)| 00:00:01 |
|   8 |         INDEX RANGE SCAN                  | LINKSOURCE_201 |     9 |       |     3   (0)| 00:00:01 |
|-  9 |       INDEX UNIQUE SCAN                   | SYS_C008784    |       |       |            |          |
|- 10 |      TABLE ACCESS BY INDEX ROWID          | USERGROUPS     |     1 |    18 |     7   (0)| 00:00:01 |
|  11 |    JOIN FILTER USE                        | :BF0000        |   122 |  2196 |     7   (0)| 00:00:01 |
|  12 |     TABLE ACCESS STORAGE FULL             | USERGROUPS     |   122 |  2196 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

…
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

And now I will produce the plan for the other hash value. The lines in the execution plan are the same, and the costs are the same, but the minus signs are now in different places indicating different lines are inactive.  The plan has flipped between indexed lookups in a nested loop joins and hash joined full scans.

select * from table(dbms_xplan.display('ASH_PLAN_TABLE','4dszd9dysry0c','+ADAPTIVE','dbid=2783210685 and plan_hash_value = 4114868852'));

Plan hash value: 4114868852
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                |       |       |    13 (100)|          |
|   1 |  SORT ORDER BY                            |                |     8 |   664 |    13   (8)| 00:00:01 |
|-  2 |   HASH JOIN                               |                |     8 |   664 |    12   (0)| 00:00:01 |
|   3 |    JOIN FILTER CREATE                     | :BF0000        |     8 |   664 |    12   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                          |                |     8 |   664 |    12   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                         |                |       |       |            |          |
|-  6 |       STATISTICS COLLECTOR                |                |       |       |            |          |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| PGRELS         |     8 |   520 |     5   (0)| 00:00:01 |
|   8 |         INDEX RANGE SCAN                  | LINKSOURCE_201 |     9 |       |     3   (0)| 00:00:01 |
|   9 |       INDEX UNIQUE SCAN                   | SYS_C008784    |       |       |            |          |
|  10 |      TABLE ACCESS BY INDEX ROWID          | USERGROUPS     |     1 |    18 |     7   (0)| 00:00:01 |
|- 11 |    JOIN FILTER USE                        | :BF0000        |   122 |  2196 |     7   (0)| 00:00:01 |
|- 12 |     TABLE ACCESS STORAGE FULL             | USERGROUPS     |   122 |  2196 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

So now I want to look at how much DB time has been spent where in which plan. The following query profiles the DB time by plan and line within the plan.

select sql_plan_hash_Value, sql_full_plan_hash_Value, sql_plan_line_id, sql_adaptive_plan_resolved
, sum(10) db_secs
from dba_hist_active_Sess_history
where sql_id = '4dszd9dysry0c'
group by dbid, sql_plan_hash_value, sql_full_plan_hash_Value, sql_plan_line_id, sql_adaptive_plan_resolved
order by 1,2,4
/

We can see that the plan with hash value 3412983073 spent time in line 12, but we saw earlier that the plan only has 7 lines if it was not generated with the +ADAPTIVE option.
NB: The line numbers reported in the ASH data correspond to the full adaptive plan, so be careful to compare profiles by line back to expanded +ADAPTIVE execution plans.

SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID SQL_ADAPTIVE_PLAN_RESOLVED    DB_SECS
------------------- ------------------------ ---------------- -------------------------- ----------
         3412983073               4059585501                1                          1         60
         3412983073               4059585501                2                          1       1540
         3412983073               4059585501                3                          1         80
         3412983073               4059585501                7                          1         50
         3412983073               4059585501                8                          1         50
         3412983073               4059585501               12                          1        750
         3412983073               4059585501                                           1        560

         4114868852               4059585501                                           0         10
         4114868852               4059585501                1                          1        230
         4114868852               4059585501                2                          1         20
         4114868852               4059585501                3                          1        200
         4114868852               4059585501                4                          1         40
         4114868852               4059585501                7                          1        140
         4114868852               4059585501                8                          1        150
         4114868852               4059585501                9                          1         70
         4114868852               4059585501               10                          1        800
         4114868852               4059585501                                           1       1180

The next query profiles database time by plan for this SQL statement.

break on sample_time skip 1
alter session set nls_date_Format = 'dd.mm.yy';
select sql_full_plan_hash_Value, sql_plan_hash_value, sql_adaptive_plan_resolved, trunc(sample_time) sample_time
, count(*)
from dba_hist_active_Sess_history
where sql_id = '4dszd9dysry0c'
and sql_plan_hash_value > 0
group by dbid, sql_plan_hash_value, sql_full_plan_hash_value, sql_adaptive_plan_resolved, trunc(sample_time)
order by sample_time
/

We can see that the adaptive plan of this statement is flipping backwards and forwards over several days.

SQL_FULL_PLAN_HASH_VALUE SQL_PLAN_HASH_VALUE SQL_ADAPTIVE_PLAN_RESOLVED SAMPLE_T   COUNT(*)
------------------------ ------------------- -------------------------- -------- ----------
              4059585501          4114868852                          1 06.11.16         35
              4059585501          3412983073                          1                  78

              4059585501          4114868852                          1 08.11.16        236

              4059585501          3412983073                          1 09.11.16        226
              4059585501          4114868852                          0                   1
              4059585501          4114868852                          1                  12

              4059585501          4114868852                          1 10.11.16         21
              4059585501          3412983073                          1                  74

              4059585501          3412983073                          1 11.11.16         20
              4059585501          4114868852                          1                   1

Then we would have to dig down further to work out why this might be happening.
 This is the statement. It is a vanilla query from a packaged system.

SELECT  item_t1.PK  
FROM pgrels item_t0 
JOIN usergroups item_t1 
ON  item_t0.TargetPK = item_t1.PK  
WHERE (    item_t0.Qualifier  = :1  
      AND  item_t0.SourcePK  = :2
      AND  item_t0.languagepk  IS NULL) 
AND   (item_t1.TypePkString IN  (:3 ,:4 ,:5 ,:6 ,:7 ) ) 
order by  item_t0.SequenceNumber  ASC , item_t0.PK  ASC

The changing values of the bind variables supplied to the SQL statement have a part to play in causing the plan to change, and in this case it is not a problem and the optimizer is functioning as designed.

DBMS_LOCK: How to ensure only one instance of a PL/SQL procedure can execute concurrently.

I recently had a need to ensure that only one instance of a PL/SQL procedure was running concurrently. My solution was to create a named lock with Oracle-supplied DBMS_LOCK package. There are lots of other blogs and posts on the internet on this subject, but this is how I used it.
Just as a row level lock will block another session from obtaining a lock on the same row, a session holding a named lock created with DBMS_LOCK can prevent another session obtaining a lock with the same name.
(updated 21.11.2016) An essential difference is that by default these locks will survive a commit or rollback.  Although you can choose to release them on commit.
In my package MY_PACKAGE, I have a procedure MY_PROCEDURE. I want to ensure that only a single instance of MY_PROCEDURE can run at any one time in my database.

CREATE OR REPLACE PACKAGE BODY my_package AS 
…
k_module     CONSTANT VARCHAR2(48 CHAR) := $$PLSQL_UNIT; --name of package for instrumentation
l_lockhandle VARCHAR2(128); -- lock handle for session lock object
…
PROCEDURE my_procedure
          (/*other parameters*/
          ,p_lock_timeout   INTEGER  DEFAULT 0 /*override lock timeout*/
) IS
  l_retcode      NUMBER;
…
BEGIN
  unlock;/*unlock any session lock from previous execution in this session*/
  DBMS_LOCK.ALLOCATE_UNIQUE(k_module,l_lockhandle); /*take out an exclusive lock*/
  l_retcode:=DBMS_LOCK.REQUEST(lockhandle=>l_lockhandle
                              ,lockmode=>DBMS_LOCK.x_mode
                              ,timeout=>p_lock_timeout
                              /*,release_on_commit=>FALSE by default*/
                              ); /*check we have the lock*/

  IF l_retcode<>0 THEN
    raise_application_error(-20000,k_module||'.MY_PROCEDURE is already running');
  END IF;
…
/*do rest of package*/
…  
  unlock;
END my_procedure;

• A package global constant is set to $$PLSQL_UNIT$$. This evaluates to the name of the package, in this case, MY_PACKAGE. I often use this during instrumentation to pass the name of the package to DBMS_APPLICATION_INFO. In this case, it will be used as the name of the lock.
• I also have a package global variable L_LOCKHANDLE which will be used as the handle of the lock. The contents of this variable will be available to subsequent instances of the package in the same session.
• A unique lock identifier is allocated with DBMS_LOCK.ALLOCATE_UNIQUE. The lock is available for the life of the session, up to a default timeout of 10 days. The name of the lock is the name of the package taken ultimately from $$PLSQL_UNIT$$.
• The lock is acquired with the DBMS_LOCK.REQUEST function. It is possible to pass a timeout in section to this function for which it will wait if it cannot acquire the lock.
• The lock is requested in exclusive mode.  Other lock modes are available, but I only want one session to be able to acquire this lock.  
• If the request function returns a non-zero code then the lock has not been acquired and the package raises an exception with a custom message. 

ERROR at line 1:
ORA-20000: MY_PACKAGE.MY_PROCEDURE is already running

If the package failed with an error it would continue to hold the lock in the session. Restarting the procedure in the same session would generate an error because the session could not acquire the lock again. I put the code to release the lock in a separate procedure.  The lock handle is stored in a package global variable so the handle will be available from previous executions of the package in the same session.  The first and last thing MY_PROCEDURE does is to call the unlock procedure.
I also found it useful to be able to call the unlock procedure during debugging, so I exposed it in the package header.

PROCEDURE unlock IS
  l_retcode NUMBER;
…
BEGIN
…
IF l_lockhandle IS NULL THEN 
    debug_msg('no lock handle',9);
  ELSE
    l_retcode:=DBMS_LOCK.RELEASE(l_lockhandle); /*release lock*/
    debug_msg('dbms_lock.release('||l_lockhandle||') return code '||l_retcode,8);
    IF l_retcode = 0 THEN 
      debug_msg('Lock Released',2);
    ELSIF l_retcode = 3 THEN
      debug_msg('Parameter Error',8);
    ELSIF l_retcode = 4 THEN
      debug_msg('Do not own lock specified by lockhandle '||l_lockhandle,8);
    ELSIF l_retcode = 5 THEN
      debug_msg('Illegal lock handle '||l_lockhandle,8);
    END IF;
  END IF;
…
END unlock;

DBMS_LOCK is a simple and robust mechanism that can easily be incorporated into a package.  Even if you can control execution concurrency via whatever you use to schedule the package, it is a useful safety mechanism.

Refreshing Materialized Views with Referential Integrity Constraints

I have a number of tables on a reporting system which have referential integrity constraints, and whose contents are replicated from a primary system. I am going to create materialized views on these prebuilt tables to manage incremental refresh. However, the referential integrity means that some materialized view will have to be refreshed before others which refer to them.
The referential constraints can be queried from the DBA_/USER_CONSTRAINTS view. Clearly this defines a hierarchy of tables. I could construct a hierarchical query on this data I could determine the order in which to refresh the materialized views.  Naturally, I looked for someone who had done this already and I found this on the Ask Tom website: All Parent - Child tables in the database. It was written in 2001 for Oracle 8.1.6. I have used the same demonstration, and enhanced it some newer features. I ran my tests on 12c.

create table p ( x int primary key ); 
create materialized view log on p;
create table c0 ( x int primary key); 
create table c1 ( x primary key constraint c1_p  references p); 
create table c2 ( x primary key constraint c2_c1 references c1); 
create table c3 ( x primary key constraint c3_c2 references c2); 
create table c4 ( x primary key constraint c4_c2 references c2); 

create materialized view c0 on prebuilt table as select x from p;
create materialized view c1 on prebuilt table as select x from p;
create materialized view c2 on prebuilt table as select x from p;
create materialized view c3 on prebuilt table as select x from p;
create materialized view c4 on prebuilt table as select x from p;

insert into p select rownum from dual connect by level <= 42;
commit;

I have no problem refreshing a materialized view C0 without an referential constraints, but C4 has a constraint that refers to C2 that has not been refreshed so I get a parent key not found error.

exec dbms_mview.refresh('C0',method=>'F');
exec dbms_mview.refresh('C4',method=>'F');

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02291: integrity constraint (SCOTT.C4_C2) violated - parent key not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

Part 1: Incremental Refresh 

I will start with the easier problem of incremental refresh. Related materialized views can be passed as a list to the DBMS_MVIEW.REFRESH procedure and will be refreshed in a single database transaction. Hierarchies of related tables are created by the aggregation of these constraints. I can calculate which materialized views to group together.
Let's start by creating a working storage table that contains a row for each materialized view.

DROP TABLE dmk_mview_refresh PURGE
/
CREATE TABLE dmk_mview_refresh AS 
SELECT owner, mview_name, mview_name refresh_group
FROM   dba_mviews
WHERE  owner = user
/

Ideally, I would like to be able to refresh each one independently. If I were using refresh groups, each materialized view would be in its own group, which for simplicity will named the same as the materialized view.

set pages 99 lines 200
column owner format a10
column mview_name format a10
column refresh_Group format a10
break on report
SELECT * FROM dmk_mview_refresh
ORDER BY 1,2,3;

OWNER      MVIEW_NAME REFRESH_GR
---------- ---------- ----------
SCOTT      C0         C0
SCOTT      C1         C1
SCOTT      C2         C2
SCOTT      C3         C3
SCOTT      C4         C4

This PL/SQL block works through each referential constraint that links any two materialized views, and determines the current refresh group for each materialized view and if they are not in the same group it moves all materialized views in the group of referring materialized view to the group of the referred to materialized view.

set serveroutput on
DECLARE
  l_owner VARCHAR2(30) := user;
  l_groupc VARCHAR2(30);
  l_groupr VARCHAR2(30);
BEGIN
  FOR i IN (
select mr.owner ownerr, mr.mview_name mview_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mc.owner = l_owner
and r.owner = mc.owner
and r.table_name = mc.container_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mr.owner = c.owner
and mr.container_name = c.table_name
  ) LOOP
    dbms_output.put_line(i.ownerr||'.'||i.mview_namer||'->'||i.ownerc||'.'||i.mview_namec||' constraint '||i.constraint_name);

    SELECT refresh_group
    INTO   l_groupr
    FROM   dmk_mview_refresh
    WHERE  owner = i.ownerr
    AND    mview_name = i.mview_namer;

    SELECT refresh_group
    INTO   l_groupc
    FROM   dmk_mview_refresh
    WHERE  owner = i.ownerc
    AND    mview_name = i.mview_namec;

    IF l_groupc != l_groupr THEN
      UPDATE dmk_mview_refresh
      SET    refresh_group = l_groupc
      WHERE  refresh_group = l_groupr;

      dbms_output.put_line('Update '||l_groupr||'->'||l_groupc||' '||SQL%rowcount||' rows updated');
    END IF;

  END LOOP;
END;
/

SCOTT.C2->SCOTT.C4 constraint C4_C2
Update C2->C4 1 rows updated
SCOTT.C2->SCOTT.C3 constraint C3_C2
Update C4->C3 2 rows updated
SCOTT.C1->SCOTT.C2 constraint C2_C1
Update C1->C3 1 rows updated

You can see that C1, C2, C3 and C4 are now all in group C3. So they need to be refreshed in a single operation.

break on refresh_group skip 1
select * from dmk_mview_refresh
order by 3,1,2;

OWNER      MVIEW_NAME REFRESH_GR
---------- ---------- ----------
SCOTT      C0         C0

SCOTT      C1         C3
SCOTT      C2
SCOTT      C3
SCOTT      C4

I can use the LISTAGG() analytic function to simply generate the list and pass it to DBMS_MVIEW.REFRESH.

DECLARE
  l_t1 TIMESTAMP;
  l_t2 TIMESTAMP;
  l_tdiff NUMBER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
BEGIN 
  dbms_application_info.read_module(l_module,l_action);
  dbms_application_info.set_module('MV Group Refresh','Begin');
  FOR i IN (
select  refresh_group, listAgg(owner||'.'||mview_name, ',') WITHIN GROUP (ORDER BY mview_name) mview_list
from dmk_mview_refresh
GROUP BY refresh_Group
  ) LOOP
    dbms_application_info.set_action(i.refresh_group);
    l_t1 := SYSTIMESTAMP;

    dbms_output.put_line(l_t1||' Start Refresh MV group: '||i.mview_list);
    dbms_mview.refresh(i.mview_list,method=>'F');

    l_t2 := SYSTIMESTAMP;
    l_tdiff := 60*(60*(24*extract(day from l_t2-l_t1)+extract(hour from l_t2-l_t1))+extract(minute from l_t2-l_t1))+extract(second from l_t2-l_t1);
    dbms_output.put_line(l_t2||' End Refresh MV '||i.mview_list||':'||l_tdiff||' secs');
    commit;
  END LOOP;
  dbms_application_info.set_module(l_module,l_action);END;
/

PL/SQL procedure successfully completed.

And you can see they materialized views refresh without error.

20-OCT-16 09.50.43.119765 Start Refresh MV group: SCOTT.C0
20-OCT-16 09.50.43.382501 End Refresh MV SCOTT.C0:.262736 secs
20-OCT-16 09.50.43.383195 Start Refresh MV group: SCOTT.C1,SCOTT.C2,SCOTT.C3,SCOTT.C4
20-OCT-16 09.50.43.655243 End Refresh MV SCOTT.C1,SCOTT.C2,SCOTT.C3,SCOTT.C4:.272048 secs

If I wanted to schedule the refresh I could also create corresponding refresh groups with DBMS_REFRESH.

Part 2: Non-Atomic Full Refresh

The second question is how to fully refresh the materialized views. I will have to do this at least once when I instantiate the replication.  I have a real-world case where there is a large volume of data in these tables, so I also want to use non-atomic refresh to reduce the time taken to refresh, and the size of the final table (see also Complete Refresh of Materialized Views: Atomic, Non-Atomic and Out-of-Place). I cannot use the same list approach as above. I can disable constraints to facilitate the refresh, but I won't be able to re-enable them until the data is integral. Of course I could disable all the constraints, refresh all the materialized views and then re-enable all the constraints.

ALTER TABLE c4 MODIFY CONSTRAINT c4_c2 DISABLE;
exec dbms_mview.refresh('C4',method=>'C',atomic_refresh=>FALSE);
ALTER TABLE c4 MODIFY CONSTRAINT c4_c2 ENABLE;
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.C4_C2) - parent keys not found

So in this case I need to refresh the materialized views on which C4 depends first, and so on.

column mview_name format a10
column table_name format a10
column constraint_name format a10
select mr.owner, mr.mview_name, r.table_name
, r.constraint_name, r.status
,  mc.owner, mc.mview_name, c.table_name
from dba_mviews mr
, dba_constraints r
, dba_constraints c
, dba_mviews mc
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
order by 1,2

OWNER      MVIEW_NAME TABLE_NAME CONSTRAINT STATUS   OWNER      MVIEW_NAME TABLE_NAME
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
SCOTT      C2         C2         C2_C1      ENABLED  SCOTT      C1         C1
SCOTT      C3         C3         C3_C2      ENABLED  SCOTT      C2         C2
SCOTT      C4         C4         C4_C2      ENABLED  SCOTT      C2         C2

I need to tree walk the hierarchy of foreign keys to determine which tables need to be refreshed first.

column path format a20
with x as (
select /*+MATERIALIZE*/ mr.owner ownerr, mr.mview_name mview_namer, r.table_name table_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec, c.table_name table_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
)
select level mylevel, ownerr, mview_namer, ownerc, mview_namec
,       sys_connect_by_path(mview_namer,'/') path
from x
connect by nocycle prior mview_namer = mview_namec
              and  prior ownerr      = ownerc
order by 1,2,3
/

I need to start with the tables with the lowest maximum level first.

   MYLEVEL OWNERR     MVIEW_NAMER OWNERC     MVIEW_NAMEC PATH
---------- ---------- ----------- ---------- ----------- --------------------
         1 SCOTT      C2          SCOTT      C1          /C2
         1 SCOTT      C3          SCOTT      C2          /C3
         1 SCOTT      C4          SCOTT      C2          /C4
         2 SCOTT      C3          SCOTT      C2          /C2/C3
         2 SCOTT      C4          SCOTT      C2          /C2/C4

Any materialized view not picked up in the tree walk is given level 0, and now I can calculate the refresh order in a single SQL statement and execute the refreshes in a single PL/SQL block.

set serveroutput on 
DECLARE
  l_t1 TIMESTAMP;
  l_t2 TIMESTAMP;
  l_tdiff NUMBER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_sql CLOB;
BEGIN 
  dbms_application_info.read_module(l_module,l_action);
  dbms_application_info.set_module('MV Group Refresh','Begin');
  FOR i IN (
with x as ( /*obtain constraints and identify table in referred constraint*/
select /*+MATERIALIZE*/ mr.owner ownerr, mr.mview_name mview_namer, r.table_name table_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec, c.table_name table_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
), y as ( /*tree walk constraints*/
select level mylevel, ownerr, mview_namer
from x
connect by nocycle prior mview_namer = mview_namec
              and  prior ownerr      = ownerc
union /*add all MVs at level 0*/
select 0, owner, mview_name
from  dba_mviews
where owner = user
)
select  ownerr owner, mview_namer mview_name
,  max(mylevel) mview_level
from y
group by ownerr, mview_namer
order by mview_level
  ) LOOP
    dbms_application_info.set_action(i.mview_name);

    FOR j IN ( /*disable enabled constraints*/
      SELECT r.owner, r.table_name, r.constraint_name
      FROM   dba_constraints c
      ,      dba_constraints r
      WHERE  c.owner = i.owner
      AND    c.table_name = i.mview_name
      AND    c.constraint_Type IN ('U','P')
      and    r.constraint_type = 'R'
      and    c.owner = r.r_owner
      and    c.constraint_name = r.r_constraint_name
      and    r.status = 'ENABLED'
    ) LOOP
      dbms_application_info.set_action('Disable '||j.constraint_name);
      l_sql := 'ALTER TABLE '||user||'.'||j.table_name||' MODIFY CONSTRAINT '||j.constraint_name||' DISABLE';
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END LOOP;

    l_t1 := SYSTIMESTAMP;
    dbms_output.put_line(l_t1||' Start Refresh MV '||i.mview_name||' ('||i.mview_level||')');

    dbms_mview.refresh(i.mview_name,method=>'C',atomic_refresh=>FALSE);
    l_t2 := SYSTIMESTAMP;
    l_tdiff := 60*(60*(24*extract(day from l_t2-l_t1)+extract(hour from l_t2-l_t1))+extract(minute from l_t2-l_t1))+extract(second from l_t2-l_t1);
    dbms_output.put_line(l_t2||' End Refresh MV '||i.mview_name||':'||l_tdiff||' secs');
    commit;
    
    FOR j IN ( /*reenable disabled constraints*/
      SELECT r.owner, r.table_name, r.constraint_name
      FROM   dba_constraints c
      ,      dba_constraints r
      WHERE  c.owner = i.owner
      AND    c.table_name = i.mview_name
      AND    c.constraint_Type IN ('U','P')
      and    r.constraint_type = 'R'
      and    c.owner = r.r_owner
      and    c.constraint_name = r.r_constraint_name
      and    r.status = 'DISABLED'
    ) LOOP
      dbms_application_info.set_action('Disable '||j.constraint_name);
      l_sql := 'ALTER TABLE '||user||'.'||j.table_name||' MODIFY CONSTRAINT '||j.constraint_name||' ENABLE';
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END LOOP;

  END LOOP;
  dbms_application_info.set_module(l_module,l_action);
END;
/

Constraints and disabled before the refresh and re-enabled afterwards. The maximum tree level of the materialized view is shown in brackets.

20-OCT-16 10.32.21.985520 Start Refresh MV C0 (0)
20-OCT-16 10.32.22.497711 End Refresh MV C0:.512191 secs
ALTER TABLE SCOTT.C2 MODIFY CONSTRAINT C2_C1 DISABLE
20-OCT-16 10.32.22.728865 Start Refresh MV C1 (0)
20-OCT-16 10.32.23.174410 End Refresh MV C1:.445545 secs
ALTER TABLE SCOTT.C2 MODIFY CONSTRAINT C2_C1 ENABLE
ALTER TABLE SCOTT.C3 MODIFY CONSTRAINT C3_C2 DISABLE
ALTER TABLE SCOTT.C4 MODIFY CONSTRAINT C4_C2 DISABLE
20-OCT-16 10.32.23.437297 Start Refresh MV C2 (1)
20-OCT-16 10.32.23.873448 End Refresh MV C2:.436151 secs
ALTER TABLE SCOTT.C3 MODIFY CONSTRAINT C3_C2 ENABLE
ALTER TABLE SCOTT.C4 MODIFY CONSTRAINT C4_C2 ENABLE
20-OCT-16 10.32.24.240742 Start Refresh MV C3 (2)
20-OCT-16 10.32.24.714952 End Refresh MV C3:.47421 secs
20-OCT-16 10.32.25.007517 Start Refresh MV C4 (2)
20-OCT-16 10.32.25.408507 End Refresh MV C4:.40099 secs

PL/SQL procedure successfully completed.

One of my Favourite Database Things: DBMS_APPLICATION_INFO

Notes for my slot in session UGF2630: EOUC Database ACES Share Their Favorite Database Things: Part I – OpenWorld , Sunday 18th September 2016
DBMS_APPLICATION_INFO is probably one of the most undervalued and without any doubt the most underused of all the packages supplied with the database by Oracle. On the face it, it is a very simple package that does a few very simple things, but it is not possible to overstate its significance.

Oracle 7.3.3

It has also been around for a long time. This package was first documented in Oracle 7.3.3. "Application developers can use the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules… System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a form in an Oracle Forms application, or the name of the code segment in an Oracle Precompilers application. The action name should usually be the name or description of the current transaction within a module." – Oracle 7 Tuning, Release 7.3.3 ©Oracle 1997, Chapter 23 Registering Applications^*

How to Register a Session

Make a simple call to DBMS_APPLICATION_INFO, and you will be able to see MODULE and ACTION in your session information,

EXECUTE dbms_application_info.set_module(module_name=>'MYMODULE',action_name=>'MYACTION');

MODULE and ACTION are just character strings. You can put anything you like in there, but I recommend that the values are meaningful to your application, consistent and predicable.

select sid, serial#, username, module, action
from v$session
where sid IN(select distinct sid from v$mystat)
/
       SID    SERIAL# USERNAME MODULE               ACTION
---------- ---------- -------- -------------------- --------------------------------
       367       2515 SCOTT    MYMODULE             MYACTION

And you can also identify SQL for that module in v$sqlarea.

select sql_id, module, action, sql_text
from v$sqlarea
where module = 'MYMODULE'
/
SQL_ID        MODULE               ACTION
------------- -------------------- --------------------------------
SQL_TEXT
---------------------------------------------------------------------------------------------
17f888us1tbk2 MYMODULE             MYACTION
select sid, serial#, username, module, action from v$session where sid IN(select distinct sid from v$mystat)

9bsqpcnygpgp7 MYMODULE             MYACTION
select sql_id, module, action, sql_text from v$sqlarea where module = 'MYMODULE'

This hasn't changed since 1997 (except the strings have got longer). Today, there are a few more dynamic performance views where module and action are recorded.

Tracing

When we did performance tuning back in the 1990s, all we had was the buffer cache hit ratio (joke) and SQL trace (an even older feature – introduced in Oracle 6, augmented by the wait interface in 7.0.12). When you registered a session with DBMS_APPLICAITON_INFO, it was recorded in the trace. So you knew which part of the trace refers to which value of module and action.
This following is taken from a 12c trace.  You can see where I called DBMS_APPLICATION_INFO to set the module and action.

Trace file /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_8795.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
…
*** 2016-08-11 11:17:42.803
*** SESSION ID:(367.2515) 2016-08-11 11:17:42.803
*** CLIENT ID:() 2016-08-11 11:17:42.803
*** SERVICE NAME:(orcl) 2016-08-11 11:17:42.803
*** MODULE NAME:() 2016-08-11 11:17:42.803
*** CLIENT DRIVER:(SQL*PLUS) 2016-08-11 11:17:42.803
*** ACTION NAME:() 2016-08-11 11:17:42.803
*** CONTAINER ID:(3) 2016-08-11 11:17:42.803
…
PARSING IN CURSOR #140183328563984 len=95 dep=0 uid=156 oct=47 lid=156 tim=79763572382 hv=611933126 ad='695370e0' sqlid='dbvj2xsk7kqy6'
BEGIN dbms_application_info.set_module(module_name=>'MYMODULE',action_name=>'MYACTION'); END;
*** MODULE NAME:(MYMODULE) 2016-08-11 11:17:42.972
*** ACTION NAME:(MYACTION) 2016-08-11 11:17:42.972

You can use the trcsess utility to extract part of a trace by module and action

trcsess output=MYACTION.trc module=MYMODULE cdb1_ora_8795.trc

and this is the start of the resulting file

*** [ Unix process pid: 8795 ]
*** 2016-08-11 11:17:42.972
*** 2016-08-11 11:17:42.972
…

And again, this hasn't changed since it was introduced

Client ID

There is also a function in DBMS_APPLICATION_INFO to set a client identifier. Again it is just a string. The idea is that you can associate an operator with a database session.

dbms_application_info.set_client_info(client_info IN VARCHAR2)

For example, PeopleSoft makes practical use of this to get a user ID set by the application into database triggers that perform application auditing. It uses another procedure to read the client identifier.

dbms_application_info.read_client_info(client_info OUT VARCHAR2)

Although, I find it is often more interesting to know where in an application a SQL came from, than which operator activated the code.

Confession

When I first looked at this feature back in the late 1990s, I struggled to see the value of MODULE and ACTION, but by then I was working with PeopleSoft, a packaged application that is still with us, and back then it did not register with the database. I used triggers to enable trace for specific processes, and then I would profile the whole trace file.

Oracle 10g

Two new things in Oracle 10g made DBMS_APPLICATION_INFO very much more interesting that it had seemed to be.

• DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE 
• Active Session History 

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE 

From Oracle 10g, it became possible to specify values of MODULE and/or ACTION for which the database would automatically enable SQL Trace. It was like setting a watch point in a debugger. Trace would be disabled when the module/action changed to something else.

BEGIN
 sys.dbms_monitor.serv_mod_act_trace_enable
 (service_name=>'P1PPP'
 ,module_name =>'RECV_PO'
 ,waits=>TRUE
 ,binds=>TRUE);
END;
/

So, if your application set module and action to meaningful, predictable values, you now had an easy way of enabling SQL trace (See also Enabling Oracle Extended SQL Trace by Module and Action). This is why I generally avoid putting the values of variables into module or action because it makes it difficult to enable trace by this method, unless perhaps the variable has relatively few known values.

Active Session History 

ASH was introduced in 10g. It is only available on Enterprise Edition and it requires the Diagnostics Pack licence. Every second, the database records the status of active database sessions in a buffer in memory. Every 10th sample is persisted to a table in the Automatic Workload Repository (AWR). Module and Action are collected as a part of that. Hence, you can analyse the ASH data with SQL Query.

WITH x AS (
SELECT  h.*
,  CASE WHEN h.program LIKE 'PS%' THEN REGEXP_SUBSTR(h.program,'[^.@]+',1,1) ELSE h.program END as program2
FROM dba_hist_active_Sess_history h
), y AS (
SELECT  x.program2, x.module, x.action, sum(10) ash_Secs
FROM x 
GROUP BY x.module, x.action, x.program2
)
SELECT * FROM y
WHERE  ash_secs >= 300
ORDER BY  ash_Secs desc
/

This query profiles the amount of database time by program, module and action. We can see which modules and actions taken the most database time. Then we could look for specific SQL statements associated with a particular module.

                                                                                     ASH
PROGRAM2                 MODULE                      ACTION                         Secs
------------------------ --------------------------- -------------------------- -------- 
PSAPPSRV                 RAE_EVENT_DETAIL            RAE_EVENT_DETAIL               5800
PSAPPSRV                 RC_CASE                     RC_CASE                        2100
PSAPPSRV                 RC_CASE                     RC_CASE_TSK                     780
PSAPPSRV                 RA_CM_HOME_GRD              RA_CM_HOME_GRD                  630
PSAPPSRV                 XX_PGM_CASE                 XX_PGM_CASE                     600
PSAPPSRV                 RB_TSK                      RB_TSK                          440
oracle@XXX1 (LGWR)                                                                   340
oracle@XXX2 (LGWR)                                                                   330
…

Module and Action are also visible in:

• Enterprise Manager: This example shows two actions for a specific module
  
• AWR reports: The profile of SQL statements reports the module.

Elapsed Time (s)	Execs	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
305.11	1	305.11	10.62	58.94	0.97	dcxx3tvxsm4az	RA_CM_HOME_GRD	SELECT BUSINESS_UNIT, RA_CAMPA...
110.51	342	0.32	3.85	47.67	0.00	bvbbkrnyjbyph	RAE_EVENT_DETAIL	SELECT PRCSINSTANCE, JOBINSTAN...
41.42	340	0.12	1.44	48.73	0.00	cb3p0zbpuw2ha	RAE_EVENT_DETAIL	SELECT BUSINESS_UNIT, RA_CAMPA...
31.74	4	7.94	1.10	57.65	0.00	7qh8s1qgz0axd	RBQ_BOSRCH_QCREATE	SELECT * FROM ( SELECT DISTINC...
25.49	812	0.03	0.89	51.36	0.29	0fj122xjvgpuc		SELECT /* OPT_DYN_SAMP */ /*+ ...
24.49	1,782	0.01	0.85	49.04	0.00	9d8rmyu6hzh5v		SELECT /* OPT_DYN_SAMP */ /*+ ...
22.50	4,232	0.01	0.78	48.38	0.00	16fx1qf2ujdrw		SELECT /* OPT_DYN_SAMP */ /*+ ...
21.63	1,543	0.01	0.75	50.30	0.00	8nzp1phr63835	XX_PGM_CASE	SELECT PROVIDER_GRP_ID, PERSON...
19.40	1	19.40	0.68	34.48	0.00	1atzwyuw0mqwn	RBQ_BOSRCH_QCREATE	SELECT * FROM ( SELECT DISTINC...
19.31	301,677	0.00	0.67	46.24	0.00	2t98byasd6zzk	RC_CASE	SELECT EOCF_LIB_TERM_ID, EOCF_...

Other Features

• Resource Manager: policies can be specified for a module and/or action. Which could be interesting, but I haven't played with it personally.  

How to Instrument PL/SQL 

I am not a developer, and so it is not my day job to write PL/SQL. However, when I do, I instrument the code by adding calls to DBMS_APPLICATION_INFO as follows:

• I store the current values of module and action at the start of each procedure in local variables.
• I set the action to the name of the procedure.  I may set other values during the procedure.
• The original values are reinstated at every exit point from the procedure.
• I have a constant with the name of the procedure. I use this to set the value of module.

k_module CONSTANT VARCHAR2(64) := $$PLSQL_UNIT;
…
PROCEDURE my_procedure IS
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
BEGIN
  dbms_application_info.read_module(module_name=>l_module
                                   ,action_name=>l_action);
  dbms_application_info.set_module(module_name=>k_module
                                  ,action_name=>'MY_PROCEDURE');
…
/*application code goes here*/
…
  dbms_application_info.set_module(module_name=>l_module
                                  ,action_name=>l_action);
EXCEPTION
  WHEN … THEN
    dbms_application_info.set_module(module_name=>l_module
                                    ,action_name=>l_action);
    RAISE / EXIT
…
END my_procedure;

I would characterise this as an investment rather than an overhead during development, in the same way, debug code is an investment that will pay dividends later if you are debugging a functional problem. The overhead of the DBMS_APPLICATION_INFO calls is trivially small, and they present the opportunity to make performance improvement activities much more effective for both developers and DBAs.

What did Instrumentation ever do for us? 

This chart is taken from EDB360 for yet another Java system that does not have any Instrumentation. Module defaults to the program name. A profile of DB time by module is not particularly useful when nearly everything has the same module.

For contrast, this example is taken from a PeopleSoft system. PeopleSoft is now instrumented out-of-the-box. With instrumentation, you can see that the time is broken down between the various components of the system. Now you can see which components consume the most time and justify the most attention. Many of the module names are meaningful to the developers.

Unfortunately, very few developers or package vendors do this. This includes Oracle, although they have instrumented their E-Business Suite and PeopleSoft ERP packages, many others are not.

Conclusion 

Nothing in this story is new.

• ASH: Oracle 10g, 2003
• DBMS_APPLICATION_INFO: Oracle 7.3.3, 1997 
• Wait Interface: Oracle 7, 1992
• Session SQL Trace: Oracle 6, 1988

I write and talk about it instrumentation because I think it is impossible to overstate the value of instrumentation to an application using DBMS_APPLICATION_INFO and yet it is very rare to find instrumented systems.
I would characterise instrumentation, along with debug code, as a small investment during development that could pay big dividends later, rather than an overhead.  Just as effective debug code can help you debug a functional problem, effective instrumentation can help you analyze performance issues.
The overhead of the DBMS_APPLICATION_INFO calls is trivially small, and they make performance improvement activities much easier for both developers and DBAs.  So really, it doesn't cost you anything, it will save you effort and resource later.

*My thanks to Mogens Nørgaard and to Peter Gram who is the keeper of the Oracle Museum at Miracle in Denmark

Complete Refresh of Materialized Views: Atomic, Non-Atomic and Out-of-Place

Recently, and more than once, I have found myself explaining exactly how an Oracle database performs a complete refresh of a materialized view, and what are the implications.  So I thought I would set it out in a blog post.
I am only looking at the case where it is not possible to incrementally refresh using on a materialized view log because the materialized view joins tables together, groups data, or breaches one of the other restrictions (see General Restrictions on Fast Refresh).

Behaviour of DBMS_MVIEW.REFRESH

Materialized views are managed by the delivered DBMS_MVIEW package.  The REFRESH procedure can refresh one or more materialized views.  I want to discuss at the effect of the ATOMIC_REFRESH parameter.  This parameter has been present since at least Oracle 8i when materialized views were called snapshots, so none of this is new.

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}
…
   atomic_refresh         IN     BOOLEAN        := true,
…
);

This is what the Oracle 12c documentation says:
"If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed non-atomically in separate transactions.
As part of complete refresh, if truncate is used (non-atomic refresh), unique index rebuild is executed. INDEX REBUILD automatically computes statistics. Thus, statistics are updated for truncated tables."
It is possible to see the behaviour of the refresh procedure by using SQL trace.  My tests were run on a 12c database.  I have cut a lot out of the traces to make them easier to read.

Trace of Atomic Refresh

• All constraints are deferred to the end of the transaction which is not until all the specified materialized views are refreshed.
• For each materialized view, all rows are deleted
• Then the complete content is reinserted.
• If more than one materialised view is refreshed in a refresh group, or by specifying them in a single execution of refresh, they are refreshed in a single transaction.

Trace file /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_27100_ATOMIC.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
System name: Linux
Node name: localhost.localdomain
Release: 3.8.13-68.1.3.el7uek.x86_64
Version: #2 SMP Wed Apr 22 11:51:54 PDT 2015
Machine: x86_64
=====================
PARSING IN CURSOR #140647220341720 len=73 dep=0 uid=156 oct=47 lid=156 tim=2204121046481 hv=3058798929 ad='c86e9600' sqlid='1rz3cakv534aj'
BEGIN DBMS_MVIEW.REFRESH('MY_EXAMPLE2','C', atomic_refresh=>TRUE); END;
END OF STMT
PARSE #140647220341720:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=2204121046475
=====================
PARSING IN CURSOR #140647220211192 len=28 dep=1 uid=156 oct=90 lid=156 tim=2204121059425 hv=3879155062 ad='c88f49d0' sqlid='231qv0rmmfdbq'
SET CONSTRAINTS ALL DEFERRED
END OF STMT
PARSE #140647220211192:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=2204121059418
EXEC #140647220211192:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=2204121059610
CLOSE #140647220211192:c=0,e=9,dep=1,type=0,tim=2204121059737
=====================
PARSING IN CURSOR #140647218302768 len=34 dep=1 uid=156 oct=7 lid=156 tim=2204121175365 hv=812824424 ad='c86e1658' sqlid='46t32c0s75dv8'
 delete from "SCOTT"."MY_EXAMPLE2"
END OF STMT
PARSE #140647218302768:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2149302637,tim=2204121175360
EXEC #140647218302768:c=0,e=303,p=0,cr=3,cu=2,mis=0,r=1,dep=1,og=1,plh=2149302637,tim=2204121175801
pw=0 time=93 us cost=2 size=0 card=1)'
=====================
PARSING IN CURSOR #140647218302768 len=177 dep=1 uid=156 oct=2 lid=156 tim=2204121185717 hv=1441390243 ad='c86d91c8' sqlid='9ctg639aymrp3'
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MY_EXAMPLE2" SELECT 2 id
,      sysdate my_system_date
,      count(*) X
from kill_cpu
connect by n > prior n
start with n = 1
END OF STMT
PARSE #140647218302768:c=9000,e=8028,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=2203518424,tim=2204121185711
*** 2016-07-11 14:44:24.152
EXEC #140647218302768:c=11189000,e=11191474,p=0,cr=4,cu=3,mis=0,r=1,dep=1,og=1,plh=2203518424,tim=2204132377380
CLOSE #140647218302768:c=0,e=18,dep=1,type=0,tim=2204132377809

Trace of Non-Atomic Refresh

• Again, all constraints are deferred to the end of the transaction, which this time is only as far as the truncate statement.
• An exclusive lock is obtained on the materialized view to ensure that there are no in-flight transactions on it.
• The materialized view is then truncated.  The purge materialized view log option is specified in case another materialized view is based on this materialized view
• The materialized view is repopulated, but in direct path mode because the APPEND hint is specified.
• Stats are automatically collected on a 12c database (note this statement is one level deeper).

Trace file /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_27100_NOTATOMIC.trc
=====================
PARSING IN CURSOR #140647224204240 len=74 dep=0 uid=156 oct=47 lid=156 tim=2204109695504 hv=2544739527 ad='c8c758b8' sqlid='fm9xzbfbuv967'
BEGIN DBMS_MVIEW.REFRESH('MY_EXAMPLE1','C', atomic_refresh=>FALSE); END;
END OF STMT
PARSE #140647224204240:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=2204109695500
=====================
PARSING IN CURSOR #140647223494264 len=28 dep=1 uid=156 oct=90 lid=156 tim=2204109716722 hv=3879155062 ad='c88f49d0' sqlid='231qv0rmmfdbq'
SET CONSTRAINTS ALL DEFERRED
END OF STMT
PARSE #140647223494264:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=2204109716718
EXEC #140647223494264:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=2204109716836
CLOSE #140647223494264:c=0,e=15,dep=1,type=0,tim=2204109717294 
=====================
PARSING IN CURSOR #140647218294296 len=59 dep=2 uid=156 oct=26 lid=156 tim=2204109881174 hv=846098398 ad='c8831a50' sqlid='8td4wj8t6wvyy'
LOCK TABLE "SCOTT"."MY_EXAMPLE1" IN EXCLUSIVE MODE  NOWAIT 
END OF STMT
PARSE #140647218294296:c=1000,e=759,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=2204109881168
EXEC #140647218294296:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=2204109881317
CLOSE #140647218294296:c=0,e=11,dep=2,type=0,tim=2204109881369
=====================
PARSING IN CURSOR #140647218302768 len=56 dep=1 uid=156 oct=85 lid=156 tim=2204109882394 hv=4078491682 ad='c8838320' sqlid='1hkq5cgtjjq12'
 truncate table "SCOTT"."MY_EXAMPLE1" purge snapshot log
END OF STMT
PARSE #140647218302768:c=3000,e=3255,p=0,cr=6,cu=1,mis=1,r=0,dep=1,og=1,plh=1592878302,tim=2204109882390
=====================
PARSING IN CURSOR #140647218302768 len=185 dep=1 uid=156 oct=2 lid=156 tim=2204109979170 hv=3331603390 ad='c8824c90' sqlid='bag14pr398fxy'
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "SCOTT"."MY_EXAMPLE1" SELECT 1 id
,      sysdate my_system_date
,      count(*) X
from kill_cpu
connect by n > prior n
start with n = 1
END OF STMT
PARSE #140647218302768:c=58000,e=58028,p=0,cr=178,cu=0,mis=1,r=0,dep=1,og=1,plh=1420092967,tim=2204109979166
CLOSE #140647224167168:c=1000,e=608,dep=2,type=1,tim=2204120957626
=====================
PARSING IN CURSOR #140647224532056 len=124 dep=2 uid=0 oct=47 lid=0 tim=2204120958103 hv=1731287439 ad='d54ed0e0' sqlid='827tjqdmm2qcg'
BEGIN    dbms_stats.postprocess_stats(:owner, :tabname, :partname,      :subpartname, 2, :rawstats, :selmap, :clist);  END; 
END OF STMT
PARSE #140647224532056:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=0,tim=2204120958098

Comparison

While there are lots of advantages to non-atomic refresh, but perhaps the most significant disadvantage for developers is that the data disappears during refresh.  I think it is this that leads to widespread use of atomic refresh, but in the next section I show how this can be overcome.

Atomic Refresh	Non-Atomic Refresh
Delete, Insert, Commit	Truncate, Insert /*+APPEND*/
Space occupied by deleted rows not available to be reused until the delete is committed, which is not until after the insert.	High water marks reset by truncate. All space in table released immediately. Table likely to be about half size of atomically refreshed materialized view.
Significant undo (and therefore also redo on undo) overhead.	Minimal undo overhead.
Delete and Insert performed row-by-row. Indexes are maintained during DML.	Insert performed in direct path mode. Faster. Indexes rebuilt when insert committed.
Table can be compressed by explicitly rebuilding after refresh, or requires Advanced Compression Licence.	Table data can be compressed using basic compression without needing Advanced Compression Licence. This would also work well with Hybrid Columnar Compression (HCC) on Exadata.
Statistics are not collected. It may be necessary to collect them manually.	In 12c, table statistics will automatically be updated.
Content of materialized view always available due to read consistency.	Materialized view will appear to be empty while refresh process is running.
Refresh of multiple materialized views can occur in a single transaction so that they remain consistent.	Each materialized view refreshed independently.

Non-Atomic Refresh Without Temporarily Losing the Data

In this section I want to explain how to evade the problem of the data disappearing from the materialized view during non-atomic refresh.  Like all the best conjuring tricks, you require two materialized views!  First, I will describe a workaround for use with Oracle 11g or earlier, then I will look at a new feature in Oracle 12c.
I want to create a simple stand-alone demonstration.  I need a materialized view that will take several seconds to refresh so I can check whether the data is still available during that process.  So, I need a long running query, for which I am using Jonathan Lewis's kill_cpu script.  On my database, it runs for around 10 seconds.

set timi on
DROP TABLE kill_cpu PURGE
/
create table kill_cpu(n primary key) organization index as 
select rownum from dual connect by level <=1
/
--this is Jonathan Lewis' killcpu query - duration doubles for every row in kill_cpu table - so the MVs take time to refresh
select count(*) X 
from kill_cpu 
connect by n > prior n 
start with n = 1 
/

I am going to create two identical materialized views based on (almost) the same query.

--create 2 identical MVs.  this time they will be fast to build
DROP MATERIALIZED VIEW my_example1;
DROP MATERIALIZED VIEW my_example2;

CREATE MATERIALIZED VIEW my_example1
COMPRESS PCTFREE 0
BUILD IMMEDIATE
AS 
SELECT 1 id
,      sysdate my_system_date
,      count(*) X 
from kill_cpu 
connect by n > prior n 
start with n = 1 
/

CREATE MATERIALIZED VIEW my_example2
COMPRESS PCTFREE 0
BUILD IMMEDIATE
AS 
SELECT 2 id
,      sysdate my_system_date
,      count(*) X 
from kill_cpu 
connect by n > prior n 
start with n = 1 
/

--this makes the query in the MVs slow to execute
TRUNCATE TABLE kill_cpu;
INSERT INTO kill_cpu
SELECT rownum FROM dual CONNECT BY LEVEL <=24
/
commit
/

I can see my materialized views and when they were last refreshed

alter session set nls_date_Format = 'hh24:mi:ss dd.mm.yyyy';
--list MVs and last refresh dates
column mview_name format a20
select mview_name, last_refresh_date, last_refresh_end_time
from user_mviews
order by last_refresh_date
/

MVIEW_NAME           LAST_REFRESH_DATE   LAST_REFRESH_END_TI
-------------------- ------------------- -------------------
MY_EXAMPLE1          14:44:01 11.07.2016 14:44:12 11.07.2016
MY_EXAMPLE2          14:44:12 11.07.2016 14:44:23 11.07.2016

The following PL/SQL block

• refreshes the older version of the two materialized views
• It then recreates a view on the freshly refreshed materialized view.
• The application should reference this view, and so it will always point to the most up-to-date materialized view.
• The view will refer to the older materialized until the refresh is complete so the data will never disappear.
• Complex view merging means that any query will seamlessly reference the underlying table and the performance will be as if the application had referred to the materialized view.
• In-flight queries will not be affected by the view being rebuilt. They will continue to run against the now older materialized view.

--refresh MVs, collect stats, rebuild view, 
set serveroutput on timi on
DECLARE
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_mview_owner VARCHAR2(128) := 'SCOTT';
  l_mview_name  VARCHAR2(128);
  l_sql CLOB;
BEGIN
  dbms_application_info.read_module(module_name=>l_module, action_name=>l_action);
  dbms_application_info.set_module(module_name=>'MV_EXAMPLE', action_name=>'Initialise');
  WITH x AS (
    SELECT ROW_NUMBER() OVER (ORDER BY last_refresh_date) as ranking
    ,      mview_name, last_refresh_date  
    FROM   user_mviews
    WHERE  owner = l_mview_owner
    AND    mview_name IN('MY_EXAMPLE1','MY_EXAMPLE2')
  ) 
  SELECT mview_name
  INTO   l_mview_name
  FROM   x
  WHERE  ranking = 1;

  dbms_application_info.set_action(action_name=>'Refresh '||l_mview_name);  
  dbms_output.put_line('About to refresh mview '||l_mview_name);
  DBMS_MVIEW.REFRESH(l_mview_name,'C', atomic_refresh=>FALSE);

  --add stats collection here – not necessary in 12c
  --dbms_application_info.set_action(action_name=>'Collect Stats '||l_mview_name);  
  --dbms_stats.gather_Table_stats(ownname=>l_mview_owner, tabname=>l_mview_name, cascade=>TRUE);

  --rebuild view to point to MV just refreshed  
  dbms_application_info.set_action(action_name=>'Rebuild view MY_EXAMPLE');  
  l_sql:= 'CREATE OR REPLACE VIEW my_example AS SELECT * FROM '||l_mview_name;
  dbms_output.put_line('SQL:'||l_sql);
  EXECUTE IMMEDIATE l_sql;

  --reinstate initial settings for module and action
  dbms_application_info.set_module(module_name=>l_module, action_name=>l_action);
END;
/
select mview_name, last_refresh_date, last_refresh_end_time
from user_mviews
order by last_refresh_date
/

The first time I ran the script MY_EXAMPLE1 was refreshed and the view was rebuilt to point to it.

About to refresh mview MY_EXAMPLE1
SQL:CREATE OR REPLACE VIEW my_example AS SELECT * FROM MY_EXAMPLE1

MVIEW_NAME           LAST_REFRESH_DATE   LAST_REFRESH_END_TI
-------------------- ------------------- -------------------
MY_EXAMPLE2          14:44:12 11.07.2016 14:44:23 11.07.2016
MY_EXAMPLE1          10:39:57 12.07.2016 10:40:06 12.07.2016

The next time I ran the script MY_EXAMPLE2 was refreshed and the view was again rebuilt.

About to refresh mview MY_EXAMPLE2
SQL:CREATE OR REPLACE VIEW my_example AS SELECT * FROM MY_EXAMPLE2

MVIEW_NAME           LAST_REFRESH_DATE   LAST_REFRESH_END_TI
-------------------- ------------------- -------------------
MY_EXAMPLE1          10:39:57 12.07.2016 10:40:06 12.07.2016
MY_EXAMPLE2          10:40:50 12.07.2016 10:40:59 12.07.2016

Summary 

There is a trade-off to this approach, but I suggest that the advantages of this approach to using non-atomic refresh outweigh the additional complexities.

Advantages	Disadvantanges
Data is always available during materialized view refresh.	The refresh and view rebuild process is more complex, though it could be encapsulated into a PL/SQL procedure.
Each materialized view will be half the size of the atomically refresh materialized view because space is freed by the delete is only available after the commit. So the overall space requirement is about the same as atomic refresh.	Two copies of the materialized view and its indexes are required.
Refresh will be faster because indexes are not maintained during the insert, but are rebuilt afterwards.
Queries no longer run on view being refreshed, so database does not have to do consistent reads of data blocks. This should prevent query performance being degraded during refresh.
Basic table compression (or HCC on Exadata) could make it considerably smaller. This should also improve query performance by reducing I/O.
I have not reserved any free space in the table in my demonstration because I know the table will never be updated. Further saving space and reducing I/O.

Out-of-Place Refresh in Oracle 12c

Out-of-place refresh is a new option on DBMS_MVIEW.REFRESH in Oracle 12c.  The complete refresh process builds a new table which seamlessly becomes the materialized view, the old table is dropped.  It only works in conjunction with non-atomic refresh.  It also works with incremental (or fast) refresh.
As with my 11g workaround described above, two copies of the data are used. The refresh process creates and populates a new table with indexes, exchanges the new with the old, and drops the old table. For this example, I have enabled the recycle bin so we can see the dropped table.

exec DBMS_MVIEW.REFRESH('MY_EXAMPLE1','C', atomic_refresh=>FALSE, out_of_place=>TRUE);
select object_id, object_type, object_name, last_ddl_time
from user_objects
where object_name LIKE 'MY_EXAMPLE1'
order by 1
/
select original_name, createtime, droptime, dropscn, base_object, purge_object
from user_recyclebin
order by dropscn
/

Each time I refresh the materialized view, the table gets a new object ID, and a new object appears in the recycle bin.

 OBJECT_ID OBJECT_TYPE             OBJECT_NAME     LAST_DDL_TIME
---------- ----------------------- --------------- -------------------
    112366 MATERIALIZED VIEW       MY_EXAMPLE1     09:50:06 14.07.2016
    112382 TABLE                   MY_EXAMPLE1     08:40:34 14.07.2016

ORIGINAL_NAME   CREATETIME          DROPTIME               DROPSCN BASE_OBJECT
--------------- ------------------- ------------------- ---------- -----------
RV$1B6FD        2016-07-14:09:43:43 2016-07-14:09:50:05   68878082      112381

The object ID of the new entry is the previous object ID of the materialized view. A new table was created and the old table was dropped. The intermediate table name is RV$ followed by the object ID in hexadecimal.

 OBJECT_ID OBJECT_TYPE             OBJECT_NAME     LAST_DDL_TIME
---------- ----------------------- --------------- -------------------
    112366 MATERIALIZED VIEW       MY_EXAMPLE1     09:51:41 14.07.2016
    112383 TABLE                   MY_EXAMPLE1     08:40:34 14.07.2016

ORIGINAL_NAME   CREATETIME          DROPTIME               DROPSCN BASE_OBJECT
--------------- ------------------- ------------------- ---------- -----------
RV$1B6FD        2016-07-14:09:43:43 2016-07-14:09:50:05   68878082      112381
RV$1B6FE        2016-07-14:09:50:03 2016-07-14:09:51:40   68878546      112382

A trace of the refresh process shows the data being inserted into another table. However, note that it is a conventional insert, not a direct-path insert. So statistics will not be collected automatically. The DDL statements at create and drop interim tables are truncated in the trace, but can be reinstated with event 10979, level 1 (see Jonathan Lewis's 12c MView refresh blog).  This event also causes additional progress information about the refresh process to be written to the trace file.

PARSING IN CURSOR #139759670421744 len=178 dep=1 uid=156 oct=2 lid=156 tim=2412454493901 hv=3909484904 ad='d56b62a8' sqlid='0k8chjrnhc0b8'
/* MV_REFRESH (ITB) */INSERT INTO "SCOTT"."RV$1B6E0"  SELECT 1 id
,      sysdate my_system_date
,      n
--,      count(*) X
from kill_cpu
connect by n > prior n
start with n = 1
END OF STMT

Summary

Out-of-Place refresh has some advantages and disadvantages over my 11g method.

Advantages	Disadvantanges
The entire refresh process is encapsulated within the DBMS_MVIEW.REFRESH procedure.
Physical tablespace is still required to hold two copies of the table, but now only during the refresh. The original table is dropped after the new one is built.	If the recycle bin is enabled, every out-of-place refresh will preserve another copy of the table and any indexes. I recommend disabling the recycle bin.
The data is always available while the materialized view is being refreshed.	Long running queries running across the out-of-place refresh will continue to run correctly, at least until the data blocks used to hold the dropped table are overwritten, when an "ORA_08103: object no longer exists" error will be raised (again, see Jonathan Lewis's 12c MView refresh blog).
	The table is populated in conventional rather than direct path mode. Although the indexes are built after population. This has a number of consequences. The performance of the conventional insert will not be as good as direct-path insert. Simple table compression will not have any effect. HCC is probably not an option. Table statistics are not automatically collected by On-Line Statistics Gathering, but the indexes will have statistics computed as they are rebuilt during each refresh.