ANBOB™

物化视图是一种高级优化技术当用于数据分析报表类数据库, 把多表join的视图数据物化为真实的表数据存储. 我个人认为如果在OLTP中如果非用上了物化视图一定是应用的设计架构存在缺陷. 但是物化视图快速刷新可有时会出现性能问题, 在9I的版本中形成了一种共识如果在物化视图刷新里遇到问题可以配置 _mv_refresh_use_stats参数或收集物化视图日志表的统计信息并锁定就可以解决, 但是这个问题一直到后来的10G, 11G都有可能还会遇到. 根本原因是ORACLE的刷新引擎自作聪明的隐式加入的Hint.从10046 trace可以发现如下:

• CARDINALITY
• NO_SEMI_JOIN
• HASH_SJ

#Search IN MOS Know issue

In 9.2:

a) The following event needs to be set in the refreshing session:
alter session set events ‘10995 trace name context forever, level 8’;
b) Statistics need to be generated on the mview log when it is empty.
c) The hidden parameter ‘_mv_refresh_use_stats’ needs to be set to FALSE (default TRUE).

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).
In 10.2

   There is a default setting change in 10.2 as the cardinality hint used internally was thought to be a bad thing in the refresh statement, causing excessive rowid searches. So, the default setting (_mv_refresh_use_stats = FALSE) was to change the refresh statement to apply HASH_SJ hint. However, using HASH_SJ hint appears to cause a problem for this type of SQL statement. Therefore, it was suggested the parameter setting change which produced the original  refresh statement the customer has been using in the past  releases.

alter session set _mv_refresh_use_stats = true;

In 11.2

Added new hidden parameters  _mv_refresh_use_hash_sj  in version 11.2,

Please set below command to resolve the issue.
SQL> alter session set “_mv_refresh_use_hash_sj”=FALSE;

After applying this patch Patch 8787325  to enable fix we need to use the following statements.
For reference:
* To remove the hint HASH_SJ from delete statements:
alter system set “_third_spare_parameter” = 0;
* To add the hint HASH_SJ from delete statements:
alter system set “_third_spare_parameter” = 1;

IN 12.2
增加了更多的新参数

_mv_complete_refresh_conventional        FALSE   >>>>>>>>>>
_mv_refresh_use_stats                    FALSE
_mv_refresh_insert_no_append             TRUE  >>>>>>>>>>>>>>
_mv_refresh_pipe_timeout                 600     >>>>>>>>>>>>>>>>>
_mv_refresh_truncate_log                 FALSE   >>>>>>>>>>>>
_mv_refresh_shrink_log                   TRUE      >>>>>>>>>>>>>>

前几天有个朋友遇到了这个问题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

References:
MOS notes 1093616.1     1297651.1        420040.1

打赏

微信扫一扫，打赏作者吧～