Fixed for Materialized View slow refresh or hangs (1)

Fixed for  Materialized View slow refresh or hangs.

 I did a performance tuning on a database where the application developer, literally are solving all performance problems with materialized views!?.
redo log switch occurs 300 times within an hour because 5 minute interval of COMPLETE refresh.
FAST refresh was not use because its hangs. the following highlights how issue was resolved MV slow refresh or hangs.

Lock MV log statistics immediately after creation and  alter system set "_mv_refresh_use_stats"=FALSE;
See Doc ID 420040.1

1. this is what Explain plan of materialized view refresh look like this before above recommendation was applied.

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 4948M(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | MVIEW_EVOLUTION_SUMMARY_DATA | | | | | | | |
| 2 | FILTER | | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 1352K| 167M| | 78593 (1)| 00:00:04 | | |
| 4 | NESTED LOOPS OUTER | | 1352K| 136M| | 78591 (1)| 00:00:04 | | |
| 5 | NESTED LOOPS OUTER | | 1352K| 107M| | 78590 (1)| 00:00:04 | | |
| 6 | HASH JOIN RIGHT OUTER | | 1352K| 77M| | 78589 (1)| 00:00:04 | | |
| 7 | VIEW | | 12840 | 426K| | 70990 (1)| 00:00:03 | | |
| 8 | HASH JOIN SEMI | | 12840 | 2156K| 293M| 70982 (1)| 00:00:03 | | |
| 9 | TABLE ACCESS FULL | APP_INCREA_EVENT | 6679K| 216M| | 52261 (1)| 00:00:03 | | |
| 10 | TABLE ACCESS FULL | MLOG$_APP_INCREA_EVENT | 20545 | 2768K| | 4028 (1)| 00:00:01 | | |
| 11 | TABLE ACCESS FULL | APP_TRANSACTION | 1352K| 33M| | 7595 (1)| 00:00:01 | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| APP_INCOMES_QUALIFICATION | 1 | 23 | | 1 (0)| 00:00:01 | ROWID | ROWID |
| 13 | INDEX UNIQUE SCAN | APP_INCOMES_QUALIFICATION_PK | 1 | | | 0 (0)| | | |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | APP_INCOMES_QUALIFICATION | 1 | 23 | | 1 (0)| 00:00:01 | ROWID | ROWID |
| 15 | INDEX UNIQUE SCAN | APP_INCOMES_QUALIFICATION_PK | 1 | | | 0 (0)| | | |
| 16 | TABLE ACCESS BY GLOBAL INDEX ROWID | APP_INCOMES_CUSTOMER | 1 | 24 | | 2 (0)| 00:00:01 | ROWID | ROWID |
| 17 | INDEX UNIQUE SCAN | APP_INCOMES_CUSTOMER_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 18 | FILTER | | | | | | | | |
| 19 | MAT_VIEW ACCESS FULL | MVIEW_EVOLUTION_SUMMARY_DATA | 1 | 10 | | 3663 (1)| 00:00:01 | | |
| 20 | FILTER | | | | | | | | |
| 21 | NESTED LOOPS | | 1 | 24 | | 3 (0)| 00:00:01 | | |
| 22 | TABLE ACCESS BY USER ROWID | APP_TRANSACTION | 1 | 18 | | 1 (0)| 00:00:01 | | |
| 23 | INDEX RANGE SCAN | INCREA_EVENT_2_TRANSACTION_IDX | 1 | 6 | | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

  1. if MV log exist, drop and recreate.

1.   drop materialized view log on crs_change_event;
2.   CREATE MATERIALIZED VIEW LOG ON USERS_APP.APP_INCREA_EVENT TABLESPACE "APP_TBS"

  2. gather statistics on MV LOGS and lock the statisctics.

1. exec DBMS_STATS.gather_TABLE_STATS('USERS_APP','MLOG$_APP_INCREA_EVENT',force=>true);
2. exec DBMS_STATS.LOCK_TABLE_STATS('USERS_APP','MLOG$_APP_INCREA_EVENT');

3. alter system

1.  alter system set "_mv_refresh_use_stats"=FALSE;

4. create MV

1. CREATE MATERIALIZED VIEW "USERS_APP"."MVIEW_EVOLUTION_SUMMARY_DATA".....

After the remommedation. you can see a signficant improvement in the Cost section of both plans. and it using an hash join instead of a nested loops.

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 100K(100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | MVIEW_EVOLUTION_SUMMARY_DATA | | | | | | | |
| 2 | HASH JOIN RIGHT OUTER | | 6532K| 809M| | 100K (1)| 00:00:04 | | |
| 3 | PARTITION LIST ALL | | 44179 | 992K| | 248 (1)| 00:00:01 | 1 | 3 |
| 4 | TABLE ACCESS FULL | APP_INCOMES_QUALIFICATION | 44179 | 992K| | 248 (1)| 00:00:01 | 1 | 3 |
| 5 | HASH JOIN RIGHT OUTER | | 6532K| 666M| | 100K (1)| 00:00:04 | | |
| 6 | PARTITION LIST ALL | | 44179 | 992K| | 248 (1)| 00:00:01 | 1 | 3 |
| 7 | TABLE ACCESS FULL | APP_INCOMES_QUALIFICATION | 44179 | 992K| | 248 (1)| 00:00:01 | 1 | 3 |
| 8 | HASH JOIN RIGHT OUTER| | 6532K| 523M| 220M| 100K (1)| 00:00:04 | | |
| 9 | PARTITION LIST ALL | | 6426K| 147M| | 33553 (1)| 00:00:02 | 1 | 3 |
| 10 | TABLE ACCESS FULL | APP_INCOMES_CUSTOMER | 6426K| 147M| | 33553 (1)| 00:00:02 | 1 | 3 |
| 11 | HASH JOIN OUTER | | 6532K| 373M| 49M| 33239 (1)| 00:00:02 | | |
| 12 | TABLE ACCESS FULL | APP_TRANSACTION | 1352K| 33M| | 2065 (1)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | APP_INCREA_EVENT | 6679K| 216M| | 14184 (1)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------


5.  Query dba_mviews to check the staleness.

1. select a.LAST_REFRESH_DATE,a.staleness,a.after_fast_refresh,a.MVIEW_NAME from dba_mviews a where owner='USERS_APP'

1. LAST_REFRESH_DATE   STALENESS AFTER_FAST_REFRESH  MVIEW_NAME
2. ------------------- ------------------- ------------------- --------------------------------------------------------------------------------------------------------------------------------
3. 2017-08-26 13:37:37 FRESH FRESH    MVIEW_EVOLUTION_SUMMARY_DATA 
4.   
5.   

Version: 12.1