MView: Slow fast refresh on commit if column group on master table

Hi there
Here is a customer case again on Documentum database.
Oracle 11.2.0.4  on AIX power 8.
[code language=”sql”]
create materialized view log on dmi_queue_item_s   WITH sequence, ROWID (item_id,name,task_name,task_subject,date_sent,router_id,delete_flag,dependency_type),PRIMARY KEY including new values ;   
create materialized view log on dmi_package_r   WITH sequence, ROWID (r_component_id),PRIMARY KEY  including new values ;   
create materialized view log on dmi_0301d65580000206_s   WITH sequence, ROWID (c_status,c_wf_recipient,c_wf_subject,r_object_id),PRIMARY KEY  including new values ;   
create materialized view log on dmi_package_s   WITH sequence, ROWID (r_workflow_id),PRIMARY KEY  including new values ;  
[/code]
 
And the materialized view.
[code language=”sql”]
create materialized view dmi_0301d65580000206_MV
refresh fast on commit
enable query rewrite
as
select
  dmi_0301d65580000206_s.c_status
, dmi_0301d65580000206_s.c_wf_recipient
, dmi_0301d65580000206_s.c_wf_subject
, dmi_0301d65580000206_s.r_object_id,
  doc.rowid rowid1,
  dmi_0301d65580000206_s.rowid rowid2,
  pk.rowid rowid3,
  dmi_queue_item.rowid rowid4
From dmi_package_r doc, dmi_0301d65580000206_s, dmi_package_s pk, dmi_queue_item_s dmi_queue_item
Where dmi_0301d65580000206_s.r_object_id = doc.r_component_id
  AND pk.r_workflow_id = dmi_queue_item.router_id
  AND pk.r_object_id = doc.r_object_id
  AND 0 = dmi_queue_item.delete_flag
  AND dmi_queue_item.dependency_type IN (‘0′,’1′,’2′,’3′,’4′,’5′,’6′,’and’,’or’,’none’)
  AND dmi_queue_item.task_subject <> ‘finish’
  AND dmi_queue_item.task_name NOT IN (‘Promotion’,’START NOOP’,’END NOOP’,’Promotion du document’);
[/code]
During refresh on commit, I have a bunch of enq:JI – contention
This querry is causing the problem behind:
 
[code language=”sql”]
/* MV_REFRESH (INS) */
INSERT
  /*+ NOAPPEND */
INTO "VFK_MIG"."DMI_0301D65580000206_MV"
SELECT
  /*+ NO_MERGE("JV$") */
  "JV$"."C_STATUS",
  "JV$"."C_WF_RECIPIENT",
  "JV$"."C_WF_SUBJECT",
  "JV$"."R_OBJECT_ID",
  "MAS$3".ROWID,
  "JV$"."RID$",
  "MAS$1".ROWID,
  "MAS$0".ROWID
FROM
  (SELECT "MAS$"."ROWID" "RID$" ,
    "MAS$".*
  FROM "VFK_MIG"."DMI_0301D65580000206_S" "MAS$"
  WHERE ROWID IN
    (SELECT
      /*+ HASH_SJ */
      CHARTOROWID("MAS$"."M_ROW$$") RID$
    FROM "VFK_MIG"."MLOG$_DMI_0301D65580000206" "MAS$"
    WHERE "MAS$".XID$$ = :1
    )
  ) "JV$",
  "DMI_QUEUE_ITEM_S" AS OF SNAPSHOT(:B_SCN) "MAS$0",
  "DMI_PACKAGE_S"    AS OF SNAPSHOT(:B_SCN) "MAS$1",
  "DMI_PACKAGE_R"    AS OF SNAPSHOT(:B_SCN) "MAS$3"
WHERE "JV$"."R_OBJECT_ID"     ="MAS$3"."R_COMPONENT_ID"
AND "MAS$1"."R_WORKFLOW_ID"   ="MAS$0"."ROUTER_ID"
AND "MAS$1"."R_OBJECT_ID"     ="MAS$3"."R_OBJECT_ID"
AND 0                         ="MAS$0"."DELETE_FLAG"
AND ("MAS$0"."DEPENDENCY_TYPE"=’0′
OR "MAS$0"."DEPENDENCY_TYPE"  =’1′
OR "MAS$0"."DEPENDENCY_TYPE"  =’2′
OR "MAS$0"."DEPENDENCY_TYPE"  =’3′
OR "MAS$0"."DEPENDENCY_TY
[/code]
And the execution plan…
[code language=”sql”]
——————————————————————————————————————————————————–
| Id  | Operation                         | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |——————————————————————————————————————————————————-
|   0 | INSERT STATEMENT                  |                               |      1 |        |      0 |00:01:11.34 |     326K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |                               |      1 |        |      0 |00:01:11.34 |     326K|       |       |          |
|   2 |   NESTED LOOPS                    |                               |      1 |      7 |      2 |00:01:11.34 |     326K|       |       |          |
|   3 |    NESTED LOOPS                   |                               |      1 |      2 |      2 |00:01:11.34 |     326K|       |       |          |
|   4 |     NESTED LOOPS                  |                               |      1 |      2 |      2 |00:01:11.34 |     326K|       |       |          |
|   5 |      VIEW                         |                               |      1 |      1 |      1 |00:01:11.34 |     326K|       |       |          |
|*  6 |       HASH JOIN RIGHT SEMI        |                               |      1 |      1 |      1 |00:01:11.34 |     326K|   982K|   982K|  433K (0)|
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_DMI_0301D65580000206    |      1 |      1 |      2 |00:00:00.01 |       3 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_DMI_0301D6558000022   |      1 |      1 |      2 |00:00:00.01 |       2 |       |       |          |
|   9 |        INDEX FULL SCAN            | <strong>DMI_0301D65580000206_S_INDX10</strong> |      1 |     56M|     56M|00:00:12.93 |     326K|       |       |          |
|* 10 |      INDEX RANGE SCAN             | DMI_PACKAGE_R_INDX03          |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
|* 11 |     INDEX RANGE SCAN              | DMI_PACKAGE_S_INDX01          |      2 |      1 |      2 |00:00:00.01 |       4 |       |       |          |
|* 12 |    INDEX RANGE SCAN               | DMI_QUEUE_ITEM_S_INDX04       |      2 |      3 |      2 |00:00:00.01 |       6 |       |       |          |
——————————————————————————————————————————————————–
[/code]
The index DMI_0301D65580000206_S_INDX10 is used because all columns are in there: “C_WF_RECIPIENT”, “C_STATUS”, “R_OBJECT_ID”, “C_WF_SUBJECT”
But, why this index is used??
There is no valid reason as all columns are in the mview log!
I have few more mviews on some other tables and everything is ok exception for this one.
After some research, the behaviour as due to column group (extended stats) on the DMI_0301D65580000206_S table.
[code language=”sql”]</pre>
<span style="font-family: courier new,courier,monospace;">– drop extended stats on DMI_0301D65580000206_S</span>
<span style="font-family: courier new,courier,monospace;">exec  dbms_stats.drop_extended_stats(null,’DMI_0301D65580000206_S’,'(C_STATUS,C_WF_ISRUNNING,C_WF_TASKHOLDER,C_RENDITION_STATUS,C_PAGENUM)’);</span>
<pre>[/code]
Recreate the mview and the problem is gone!
I will have to test this in 12c to see if the problem is also there.
Now let’s consider 12c behaviour with automatic column group creation… If the problem is still in 12c then some materialized views refresh on commit might have some problems 🙂
Cheers
jko
 
 
 
 
 
 


     

Leave a Reply

Your email address will not be published. Required fields are marked *