How to refresh materialized views faster in 19c?
When I finished upgrading a database from Oracle 11.2 to Oracle 19.24, my customer noticed delays in the fast refresh processes of materialized views: what once took less than 30 minutes now takes hours and hours!
I verified that the statistics were up to date, that the materialized view logs were not too big, and that everything was indexed, but the times did not improve.
There was already talk about downgrading to Oracle 11.2, but happily, I was able to remedy the problem, and with just one command!
Wanna know what it is? Then continue reading—it could save your life if you ever find yourself in a situation like this.
The problem
While reviewing the code for the application that refreshes the approximately 400 materialized views, I discovered that a hidden parameter had been changed for some of them.
DECLARE
. . .
BEGIN
EXECUTE IMMEDIATE
'alter session set "_mv_refresh_use_stats"=FALSE';
. . .
DBMS_MVIEW.REFRESH ( 'INS00.MV_POLICY', 'F' );
. . .
END;
I addressed the programmers about the reasoning behind setting _mv_refresh_use_stats=false, but the roots have been lost.
I search My Oracle Support and see multiple notes advising that change, with the goal of boosting fast refresh performance:
| 420040.1 | Materialized View Fast Refresh Is Slow Due to a Poor Execution Plan During the Insert Phase With Hints Such As CARDINALITY, NO_SEMI_JOIN, and HASH_SJ |
| 981948.1 | MSRFWOR module: refresh collection snapshots runs longer than 3 hours |
| 145419.1 | How to Setup and Run Data Collections |
| 1067892.1 | Performance issue - refresh collections snapshot when run for all |
| 1063953.1 | Refresh Collection Snapshots Performance - Managing MLOG$ Tables and Snapshots for Data Collections |
The enigma has been solved: when they created the application for refreshing materialized views, they ran into performance issues, which they fixed by setting the hidden parameter.
However, this was true a decade ago, when they used Oracle 11.2. Will that still be true now that the Oracle 19.24 upgrade is complete?
The solution
While checking the SQL that Oracle creates automatically for fast refreshing materialized views, I found that the HASH_SJ hint is used frequently, as shown in line 11 below.
INSERT /*+ NOAPPEND */ INTO "INS00"."MV_POLICY"
SELECT /*+ NO_MERGE("JV$")*/
"MAS$4"."ID","MAS$4"."INITDATE","MAS$4"."ENDDATE",
"MAS$4"."POLICYNUM","MAS$4"."PREMIUM","MAS$4"."IDAGENT",
"MAS$2"."IDMOV","MAS$2"."AVISOCOBRANZA",
"MAS$0"."FIRSTNAME","MAS$0"."LASTNAME"
"MAS$4".ROWID,"MAS$1".ROWID,"MAS$2".ROWID, "JV$"."RID$","MAS$0".ROWID
FROM ( SELECT "MAS$"."ROWID" "RID$",
"MAS$".*
FROM "INS00"."MV_POLICY_LAST_MOV" "MAS$" W
HERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$
FROM "INS00"."MLOG$_VM_POLICY_LAST_MOV" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :B_ST3
)
) AS OF SNAPSHOT(:B_SCN) "JV$",
"INS00"."CUSTOMER" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"INS00"."AGENT" AS OF SNAPSHOT(: B_SCN) "MAS$1",
"INS00"."POLICY_MOV" AS OF SNAPSHOT(:B_SCN) "MAS$2",
"INS00"."POLICY" AS OF SNAPSHOT(:B_SCN) "MAS$4"
WHERE "MAS$4"."ID"="JV$"."ID"
AND "MAS$2"."IDMOV"="JV$"."MOV"
AND "MAS$2"."ID"="JV$"."ID"
AND "MAS$4"."IDAGENTE"="MAS$1" ."IDAGENT"(+)
AND "MAS$4"."IDCUSTOMER"="MAS$0"."IDCUSTOMER"(+)My Oracle Support has multiple notes showing that the use of the HASH_SJ hint has been connected with performance issues with fast refreshing materialized views.
| 1388637.1 | How To Troubleshoot Materialized View Fast Log Timestamp-based Refresh Performance Issues |
| 733054.1 | Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table |
| 2862145.1 | Refresh Collection Snapshot Is Taking Very Long To Complete - Performance Issue with Large Base Table Row Count |
Note 2862145.1 is the most explicit in highlighting the issue and its solution:
The RDBMS Optimizer chooses how the Snapshots are refreshed during Refresh Collection Snapshots.
In RDBMS 19c version - it was found that Optimizer was choosing to use the hint /*+ HASH_SJ */ - which is a known performance issue.
In the past, we have recommended setting DB parameter _mv_refresh_use_stats - false and this was correct setting for earlier DB versions.
On 19c, we need to set this parameter - true to avoid DB Optimizer choosing to use /*+ HASH_SJ */ to refresh the snapshot.
Bingo! What was advised in Oracle 11.2 is no longer recommended in 19.24, therefore we make the adjustment at the instance level and wait to see what happens with the queries.
SQL> ALTER SYSTEM SET "_mv_refresh_use_stats"=TRUE SCOPE=BOTH SID='*';INSERT /*+ NOAPPEND */ INTO "INS00"."MV_POLICY"
SELECT /*+ NO_MERGE("JV$") */
"MAS$4"."ID","MAS$4"."INITDATE","MAS$4"."ENDDATE",
"MAS$4"."POLICYNUM","MAS$4"."PREMIUM","MAS$4"."IDAGENT",
"MAS$2"."IDMOV","MAS$2"."AVISOCOBRANZA",
"MAS$0"."FIRSTNAME","MAS$0"."LASTNAME"
"MAS$4".ROWID,"MAS$1".ROWID,"MAS$2".ROWID, "JV$"."RID$","MAS$0".ROWID
FROM ( SELECT "MAS$"."ROWID" "RID$",
"MAS$".*
FROM "INS00"."MV_POLICY_LAST_MOV" "MAS$"
WHERE ROWID IN (SELECT /*+ CARDINALITY(MAS$ 294) NO_SEMIJOIN */
CHARTOROWID("M AS$"."M_ROW$$") RID$
FROM "INS00"." MLOG$_VM_POLICY_LAST_MOV" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :B_ST3
)
) AS OF SNAPSHOT(:B_SCN) "JV$",
"INS00"."CUSTOMER" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"INS00"."AGENT" AS OF SNAPSHOT(:B_SCN) "MAS$1",
"INS00"."POLICY_MOV" AS OF S NAPSHOT(:B_SCN) "MAS$2",
"INS00"."POLICY" AS OF SNAPSHOT(:B_SCN) "MAS$4"
WHERE "MAS$4"."ID"="JV$"."ID"
AND "MAS$2"."IDMOV"="JV$"."MOV"
AND "MAS$2"."ID"="JV$"."ID"
AND "MAS$4"."CODSISTEMA"="MAS$1"."CODSISORI"(+)
AND "MAS$4"."IDAGENTE"="MAS$1" ."IDAGENT"(+)
AND "MAS$4"."IDCUSTOMER"="MAS$0"."IDCUSTOMER"(+)Several days after applying the parameter change, execution times improved, and the application can now refresh all materialized views in less than 30 minutes, thus we can consider the problem solved.
Based on this experience, if you have Oracle 19c databases that rely on materialized views, don’t be afraid to set _mv_refresh_use_stats=true. You are welcome to thank me later …
Recent Posts
