物化视图因为hash_sj hint刷新性能差

前几天有个朋友遇到了这个问题11204的环境  也是在做两个表只是简单的join快速刷新的MVIEW时发现很慢然后 做了10046 发现刷新引擎自动增加了HASH_SJ的hint.

INSERT  /*+ NOAPPEND */ INTO "CJ"."MV_SALES2" SELECT /*+ NO_MERGE("JV$") */ 
  "JV$"."PURCHASE_DETAIL_ID","JV$"."RID$","MAS$0".ROWID FROM ( SELECT 
  "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "CJ"."T_SALES_PURCHASED_0301" 
  "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$")
   RID$     FROM "CJ"."MLOG$_T_SALES_PURCHASED_03" "MAS$"   WHERE 
  "MAS$".XID$$ = :1  AND NOT        ("MAS$".DMLTYPE$$ = 'U' AND         
  (sys.dbms_snapshot_utl.vector_compare(:B_CV1,                               
                  "MAS$".change_vector$$) = 0)))) "JV$", 
  "T_SALES_PURCHASEM_0301" AS OF SNAPSHOT(:B_SCN)  "MAS$0" WHERE 
  "JV$"."PURCHASE_ID"="MAS$0"."PURCHASE_ID" AND "JV$"."RECORD_STATUS"='0' AND 
  NVL("MAS$0"."SALES_TYPE",0)='0' AND ("JV$"."PURCHASE_DETAIL_STATUS"='2' OR 
  "JV$"."PURCHASE_DETAIL_STATUS"='3' OR "JV$"."PURCHASE_DETAIL_STATUS"='4' OR 
  "JV$"."PURCHASE_DETAIL_STATUS"='5' OR "JV$"."PURCHASE_DETAIL_STATUS"='6' OR 
  "JV$"."PURCHASE_DETAIL_STATUS"='7' OR "JV$"."PURCHASE_DETAIL_STATUS"='8' OR 
  "JV$"."PURCHASE_DETAIL_STATUS"='26' OR "JV$"."PURCHASE_DETAIL_STATUS"='27') 
  AND "MAS$0"."PURCHASE_ORG_ID"<>1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      2.50       9.93      88267      88277          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.51       9.93      88267      88277          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  MV_SALES2 (cr=88275 pr=88267 pw=0 time=9925787 us)
         0          0          0   NESTED LOOPS  (cr=88275 pr=88267 pw=0 time=9925776 us cost=24258 size=77 card=1)
         0          0          0    NESTED LOOPS  (cr=88275 pr=88267 pw=0 time=9925772 us cost=24258 size=77 card=1)
         0          0          0     VIEW  (cr=88275 pr=88267 pw=0 time=9925771 us cost=24257 size=52 card=1)
         0          0          0      HASH JOIN RIGHT SEMI (cr=88275 pr=88267 pw=0 time=9925768 us cost=24257 size=302 card=1)
         1          1          1       TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_T_SALES_PURCHASED_03 (cr=2 pr=0 pw=0 time=106 us cost=1 size=273 card=1)
         1          1          1        INDEX RANGE SCAN I_MLOG$_T_SALES_PURCHASED_ (cr=1 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 92030)
   1276760    1276760    1276760       TABLE ACCESS FULL T_SALES_PURCHASED_0301 (cr=88273 pr=88267 pw=0 time=16503675 us cost=24254 size=19872105 card=685245)
         0          0          0     INDEX UNIQUE SCAN PK_SALES_PURCHASE_M (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 92021)
         0          0          0    TABLE ACCESS BY INDEX ROWID T_SALES_PURCHASEM_0301 (cr=0 pr=0 pw=0 time=0 us cost=1 size=25 card=1)

看到上面执行计划日志表小量数据和大表T_SALES_PURCHASED_0301使用了HASH JOIN RIGHT SEMI,如果手动去掉hint后执行实际使用NL JOIN 访问T_SALES_PURCHASED_0301直接使用了table access by user rowid的访问路径效率更佳 . 判断应该是11204同样存在物化视图刷新引擎的问题, 后来同样的数据在12.1中测试同样有该问题 。最后无奈改用了其它办法

这里简单的记录. 这类问题多数是bug, 现有的案例2种解决方法,
1:set “_mv_refresh_use_stats” to “true”
2:collect and lock statistics on the logs