ON COMMIT MVIEW Refresh using HASH_SJ in the UPDATE recursive SQL
Last updated on FEBRUARY 02, 2017
Applies to:Oracle Server - Enterprise Edition - Version 10.1.0.2 to 18.104.22.168.0 [Release 10.1 to 11.2]
Information in this document applies to any platform.
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.
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.
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.
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