Hi Tom,
I have a materialized view that joins two tables. Both tables have materialized view logs and the view meets the criteria for a fast refresh. However, simply adding one new record to the ATTRIBUTE base table takes several minutes to commit. Yet, once the MV is refreshed, it shows as a fast refresh. I am new to MV’s so would appreciate your help on this.
The base tables:
CREATE TABLE ATTRIBUTE –- Approx 80 Million rows
( "ATT_CONTACT_ID" NUMBER(9,0) NOT NULL,
"ATT_LDET_NAME" VARCHAR2(20 BYTE) NOT NULL,
"ATT_LDET_ID" VARCHAR2(20 BYTE) NOT NULL
CONSTRAINT "PK_ATTRIBUTES" PRIMARY KEY ("ATT_CONTACT_ID", "ATT_LDET_NAME", "ATT_LDET_ID")USING INDEX
);
CREATE TABLE EDTY_PERSONALITY_CONTACT –- Approx 14 million rows
( "PC_PERSONALITY_ID" NUMBER NOT NULL,
"PC_CONTACT_ID" NUMBER
CONSTRAINT "UQ_EDTY_PC_CONTACT_ID" UNIQUE ("PC_CONTACT_ID")USING INDEX
);
Tables above are fully populated with millions of rows as shown before the MV is built. Irrelevant columns have been omitted from the tables and views.
The MV Logs are created as follows:
CREATE materialized view log on attribute with primary key, rowid, sequence including new values;
CREATE materialized view log on edty_personality_contact with primary key, rowid, sequence including new values
The MV joins the two tables and filters only items where ATT_LDET_NAME = ‘COTYPE’ :
CREATE MATERIALIZED VIEW MV_DEMO
build immediate
refresh fast on commit
AS
SELECT
pc_contact_id, att_contact_id, att_ldet_name, att_ldet_id, att_updated, attribute.rowid att_rid, edty_personality_contact.rowid pc_rid
FROM attribute , edty_personality_contact
where
att_contact_id = pc_contact_id and
att_ldet_name in ( 'COTYPE' )
The MV takes about 5 minutes to build and contains around 3.5 million rows.
The issue I am having is that after the MV is built, inserting a single new row in ATTRIBUTE takes around 3 minutes to commit. Without the MV the inserts work as fast as expected.
INSERT INTO ATTRIBUTE (ATT_CONTACT_ID, ATT_LDET_NAME, ATT_LDET_ID,) VALUES ('17891166', 'COTYPE', '0014')
The query plan for the MV refresh shows a “TABLE ACCESS FULL” on ATTRIBUTE with the filter “"MAS$"."ATT_LDET_NAME"='COTYPE... “ for the following SQL taken from Enterprise Manager top activity monitor:
/* MV_REFRESH (INS) */ INSERT /*+ NOAPPEND */ INTO "EDEN_OWNER"."MV_DEMOGRAPHICS" SELECT /*+ NO_MERGE("JV$") */ "MAS$0"."PC_CONTACT_ID","JV$"."ATT_CONTACT_ID","JV$"."ATT_LDET_NAME","JV$"."ATT_LDET_ID","JV$"."ATT_UPDATED","JV$"."RID$","MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM "EDEN_OWNER"."ATTRIBUTE" "MAS$" WHERE ROWID IN (SELECT /*+ HASH_SJ */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "EDEN_OWNER"."MLOG$_ATTRIBUTE" "MAS$" WHERE "MAS$".XID$$ = :1 )) "JV$", "EDENTITY"."EDTY_PERSONALITY_CONTACT" AS OF SNAPSHOT(:B_SCN) "MAS$0" WHERE "JV$"."ATT_CONTACT_ID"="MAS$0"."PC_CONTACT_ID" AND "JV$"."ATT_LDET_NAME"='COTYPE'
What I don’t understand is why it is doing a TABLE ACCESS FULL on ATTRIBUTE when it is simply joining on the ROWID recorded in MLOG$_ATTRIBUTE resulting from the insert.
Here is the execution plan as requested:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f22atk24n3z18, child number 0
/* MV_REFRESH (INS) */ INSERT /*+ NOAPPEND */ INTO
"EDEN_OWNER"."MV_DEMOGRAPHICS" SELECT /*+ NO_MERGE("JV$") */
"MAS$0"."PTY_ORGANISATION_ID","MAS$0"."PTY_SITE_ID","MAS$0"."PTY_PARTY_I
D","MAS$0"."PTY_PERSONALITY_ID","MAS$1"."PC_CONTACT_ID","JV$"."ATT_CONTA
CT_ID","JV$"."ATT_LDET_NAME","JV$"."ATT_LDET_ID","JV$"."ATT_UPDATED","JV
$"."RID$","MAS$1".ROWID,"MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID"
"RID$" , "MAS$".* FROM "EDEN_OWNER"."ATTRIBUTE" "MAS$" WHERE ROWID
IN (SELECT /*+ HASH_SJ */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"EDEN_OWNER"."MLOG$_ATTRIBUTE" "MAS$" WHERE "MAS$".XID$$ = :1 ))
"JV$", "EDENTITY"."EDTY_PERSONALITY" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"EDENTITY"."EDTY_PERSONALITY_CONTACT" AS OF SNAPSHOT(:B_SCN) "MAS$1"
WHERE "JV$"."ATT_CONTACT_ID"="MAS$1"."PC_CONTACT_ID" AND
"MAS$1"."PC_PERSONALITY_ID"="MAS$0"."PTY_PERSONALITY_ID" AND
"JV$"."ATT_LDET_NAME"='COTYPE'
NOTE: cannot fetch plan for SQL_ID: f22atk24n3z18, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
EXPLAINED SQL STATEMENT:
------------------------
/* MV_REFRESH (INS) */ INSERT /*+ NOAPPEND */ INTO
"EDEN_OWNER"."MV_DEMOGRAPHICS" SELECT /*+ NO_MERGE("JV$") */
"MAS$0"."PTY_ORGANISATION_ID","MAS$0"."PTY_SITE_ID","MAS$0"."PTY_PARTY_I
D","MAS$0"."PTY_PERSONALITY_ID","MAS$1"."PC_CONTACT_ID","JV$"."ATT_CONTA
CT_ID","JV$"."ATT_LDET_NAME","JV$"."ATT_LDET_ID","JV$"."ATT_UPDATED","JV
$"."RID$","MAS$1".ROWID,"MAS$0".ROWID FROM ( SELECT "MAS$"."ROWID"
"RID$" , "MAS$".* FROM "EDEN_OWNER"."ATTRIBUTE" "MAS$" WHERE ROWID
IN (SELECT /*+ HASH_SJ */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"EDEN_OWNER"."MLOG$_ATTRIBUTE" "MAS$" WHERE "MAS$".XID$$ = :1 ))
"JV$", "EDENTITY"."EDTY_PERSONALITY" AS OF SNAPSHOT(:B_SCN) "MAS$0",
"EDENTITY"."EDTY_PERSONALITY_CONTACT" AS OF SNAPSHOT(:B_SCN) "MAS$1"
WHERE "JV$"."ATT_CONTACT_ID"="MAS$1"."PC_CONTACT_ID" AND
"MAS$1"."PC_PERSONALITY_ID"="MAS$0"."PTY_PERSONALITY_ID" AND
"JV$"."ATT_LDET_NAME"='COTYPE'
Plan hash value: 567991100
----------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | LOAD TABLE CONVENTIONAL | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | VIEW | |
| 6 | HASH JOIN RIGHT SEMI | |
| 7 | TABLE ACCESS BY INDEX ROWID| MLOG$_ATTRIBUTE |
| 8 | INDEX RANGE SCAN | I_MLOG$_ATTRIBUTE |
| 9 | TABLE ACCESS FULL | ATTRIBUTE |
| 10 | TABLE ACCESS BY INDEX ROWID | EDTY_PERSONALITY_CONTACT |
| 11 | INDEX UNIQUE SCAN | UQ_EDTY_PC_CONTACT_ID |
| 12 | INDEX UNIQUE SCAN | PK_EDTY_PER |
| 13 | TABLE ACCESS BY INDEX ROWID | EDTY_PERSONALITY |
----------------------------------------------------------------------
59 rows selected.
I have since discovered this article which talks about locking the stats for the MV logs :
https://orastory.wordpress.com/2014/11/27/the-mess-that-is-fast-refresh-join-only-materialized-views/ The performance issue resolves itself when I create and lock stats for the MV logs – so the question now is – is this an Oracle recommended solution and can I rely on this method working into the future?
"is this an Oracle recommended solution and can I rely on this method working into the future?"
Yes, and is documented as such in MOS note 420040.1
"SYMPTOMS
Fast refresh for a materialized view is very slow. Tracing the recursive SQL with SQL Trace or
the 10046 event shows that the insert phase of the refresh is using one of the following hints in a subquery:
CARDINALITY
NO_SEMI_JOIN
HASH_SJ
SOLUTION
In 10.1 and above
a) Statistics need to be generated on the materialized view log while it is empty, and then locked via
DBMS_STATS.LOCK_TABLE_STATS.
b) The hidden parameter '_mv_refresh_use_stats' needs to be set to FALSE (default TRUE).
If using an spfile:
alter system set "_mv_refresh_use_stats"=FALSE;
If using a pfile:
_mv_refresh_use_stats=FALSE"