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

Last updated on NOVEMBER 02, 2017

Applies to:

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


On : version, Installation

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.

Expect this to run in a timely manner

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.

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


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