问题描述
前几天有个朋友遇到了这个问题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
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。