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?