My Oracle Support Banner

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

Last updated on NOVEMBER 07, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2.0 [Release 10.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.