ON COMMIT MVIEW Refresh using HASH_SJ in the UPDATE recursive SQL (Doc ID 1297651.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2.0 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

When performing an ON COMMIT ( ON-COMMIT ) fast refresh for a read-only materialized view, we see a poor explain plan for the UPDATE part of the recursive SQL.

For example:

Having installed patch 10320619 (setting _first_spare_parameter =0)  to solve MV performance issues, and analyzing the mlog$ tables and
locking these statistics when there are 0 rows present.

The execution plan for the refresh contains sql  like this:
/* MV_REFRESH (UPD) */ UPDATE "PAS"."MV_WERKNEMERS" SNA$ SET "SECTOR"=NULL,"BSN_SECTOR_RID"=NULL WHERE "BSN_SECTOR_RID" IN
(SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$")
RID$ FROM "PAS"."MLOG$_PAS_BASISGEGEVENS" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )MAS$)

So, as can be seen, we have NO_MERGE HASH_SJ hints here which are hindering this statement.

NOTE:  The actual INSERT statement looks good and is very fast.

Changes

This looks like <Bug:6456841>.

For many mview fast refresh issues we have set the parameter _mv_refresh_use_stats = false and this was seen to enhance performance.


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