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 |