Mohamed Houri’s Oracle Notes

April 18, 2015

Parallel refreshing a materialized view

Filed under: Materialized view — hourim @ 4:58 pm

I have been asked to troubleshoot a monthly on demand materialized view refresh job which has got the bad idea to crash with the ORA-01555 error after 25,833 seconds (more than 7 hours) of execution. Despite my several years of professional experience this is the first time I have been asked to look at a materialized view refresh. This issue came up Friday afternoon so I was given a week-end to familiarize myself with materialized views. Coincidentally a couple of days before there was an Oracle webcast on Materialized view basics, architecture and internal working  which I have replayed on Saturday and practiced its demo. Christian Antognini book contains a chapter on this topic which I have also gone through as far as Christian book is from where I always like to start when trying to learn an Oracle concept.

Materialized view capabilities

The following Monday morning, armed with this week-end accelerated auto-training, I opened again the e-mail I have been sent about the failing refresh job and started re-reading it. The first thing that has retained my attention this time, in contrast to my last Friday quick pass through reading, was a suggestion made by the DBA to try fast refreshing the materialized view instead of completely refreshing it. I learnt from the Oracle webcast that Oracle is able to let us know wether a materialized view can be fast (also know as incremental) refreshed or not. Here below the steps to do if you want to get this information:

You need first to create the mv_capabilities_table table (in the schema you are going to use against it the dbms_mview package) using the following script :

SQL> $ORACLE_HOME/rdbms/admin/utlxmv.sql

SQL> select * from mv_capabilities_table;
no rows selected

Once this table created you can execute the dbms_mview.explain_mview package as shown below:

SQL> exec dbms_mview.explain_mview ('my_materialied_mv');

PL/SQL procedure successfully completed.

SQL> select
  2     mvname
  3    ,capability_name
  4    ,possible
  5  from
  6    mv_capabilities_table
  7  where
  8     mvname = 'MY_MATERIALIED_MV'
  9  and
 10    capability_name  like '%REFRESH%';

MVNAME                         CAPABILITY_NAME                P
------------------------------ ------------------------------ -
MY_MATERIALIED_MV              REFRESH_COMPLETE               Y  
MY_MATERIALIED_MV              REFRESH_FAST                   N --> spot this
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_ONETAB_DML  N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_ANY_DML     N
MY_MATERIALIED_MV              REFRESH_FAST_PCT               N

As spotted above, fast refreshing this materialized view is impossible.

The first learned lesson: instead of trying the create a materialized view log and fast refreshing a complex materialized view which might be impossible to be refreshed incrementally, try first getting the capabilities of the view using the explain_mview procedure. You will certainly save time and resource.

SQL> SELECT
         refresh_method
       , refresh_mode
       , staleness
       , last_refresh_type
       , last_refresh_date
    FROM
          user_mviews
    WHERE mview_name = 'MY_MATERIALIED_MV';

REFRESH_ REFRES STALENESS           LAST_REF LAST_REFRES
-------- ------ ------------------- -------- --------------------
COMPLETE DEMAND NEEDS_COMPILE       COMPLETE 02-APR-2015 16:16:35

Parallel clause in the SQL create statement : any effect on the mview creation?

Since I have ruled out an incremental refresh I decided to get the materialized view definition so that I can investigate its content

SQL> SELECT
       replace (dbms_metadata.get_ddl(replace(
                                      OBJECT_TYPE, ' ', '_'),    
                                      OBJECT_NAME,OWNER)
                    ,'q#"#'
                    ,'q#''#'
                    )
     FROM DBA_OBJECTS
     WHERE OBJECT_TYPE = 'MATERIALIZED VIEW'
     AND object_name = 'MY_MATERIALIED_MV';

------------------------------------------------------------------
CREATE MATERIALIZED VIEW MY_MATERIALIED_MV
   ({list of columns}) 
  TABLESPACE xxxx
  PARALLEL 16 –----------------------------------> spot this
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
-- select n°1
 SELECT
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
  {list of columns}
.../...
UNION ALL
-- select n°5
SELECT
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
GROUP BY
  {list of columns} ;

Have you noticed that parallel 16 clause in the materialized view create script? The developer intention was to create the materialized view using parallel process. Having a Production equivalent database I was happy enough to try re-creating this materialized view:

SQL> set timing on

SQL> start ddl_mv1.sql

Materialized view created.

Elapsed: 00:22:33.52

Global Information
------------------------------
 Status              :  DONE               
 Instance ID         :  1                  
 Session             :  XZYY (901:25027)  
 SQL ID              :  f9s6kdyysz84m      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 09:49:22
 First Refresh Time  :  04/16/2015 09:49:23
 Last Refresh Time   :  04/16/2015 10:11:48
 Duration            :  1346s              
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY
 Program             :  sqlplus.exe         

Global Stats
========================================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
========================================================================
|   20338 |    5462 |    14205 |    63M |   3M | 716GB |    2M | 279GB |
========================================================================

Parallel Execution Details (DOP=16 , Servers Allocated=32)

SQL Plan Monitoring Details (Plan Hash Value=853136481)
==================================================================================================
| Id  |                       Operation            | Name    |  Rows   | Execs |   Rows   |Temp  |
|     |                                            |         | (Estim) |       | (Actual) |(Max) |
==================================================================================================
|   0 | CREATE TABLE STATEMENT                     |         |         |    33 |       16 |      |
|   1 |   PX COORDINATOR                           |         |         |    33 |       16 |      |
|   2 |    PX SEND QC (RANDOM)                     | :TQ10036|         |    16 |       16 |      |
|   3 |     LOAD AS SELECT                         |         |         |    16 |       16 |      |
|   4 |      UNION-ALL                             |         |         |    16 |     117M |      |
|   5 |       HASH GROUP BY                        |         |    259M |    16 |      58M |  36G |
|   6 |        PX RECEIVE                          |         |    259M |    16 |     264M |      |
|   7 |         PX SEND HASH                       | :TQ10031|    259M |    16 |     264M |      |
|   8 |          HASH JOIN RIGHT OUTER BUFFERED    |         |    259M |    16 |     264M |  61G |
|   9 |           PX RECEIVE                       |         |      4M |    16 |       4M |      |
|  10 |            PX SEND HASH                    | :TQ10013|      4M |    16 |       4M |      |
|  11 |             PX BLOCK ITERATOR              |         |      4M |    16 |       4M |      |
|     |                                            |         |         |       |          |      |
| 180 |                PX RECEIVE                  |         |     19M |    16 |      20M |      |
| 181 |                 PX SEND HASH               | :TQ10012|     19M |    16 |      20M |      |
| 182 |                  PX BLOCK ITERATOR         |         |     19M |    16 |      20M |      |
| 183 |                   TABLE ACCESS FULL        | TABLE_M |     19M |   268 |      20M |      |
==================================================================================================

Surprisingly the materialized view has been created in less than 23 minutes. And this creation has been parallelised with a DOP of 16 as shown by the corresponding Real Time Sql Monitoring report (RTSM).The master table has been henceforth created with a DOP of 16 as shown below:

SQL> select
  2    table_name
  3   ,degree
  4  from
  5    user_tables
  6  where table_name = 'MY_MATERIALIED_MV';

TABLE_NAME                     DEGREE
------------------------------ ----------
MY_MATERIALIED_MV               16

A simple select against the created materialized view will go parallel as well

SQL> select count(1) from MY_MATERIALIED_MV;               

SQL Plan Monitoring Details (Plan Hash Value=3672954679)
============================================================================================
| Id |          Operation          |           Name           |  Rows   | Execs |   Rows   |
|    |                             |                          | (Estim) |       | (Actual) |
============================================================================================
|  0 | SELECT STATEMENT            |                          |         |     1 |        1 |
|  1 |   SORT AGGREGATE            |                          |       1 |     1 |        1 |
|  2 |    PX COORDINATOR           |                          |         |    17 |       16 |
|  3 |     PX SEND QC (RANDOM)     | :TQ10000                 |       1 |    16 |       16 |
|  4 |      SORT AGGREGATE         |                          |       1 |    16 |       16 |
|  5 |       PX BLOCK ITERATOR     |                          |    104M |    16 |     117M |
|  6 |        MAT_VIEW ACCESS FULL | MY_MATERIALIED_MV        |    104M |   191 |     117M |
============================================================================================

You might have already pointed out in the above RTSM report that the select part of the “create as select” statement has been parallelised as well. It is as if the parallel 16 clause of the “create” part of the SQL  materialized view script induced implicitly its  “select” part to be done in parallel with a DOP of 16.

Parallel clause in the SQL create statement : any effect on the mview refresh ?

As far as I am concerned, the problem I have been asked to trouble shoot resides in refreshing the materialized view and not in creating it.  Since, the materialized view has been created in 23 minutes, I should be optimistic for its refresh time; isn’t it?

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C',atomic_refresh=>FALSE);

After more than 4,200 seconds of execution time I finally gave up and decided to stop this refresh. Below is an overview of its corresponding Real Time Sql Monitoring (RTSM) report:

Global Information
------------------------------
 Status              :  DONE (ERROR) --> I have cancelled it after more than 1 hour   
 Instance ID         :  1                  
 Session             :  XZYY (901:25027)  
 SQL ID              :  d5n03tuht2cg8      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 10:55:46
 First Refresh Time  :  04/16/2015 10:55:52
 Last Refresh Time   :  04/16/2015 12:06:39
 Duration            :  4253s               
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY
 Program             :  sqlplus.exe         

Global Stats
===================================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
===================================================================================
|    4253 |    1640 |     2563 |       50 |    53M | 824K | 227GB |  570K | 120GB |
===================================================================================

SQL Plan Monitoring Details (Plan Hash Value=998958099)
=============================================================================
| Id |                  Operation                   |Name |  Rows   | Cost  |
|    |                                              |     | (Estim) |       |
=============================================================================
|  0 | INSERT STATEMENT                             |     |         |       |
|  1 |   LOAD AS SELECT                             |     |         |       |
|  2 |    UNION-ALL                                 |     |         |       |
|  3 |     HASH GROUP BY                            |     |    259M |       |
|  4 |      CONCATENATION                           |     |         |       |
|  5 |       NESTED LOOPS OUTER                     |     |       7 |  4523 |
|  6 |        NESTED LOOPS OUTER                    |     |       7 |  4495 |
|  7 |         NESTED LOOPS                         |     |       7 |  4474 |
|  8 |          NESTED LOOPS                        |     |       7 |  4460 |
|  9 |           PARTITION REFERENCE ALL            |     |       7 |  4439 |
…/…

In contrast to the creation process, the materialized view refresh has been done serially. This confirms that the above parallel 16 clause in the create DDL script concerns only the parallel materialized view creation and not its refresh process.

The second learned lesson : I think that a parallel clause specified in the create statement of a materialized view is not used during the refresh of the same materialized view.  The parallel run is considered in this kind of situations only at the materialized view creation time.

dbms_mview.refresh and its parallelism parameter : any effect on the mview refresh ?

The tables on which the materialized view is based have all a degree = 1

 SQL> select
  2      table_name
  3    , degree
  4  from user_tables
  5  where trim(degree) <> '1';

TABLE_NAME            DEGREE
--------------------- -------
MY_MATERIALIED_MV     16

Having said that, what if I try refreshing this materialized view using the parallelism parameter of the dbms_mview.refresh procedure as shown below:

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C', atomic_refresh=>FALSE, parallelism =>16);

SQL Plan Monitoring Details (Plan Hash Value=998958099)
==========================================================================================
| Id |                  Operation                   |           Name           |  Rows   |
|    |                                              |                          | (Estim) |
==========================================================================================
|  0 | INSERT STATEMENT                             |                          |         |
|  1 |   LOAD AS SELECT                             |                          |         |
|  2 |    UNION-ALL                                 |                          |         |
|  3 |     HASH GROUP BY                            |                          |    259M |
|  4 |      CONCATENATION                           |                          |         |
|  5 |       NESTED LOOPS OUTER                     |                          |       7 |
|  6 |        NESTED LOOPS OUTER                    |                          |       7 |
|  7 |         NESTED LOOPS                         |                          |       7 |
|  8 |          NESTED LOOPS                        |                          |       7 |
|  9 |           PARTITION REFERENCE ALL            |                          |       7 |
| 10 |            TABLE ACCESS BY LOCAL INDEX ROWID | TABLE_XX_ZZ              |       7 |
../..
| 94 |           PARTITION RANGE ALL                |                          |    369M |
| 95 |            PARTITION LIST ALL                |                          |    369M |
| 96 |             TABLE ACCESS FULL                | TABLE_AA_BB_123          |    369M |
==========================================================================================

As confirmed by the above corresponding RTSM report, the parallelism parameter has not been obeyed and the refresh has been done serially in this case as well.

The third learned lesson : using the parameter parallelism of the dbms_mview.refresh procedure has no effect on the parallel refresh of the underlying materialized view.

Adding a parallel hint in the select part of the mview : any effect on the mview refresh ?

At this stage of the troubleshooting process I have emphasized the following points:

  • The parallel clause used in the create statement of a materialized view is considered only during the materialized view creation. This parallel clause is ignored during the refresh process
  • The parallelism parameter of the dbms_mview.refresh procedure will not refresh the materialized view in parallel

Now that I have ruled out all the above steps I was almost convinced that to expedite the refresh process I need to add a parallel hint directly in the materialized view definition (ddl_mv2.sql):

CREATE MATERIALIZED VIEW MY_MATERIALIED_MV
   ({list of columns}) 
  TABLESPACE xxxx
  PARALLEL 16
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
 SELECT /*+ parallel(8) pq_distribute(tab1 hash hash)*/
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
  {list of columns}
UNION ALL
 SELECT /*+ parallel(8) pq_distribute(tab1 hash hash)*/
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
    {list of columns}
;

Having changed the select part of materialized view DDL script I launched again it creation which completes in 25 minutes as shown below:

SQL> start ddl_mv2.sql
Materialized view created.
Elapsed: 00:25:05.37

And immediately after the creation I launched the refresh process :

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C',atomic_refresh=>FALSE);

PL/SQL procedure successfully completed.
Elapsed: 00:26:11.12

And hopefully this time the refresh completed in 26 minutes thanks to the parallel run exposed below in the corresponding RTSM report:

Global Information
------------------------------
 Status              :  DONE               
 Instance ID         :  1                  
 Session             :  XZYY
 SQL ID              :  1w1v742mr35g3      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 13:38:13
 First Refresh Time  :  04/16/2015 13:38:13
 Last Refresh Time   :  04/16/2015 14:04:24
 Duration            :  1571s              
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY            
 Program             :  sqlplus.exe         

Parallel Execution Details (DOP=8, Servers Allocated=80)

SQL Plan Monitoring Details (Plan Hash Value=758751629)
===============================================================================
| Id  |                       Operation          |           Name   |  Rows   |
|     |                                          |                  | (Estim) |
===============================================================================
|   0 | INSERT STATEMENT                         |                  |         |
|   1 |   LOAD AS SELECT                         |                  |         |
|   2 |    UNION-ALL                             |                  |         |
|   3 |     PX COORDINATOR                       |                  |         |
|   4 |      PX SEND QC (RANDOM)                 | :TQ10005         |    259M |
|   5 |       HASH GROUP BY                      |                  |    259M |
| 177 |                PX RECEIVE                |                  |     19M |
| 178 |                 PX SEND HASH             | :TQ50004         |     19M |
| 179 |                  PX BLOCK ITERATOR       |                  |     19M |
| 180 |                   TABLE ACCESS FULL      | TABLE_KZ_YX      |     19M |
===============================================================================

I’ve added the pq_distribute (tab1 hash hash) hint above because several refreshes crashed because of the broadcast distribution that ended up by overconsuming TEMP space raising the now classical error:

ERROR at line 484:
ORA-12801: error signaled in parallel query server P012
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The fourth learned lesson : if you want to parallelise your materialized view refresh process you had better to include the parallel hint in the select part of the materialized view. This is better than to change the parallel degree of the tables on which the materialized view is based on.

Advertisements

6 Comments »

  1. Nice article, learned several new things…Keep going.

    Comment by promisinganuj — April 18, 2015 @ 6:52 pm | Reply

  2. Nice article…. Step by Step analysis were clearly put down..!! (Y)..
    Good Sharing!!!

    Comment by Deepak Mahto — April 19, 2015 @ 7:13 pm | Reply

  3. same here, never asked to do that kind of ‘look’ but your insights were very helpful to me for the future

    Comment by oraclemanconsulting — April 22, 2015 @ 11:23 pm | Reply

  4. Mohamed,
    interesting results.

    To add another link: when I have to work with fast refreshable MViews I tend to take a look at Alberto Dell’Era’s page – http://www.adellera.it/blog/category/materialized-views. In ancient times it was common knowledge that the “fast” in fast refresh was a bad joke since this refresh type was certainly incremental but not exactly fast: even a small delta could make a complete refresh more efficient. But the mechanism has been improved and is rather elaborated in current releases and Alberto gives a very instructive explanation of the internal strategies involved.

    Regards

    Martin

    Comment by Martin Preiss — May 5, 2015 @ 11:25 am | Reply

  5. Hi Martin

    Thanks for Alberto’s links. Very interesting

    Best regards

    Comment by hourim — May 8, 2015 @ 4:29 pm | Reply

  6. A little tip. Before executing the refresh with parallelism, execute this. No need to change the mview body.

    ALTER SESSION FORCE PARALLEL DML PARALLEL 16;
    ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;

    Comment by Marcus — March 7, 2017 @ 5:39 pm | 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

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: