mview refresh always get stuck

Jack_ma

有个视图就可以很快创建成功， 但是刷新总是卡住，没有返回：
SQL> exec dbms_mview.refresh('MV_018_A1_LAPSED_CLEA_6W', 'C', ATOMIC_REFRESH=>false) ;

尝试将远程表OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT 拷贝到本地（ctas），修改MView远程表为本地表， 刷新可以很快完成。

以下是视图定义：
CREATE MATERIALIZED VIEW "WOW_DATA"."MV_018_A1_LAPSED_CLEA_6W" ("LYLTY_CARD_NBR", "TIMESTAMP", "AVG_BASKET_SPEND_CAT", "AVG_WEEKLY_SPEND_CAT", "NO_BASKETS_CAT")
  ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  COMPRESS BASIC NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"
  BUILD DEFERRED
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select a.lylty_card_nbr, b."TIMESTAMP", round(sum(tot_amt_incld_gst)/count(distinct  txn_id), 2) as AVG_BASKET_SPEND_CAT,
  round(sum(tot_amt_incld_gst)/count(distinct trunc(a.start_txn_date, 'day')), 2) as AVG_WEEKLY_SPEND_CAT,
count (distinct  txn_id) as NO_BASKETS_CAT
from wow_data.super_item_sales_summary a, OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT b
where
a.lylty_card_nbr = b.entityid
and a.start_txn_date >=  trunc(b."TIMESTAMP" - 42, 'day') -35
and to_timestamp(a.start_txn_date || ' ' || a.start_txn_time, 'dd-mon-rr hh24:mi:ss') <= b."TIMESTAMP" - 42
and a.division_nbr in ('03', '07')
and (a.trading_dept_nbr not in ('027', '020') or a.trading_dept_nbr is null)
and (a.fine_dept_nbr != '746' or a.fine_dept_nbr is null)
and a.online_flag != 'Y'
and a.FINE_DEPT_NBR in ('194','195','197','199','202','204','206','208','209')
and start_txn_date >= trunc(sysdate) - (2*6+1)*7
and start_txn_date <= trunc(sysdate) -6*7
group by a.lylty_card_nbr, b."TIMESTAMP";

视图刷新时的trace在附件中
alter session SET tracefile_identifier=mview_trace1;
alter session set events '10046 trace name context forever, level 12';
set autotrace on
exec dbms_mview.refresh('MV_018_A1_LAPSED_CLEA_6W', 'C', ATOMIC_REFRESH=>false) ;

请帮忙分析  ， 谢谢

Jack_ma

一下是相关的执行计划：
#############  by remote table
Execution Plan
----------------------------------------------------------
Plan hash value: 1676992553

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                           |    34 |  4046 |   344K  (1)| 01:08:55 |        |      |
|   1 |  SORT GROUP BY       |                           |    34 |  4046 |   344K  (1)| 01:08:55 |        |      |
|*  2 |   FILTER             |                           |       |       |            |          |        |      |
|*  3 |    HASH JOIN         |                           |    34 |  4046 |   344K  (1)| 01:08:55 |        |      |
|   4 |     REMOTE           | OMT_018_A1_LAPSED_CLEA_6W |  2804 |   117K|    72   (9)| 00:00:01 | WOW_U~ | R->S |
|*  5 |     TABLE ACCESS FULL| SUPER_ITEM_SALES_SUMMARY  |   269K|    19M|   344K  (1)| 01:08:54 |        |      |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(SYSDATE@!)-91<=TRUNC(SYSDATE@!)-42)
   3 - access("B"."ENTITYID"=SYS_OP_C2C("A"."LYLTY_CARD_NBR"))
       filter("A"."START_TXN_DATE">=TRUNC(INTERNAL_FUNCTION("B"."TIMESTAMP")-42,'fmday')-35 AND
              TO_TIMESTAMP(INTERNAL_FUNCTION("A"."START_TXN_DATE")||' '||"A"."START_TXN_TIME",'dd-mon-rr
              hh24:mi:ss')<=INTERNAL_FUNCTION("B"."TIMESTAMP")-42)
   5 - filter(("A"."FINE_DEPT_NBR"='194' OR "A"."FINE_DEPT_NBR"='195' OR "A"."FINE_DEPT_NBR"='197' OR
              "A"."FINE_DEPT_NBR"='199' OR "A"."FINE_DEPT_NBR"='202' OR "A"."FINE_DEPT_NBR"='204' OR
              "A"."FINE_DEPT_NBR"='206' OR "A"."FINE_DEPT_NBR"='208' OR "A"."FINE_DEPT_NBR"='209') AND
              "START_TXN_DATE">=TRUNC(SYSDATE@!)-91 AND "A"."ONLINE_FLAG"<>'Y' AND ("A"."FINE_DEPT_NBR"<>'746' OR
              "A"."FINE_DEPT_NBR" IS NULL) AND ("A"."TRADING_DEPT_NBR"<>'027' AND "A"."TRADING_DEPT_NBR"<>'020' OR
              "A"."TRADING_DEPT_NBR" IS NULL) AND ("A"."DIVISION_NBR"='03' OR "A"."DIVISION_NBR"='07') AND
              "START_TXN_DATE"<=TRUNC(SYSDATE@!)-42)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "ENTITYID","TIMESTAMP" FROM "OMT_018_A1_LAPSED_CLEA_6W" "B" WHERE
       TRUNC("TIMESTAMP"-42,'fmday')-35<=TRUNC(:1)-42 (accessing 'WOW_UAT2DETECT_UAT' )
          
          
####  by local table without statistics

Execution Plan
----------------------------------------------------------
Plan hash value: 2179478887

----------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                           |     3 |   357 |       |   345K  (1)| 01:09:11 |
|   1 |  SORT GROUP BY       |                           |     3 |   357 |       |   345K  (1)| 01:09:11 |
|*  2 |   FILTER             |                           |       |       |       |            |          |
|*  3 |    HASH JOIN         |                           |   673 | 80087 |  3744K|   345K  (1)| 01:09:11 |
|*  4 |     TABLE ACCESS FULL| OMT_018_A1_LAPSED_CLEA_6W | 69578 |  2921K|       |   112   (7)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| SUPER_ITEM_SALES_SUMMARY  |   269K|    19M|       |   344K  (1)| 01:08:54 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(SYSDATE@!)-91<=TRUNC(SYSDATE@!)-42)
   3 - access("B"."ENTITYID"=SYS_OP_C2C("A"."LYLTY_CARD_NBR"))
       filter("A"."START_TXN_DATE">=TRUNC(INTERNAL_FUNCTION("B"."TIMESTAMP")-42,'fmday')-35 AND
              TO_TIMESTAMP(INTERNAL_FUNCTION("A"."START_TXN_DATE")||' '||"A"."START_TXN_TIME",'dd-mon-rr
              hh24:mi:ss')<=INTERNAL_FUNCTION("B"."TIMESTAMP")-42)
   4 - filter(TRUNC(INTERNAL_FUNCTION("B"."TIMESTAMP")-42,'fmday')-35<=TRUNC(SYSDATE@!)-42)
   5 - filter(("A"."FINE_DEPT_NBR"='194' OR "A"."FINE_DEPT_NBR"='195' OR
              "A"."FINE_DEPT_NBR"='197' OR "A"."FINE_DEPT_NBR"='199' OR "A"."FINE_DEPT_NBR"='202' OR
              "A"."FINE_DEPT_NBR"='204' OR "A"."FINE_DEPT_NBR"='206' OR "A"."FINE_DEPT_NBR"='208' OR
              "A"."FINE_DEPT_NBR"='209') AND "START_TXN_DATE">=TRUNC(SYSDATE@!)-91 AND "A"."ONLINE_FLAG"<>'Y'
              AND ("A"."FINE_DEPT_NBR"<>'746' OR "A"."FINE_DEPT_NBR" IS NULL) AND
              ("A"."TRADING_DEPT_NBR"<>'027' AND "A"."TRADING_DEPT_NBR"<>'020' OR "A"."TRADING_DEPT_NBR" IS
              NULL) AND ("A"."DIVISION_NBR"='03' OR "A"."DIVISION_NBR"='07') AND
              "START_TXN_DATE"<=TRUNC(SYSDATE@!)-42)

Note
-----
   - dynamic sampling used for this statement (level=2)
   
  
####  by local table with statistics  
Execution Plan
----------------------------------------------------------
Plan hash value: 2179478887

--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                           |    34 |  3706 |   344K  (1)| 01:08:56 |
|   1 |  SORT GROUP BY       |                           |    34 |  3706 |   344K  (1)| 01:08:56 |
|*  2 |   FILTER             |                           |       |       |            |          |
|*  3 |    HASH JOIN         |                           |    34 |  3706 |   344K  (1)| 01:08:56 |
|*  4 |     TABLE ACCESS FULL| OMT_018_A1_LAPSED_CLEA_6W |  2804 | 92532 |   111   (6)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| SUPER_ITEM_SALES_SUMMARY  |   269K|    19M|   344K  (1)| 01:08:54 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(SYSDATE@!)-91<=TRUNC(SYSDATE@!)-42)
   3 - access("B"."ENTITYID"=SYS_OP_C2C("A"."LYLTY_CARD_NBR"))
       filter("A"."START_TXN_DATE">=TRUNC(INTERNAL_FUNCTION("B"."TIMESTAMP")-42,'fmday')-3
              5 AND TO_TIMESTAMP(INTERNAL_FUNCTION("A"."START_TXN_DATE")||'
              '||"A"."START_TXN_TIME",'dd-mon-rr hh24:mi:ss')<=INTERNAL_FUNCTION("B"."TIMESTAMP")-42)
   4 - filter(TRUNC(INTERNAL_FUNCTION("B"."TIMESTAMP")-42,'fmday')-35<=TRUNC(SYSDATE@!)-42
              )
   5 - filter(("A"."FINE_DEPT_NBR"='194' OR "A"."FINE_DEPT_NBR"='195' OR
              "A"."FINE_DEPT_NBR"='197' OR "A"."FINE_DEPT_NBR"='199' OR "A"."FINE_DEPT_NBR"='202' OR
              "A"."FINE_DEPT_NBR"='204' OR "A"."FINE_DEPT_NBR"='206' OR "A"."FINE_DEPT_NBR"='208' OR
              "A"."FINE_DEPT_NBR"='209') AND "START_TXN_DATE">=TRUNC(SYSDATE@!)-91 AND
              "A"."ONLINE_FLAG"<>'Y' AND ("A"."FINE_DEPT_NBR"<>'746' OR "A"."FINE_DEPT_NBR" IS NULL)
              AND ("A"."TRADING_DEPT_NBR"<>'027' AND "A"."TRADING_DEPT_NBR"<>'020' OR
              "A"."TRADING_DEPT_NBR" IS NULL) AND ("A"."DIVISION_NBR"='03' OR "A"."DIVISION_NBR"='07')
              AND "START_TXN_DATE"<=TRUNC(SYSDATE@!)-42)

kevinlin.ora

1.修改MView远程表为本地表， 刷新可以很快完成。
2.trace中最后部分处于insert into xxx select ...状态，等待事件是'SQL*Net more data from dblink'，且每次等待时间都非常短。不过trace中只显示了13次。
3.表在本地或remote db的情形下，select ...部分的执行计划在逻辑上相同。不同的是通过dblink时需要将表B经过过滤后的数据取到本地后与表A进行hash join，这需要消耗额外的时间。

syhnd

1. 为什么创建mv很快，但刷新就卡住。 因为你创建MV时使用的是"BUILD DEFERRED"方式。 其实如果你用"BUILD IMMEDIATE"方式，则创建MV一样很慢。

2. 从你tkprof格式化后的信息来看：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.03          0          0          0           0
怎么会是这样呢？是不是你执行过程中cancel掉了。

从你的10046 raw trace来看，都是在more data from dblink.. 建议设置下DEFAULT_SDU_SIZE增加网络吞吐量。

说白了，还是要优化下MV中的select语句。

Jack_ma

syhnd 发表于 2013-7-16 15:20
1. 为什么创建mv很快，但刷新就卡住。 因为你创建MV时使用的是"BUILD DEFERRED"方式。 其实如果你用"BUILD  ...

SQL> select a.lylty_card_nbr, b."TIMESTAMP", round(sum(tot_amt_incld_gst)/count(distinct  txn_id), 2) as AVG_BASKET_SPEND_CAT,
  2    round(sum(tot_amt_incld_gst)/count(distinct trunc(a.start_txn_date, 'day')), 2) as AVG_WEEKLY_SPEND_CAT,
  3  count (distinct  txn_id) as NO_BASKETS_CAT
  4  from wow_data.super_item_sales_summary a, wow_data.OMT_018_A1_LAPSED_CLEA_6W b
  5  where
  6  a.lylty_card_nbr = b.entityid
  7  and a.start_txn_date >=  trunc(b."TIMESTAMP" - 42, 'day') -35
  8  and to_timestamp(a.start_txn_date || ' ' || a.start_txn_time, 'dd-mon-rr hh24:mi:ss') <= b."TIMESTAMP" - 42
  9  and a.division_nbr in ('03', '07')
10  and (a.trading_dept_nbr not in ('027', '020') or a.trading_dept_nbr is null)
11  and (a.fine_dept_nbr != '746' or a.fine_dept_nbr is null)
12  and a.online_flag != 'Y'
13  and a.FINE_DEPT_NBR in ('194','195','197','199','202','204','206','208','209')
and start_txn_date >= trunc(sysdate) - (2*6+1)*7
and start_txn_date <= trunc(sysdate) -6*7
group by a.lylty_card_nbr, b."TIMESTAMP"; 14   15   16  

no rows selected

Elapsed: 00:02:14.37

这是利用本地表的执行时间， 感觉这还是配置问题

Jack_ma

Jack_ma 发表于 2013-7-16 15:35
SQL> select a.lylty_card_nbr, b."TIMESTAMP", round(sum(tot_amt_incld_gst)/count(distinct  txn_id), ...

SQL> set timing on
SQL> select a.lylty_card_nbr, b."TIMESTAMP", round(sum(tot_amt_incld_gst)/count(distinct  txn_id), 2) as AVG_BASKET_SPEND_CAT,
  2    round(sum(tot_amt_incld_gst)/count(distinct trunc(a.start_txn_date, 'day')), 2) as AVG_WEEKLY_SPEND_CAT,
  3  count (distinct  txn_id) as NO_BASKETS_CAT
from wow_data.super_item_sales_summary a, OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT b
where
  4    5    6  a.lylty_card_nbr = b.entityid
  7  and a.start_txn_date >=  trunc(b."TIMESTAMP" - 42, 'day') -35
  8  and to_timestamp(a.start_txn_date || ' ' || a.start_txn_time, 'dd-mon-rr hh24:mi:ss') <= b."TIMESTAMP" - 42
  9  and a.division_nbr in ('03', '07')
10  and (a.trading_dept_nbr not in ('027', '020') or a.trading_dept_nbr is null)
and (a.fine_dept_nbr != '746' or a.fine_dept_nbr is null)
11   12  and a.online_flag != 'Y'
13  and a.FINE_DEPT_NBR in ('194','195','197','199','202','204','206','208','209')
14  and start_txn_date >= trunc(sysdate) - (2*6+1)*7
and start_txn_date <= trunc(sysdate) -6*7
15   16  group by a.lylty_card_nbr, b."TIMESTAMP";
from wow_data.super_item_sales_summary a, OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT b
                                                                    *
ERROR at line 4:
ORA-01013: user requested cancel of current operation


Elapsed: 00:12:46.89

SQL> select count(*) from OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT;

  COUNT(*)
----------
     56075

Elapsed: 00:00:00.01
SQL> set autotrace traceonly
SQL> select * from OMT_018_A1_LAPSED_CLEA_6W@WOW_UAT2DETECT_UAT;

56075 rows selected.

Elapsed: 00:00:01.60

获取remote的数据是很快的， 修改SDU_SIZE并没有起作用。。。