CODEWITHSUNDEEP
oracle-databaseindexingperformance-testingmaterialized-views
Tim Sanders
Tim Sanders

Reputation: 851

Performance Degradation During Materialized View Complete Refresh With Indexes Active

Problem: A significant performance degradation during complete refresh when indexes are active. I'm not really sure why having indexes active during a complete refresh would cause a significant difference in performance. At the moment our data warehouse has an issue with over indexing, but I was astonished to see a huge performance degradation with even just one active index vs. no active index on a complete refresh.

Oracle Version 12c

Research: Materialized view refresh terrible performance degradation I found this on SO, but it doesn't necessarily answer my question why the indexes would cause a reduction in performance. I may proceed with the suggestion to drop indexes and rebuild after the complete refresh, but I'm still trying figure out the WHY.

Example Performance Test: I have many MVs, but this is an example of how I tested the MV and the costs associated. I've tested about 10 MVs and they all show the same pattern. Please note, I modified the code to remove all of the object names

With all indexes active:

exec dbms_mview.refresh('MY_MV_TEST','C');

Real time exec as reported from SQL Developer: ~153s

Getting performance:

SELECT elapsed_time, log_purge_time
FROM dba_mvref_stats
....

elapsed_time = 151 log_purge_time = 1

ALTER INDEX IX_MY_MV_TEST_1 UNUSABLE;
....
ALTER INDEX IX_MY_MV_TEST_13 UNUSABLE;

Re-run complete refresh:

exec dbms_mview.refresh('MY_MV_TEST','C');

Get stats from dba_mvref_stats:

elapsed_time = 27 log_purge_time = 1

It was a bit astonished, so I tried 1 by 1, with only 1 index active at a time. For each index it was reported an elapsed_time of 33 and log_purge_time of 2 (I thought it was a bit weird they all reported the same time too). There are a few other MVs that go from 300s to 40s as well. I've only tested on a small subset of our data warehouse so far, and I'm going to assume some of our larger MVs will show the same results. The rebuild of the indexes only takes 11s as reported by SQL developer.

MV DDL: It would take some time to rename all objects, but if necessary I will if needed. For right now, this is the general over view of this particular MV defintion. In the SELECT clause there are only columns, a couple case statements, and a couple substr(), and cast().

CREATE MATERIALIZED VIEW MY_MV_TEST 
BUILD DEFERRED 
USING INDEX REFRESH FORCE ON DEMAND 
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS AS
SELECT column1, column2, CASE..., SUBSTR(..), CAST()...
FROM mv1, mv2, mv3
WHERE mv1.column1 = mv2.column1
AND mv1.column1 = mv3.column1
AND ... (other simple conditions using the equality operator)

Also Note that all MVs that I've tested are REFRESH FAST capable. The DBMS_MVIEW.EXPLAIN_MVIEW shows that they are REFRESH FAST capable. I'm using the COMPLETE REFRESH just for testing.

Upvotes: 1

Views: 883

Answers (1)

wolφi
wolφi

Reputation: 8361

Please check if it helps to run the refresh parallel:

ALTER SESSION ENABLE PARALLEL DML;

Furthermore, switch the refresh to non-atomic:

EXEC dbms_mview.refresh(list=>'MY_MV_TEST', method=>'C', atomic_refresh=>false);

Then Oracle will disable the indexes, refresh the data and rebuild the indexes automatically, which is in most cases faster.

Upvotes: 1

Related Questions