Refresh of Aggregation Materialized Views is Taking Too Long
Last updated on NOVEMBER 02, 2017
Applies to:Oracle Utilities Meter Data Management - Version 22.214.171.124 and later
Information in this document applies to any platform.
On : 126.96.36.199 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
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.
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