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.
Mohamed Houri’s Oracle Notes 