Refresh of Aggregation Materialized Views is Taking Too Long (Doc ID 2133171.1)

Last updated on MAY 09, 2016

Applies to:

Oracle Utilities Meter Data Management - Version 2.1.0.2 and later
Information in this document applies to any platform.

Symptoms

On : 2.1.0.2 version, Installation

ACTUAL BEHAVIOR
---------------
Refresh of Aggregation Materialized views is taking too long

Materialized View refresh for AGR (e.g. D2_QUALITY_CNT_AGR_MV, D2_MEASR_QTY_AGR_MV, D2_TIMELINES_CNT_AGR_MV) views related to BI integration is taking too long (close to 4-5 hours on a normal day, close to 10 hours if multiple days' worth of usage is loaded simultaneously)
Upon trying to run this job in tandem with the D1-IMD batch job causes this job to halt and take over 10 hours.
 
The batch job D2-MVREF calls a stored procedure CISADM.D2_MV_REFRESH_PROC, which upon further research we found only provides the option to completely refresh the materialized views, also presently the stored procedure does not set the ATOMIC_REFRESH property as false while calling the DBMS_MVIEW.REFRESH procedure
The following lines are from the procedure
DBMS_MVIEW.REFRESH('''||tbl_owner||'.'||mv_name||''',''C''); END;

Out of the box this property defaults to true in 9i and up and can cause performance issues especially if complete refresh is used, as Deletes are used to clear the table instead of using a Truncate statement.

Request is to provide a resolution of this performance problem, as it obstructs our ability run the D1-IMD job in parallel and limit the number of hours available for other processing.

EXPECTED BEHAVIOR
-----------------------
Expect this to run in a timely manner

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run a materialized view batches as listed above.
2. Wait
3. Note completion time 4-5 hours for one day elapsed time.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot un the D1-IMD job in parallel and limits the number of hours available for other processing

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms