物化视图刷新，请yangtingkun等大侠帮忙

yangtingkun · 发表于 2005-3-16 12:14

把tlw_sett_f3_200502和js_mv_tlw_sett_f3_200502贴出来看看

top100 · 发表于 2005-3-16 12:41

prompt
prompt Creating table TLW_SETT_F3_200502
prompt =================================
prompt
create table TLW_SETT_F3_200502
(
  SWITCH_ID          NUMBER(5),
  BATCH_CODE1       NUMBER(8),
  BATCH_CODE2       NUMBER(8),
  SETT_TYPE          NUMBER(8),
  INNET_REGION_CODE NUMBER(7) default -2,
  OUTNET_REGION_CODE  NUMBER(7) default -2,
  TRUNK_IN_CODE    VARCHAR2(8) default '-',
  TRUNK_OUT_CODE    VARCHAR2(8) default '-',
  YEAR_ID          NUMBER(4),
  MONTH_ID          NUMBER(2),
  DAY_ID             NUMBER(2),
  HOUR_ID          NUMBER(2),
  RANGE_ID          NUMBER(1),
  DIRECTION_ID       NUMBER(1),
  CALLING_TSP_CODE NUMBER(2),
  CALLED_TSP_CODE    NUMBER(2),
  TRANS_NETWORK    NUMBER(2),
  NETWORK_TYPE       NUMBER(2),
  CALLING_BRAND_CODE  NUMBER(2),
  CALLED_BRAND_CODE NUMBER(2),
  CALLING_AREA_ATTR NUMBER(2),
  CALLED_AREA_ATTR NUMBER(2),
  CALLED_TYPE       NUMBER(2),
  EXPIRE_FLAG       VARCHAR2(8),
  ACCESS_CODE       VARCHAR2(8) default '-',
  NUM_CDR          NUMBER(12),
  DURATION          NUMBER(12),
  CHARGING_DURATION NUMBER(12),
  SETTLEMENT_DURATION NUMBER(12),
  SETT_COUNT       NUMBER(14),
  LOCAL_DISCOUNT_FEE  NUMBER(14,3),
  TOLL_DISCOUNT_FEE NUMBER(14,3),
  SETT_FEE          NUMBER(14,3),
  CALLING_AREA_CODE NUMBER(4) default -2,
  CALLED_AREA_CODE NUMBER(4) default -2
)
tablespace ZHJS_LIST_WJJS_200502
  pctfree 20
  initrans 1
  maxtrans 255
  storage
  (
initial 8M
next 8M
minextents 1
maxextents unlimited
pctincrease 0
  );

top100 · 发表于 2005-3-16 12:42

create materialized view log on tlw_sett_f3_200502 with rowid,sequence(year_id,month_id,day_id,switch_id,innet_region_code,outnet_region_code,access_code,sett_type,direction_id,calling_tsp_code,called_tsp_code,num_cdr,duration,settlement_duration,sett_count,local_discount_fee,toll_discount_fee,sett_fee) including new values;

create materialized view js_mv_tlw_sett_f3_200502
tablespace zhjs_list_wjjs_200503
parallel 4
build immediate
REFRESH fast START WITH trunc(sysdate,'dd') NEXT trunc(sysdate,'dd')+1 + 4/24
enable query rewrite
as
select /*+parallel(tlw_sett_f3_200502,16)*/
   t.year_id,
   t.month_id,
   t.day_id,
   t.switch_id,
   t.innet_region_code,
   t.outnet_region_code,
   t.access_code,
   t.sett_type,
   t.direction_id,
   t.calling_tsp_code,
   t.called_tsp_code,
   count(t.num_cdr) count_cdrs,
   count(t.duration) count_durs,
   count(t.settlement_duration) count_sdurs,
   count(t.sett_count) count_scounts,
   count(t.local_discount_fee) count_ldisfees,
   count(t.toll_discount_fee) count_tdisfees,
   count(t.sett_fee) count_sfees,
   count(*) count_star,
   sum(t.num_cdr) cdrs,
   sum(t.duration) durs,
   sum(t.settlement_duration) sdurs,
   sum(t.sett_count) scounts,
   sum(t.local_discount_fee) ldisfees,
   sum(t.toll_discount_fee) tdisfees,
   sum(t.sett_fee) sfees
from tlw_sett_f3_200502 t
group by
   t.year_id,
   t.month_id,
   t.day_id,
   t.switch_id,
   t.innet_region_code,
   t.outnet_region_code,
   t.access_code,
   t.sett_type,
   t.direction_id,
   t.calling_tsp_code,
   t.called_tsp_code;

yangtingkun · 发表于 2005-3-16 13:26

最初由 top100 发布
[B]

果真不出所料，我通过plsql dev查看视图，发现变成refresh complete了，为什么呢？是什么操作导致这种结果的呢？

继续一步，我同步的查看了相同方式下的另一个视图、表，即前一月帐期的
select sum(...) from tlw_sett_f3_200502;
居然也没有查询重写？？？？？？？
再看视图定义，居然没有变，为refresh fast;

（注：——200502,200503所有的模式、定义均类似，唯一不同的只是由于当前是3月帐期，tlw_sett_f3_200503每天都有变化，而2月帐期的没有变化了而已） [/B]

执行计划？
加上rewrite的hint试试，如果加上hint没有问题的话，你试试收集一下tlw_sett_f3_200502的统计信息。

top100 · 发表于 2005-3-16 15:46

12:40:18 SQL> set autotrace traceonly explain;
12:40:34 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
Card=24283160 Bytes=72849480)

tlw_*_200502的表使用rewrite提示，同样，没有使用重写

yangtingkun · 发表于 2005-3-16 16:33

执行alter materialized view js_mv_tlw_sett_f3_200503 consider fresh;后三月的就应该利用物化视图了。

你把对二月的查询的执行计划贴出来。

top100 · 发表于 2005-3-16 17:23

我明天准备到现场去看看，我想这样子：
1.把js_mv_*_200503 drop掉，后重建，使用fresh fast选项，重试，确保；
2.如果不行
执行alter materialized view js_mv_tlw_sett_f3_200503 consider fresh;
（不过我上次好像是后来做了该步验证的，没有解决问题。）
我明天再试
3.监测几天，如果还不行，我再请教；
4.二月的执行计划，明天一起。
5.——谢谢大家不厌其烦的关注、帮忙。

top100 · 发表于 2005-3-17 22:05

这个是今天查看结果：
SQL> conn / as sysdba
已连接。
SQL> conn user/passwd@db
已连接。
SQL> set time on
11:14:14 SQL> set timing on
11:14:18 SQL> show parameter query

NAME                               TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
query_rewrite_enabled             string
TRUE
query_rewrite_integrity             string
TRUSTED
11:15:22 SQL> set autotrace traceonly explain
11:15:56 SQL> select sum(num_cdr) from tlw_sett_f3_200502;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200502'

12:46:13 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200502;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1    SORT* (AGGREGATE)                                     :Q914500
                                                                     0

3 2    TABLE ACCESS* (FULL) OF 'JS_MV_TLW_SETT_F3_200502' (Co :Q914500
      st=75 Card=631959 Bytes=8215467)                            0

2 PARALLEL_TO_SERIAL          SELECT /*+ PIV_SSF */ SYS_OP_MSR(SUM(A1.C0))
                                 FROM (SELECT /*+ NO_EXPAND ROWID(A2

3 PARALLEL_COMBINED_WITH_PARENT

12:46:39 SQL> select sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:47:27 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=18828 Card=1 Bytes=3
      )

1 0 SORT (AGGREGATE)
2 1    TABLE ACCESS (FULL) OF 'TLW_SETT_F3_200503' (Cost=18828
      Card=24283160 Bytes=72849480)

12:47:42 SQL> ALTER MATERIALIZED VIEW js_mv_tlw_sett_f3_200503 CONSIDER FRESH;

实体化视图已更改。

已用时间:  00: 09: 24.23
13:05:12 SQL> select /*+rewrite*/sum(num_cdr) from tlw_sett_f3_200503;
已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1    SORT* (AGGREGATE)                                     :Q914800
                                                                     0

3 2    TABLE ACCESS* (FULL) OF 'JS_MV_TLW_SETT_F3_200503' (Co :Q914800
      st=34 Card=169444 Bytes=508332)                            0

2 PARALLEL_TO_SERIAL          SELECT /*+ PIV_SSF */ SYS_OP_MSR(SUM(A1.C0))
                                 FROM (SELECT /*+ NO_EXPAND ROWID(A2

3 PARALLEL_COMBINED_WITH_PARENT

select t.mview_name,t.updatable,t.rewrite_enabled,t.refresh_mode,
t.fast_refreshable,t.last_refresh_type,t.last_refresh_date,t.staleness,
t.after_fast_refresh,t.compile_state
from user_mviews t

MVIEW_NAME JS_MV_TLW_SETT_F3_200502
UPDATABLE N
REWRITE_ENABLED Y
REFRESH_MODE DEMAND
FAST_REFRESHABLE DIRLOAD_DML
LAST_REFRESH_TYPE FAST
LAST_REFRESH_DATE 2005-3-17 4:00:01
STALENESS FRESH
AFTER_FAST_REFRESH FRESH
COMPILE_STATE VALID

MVIEW_NAME JS_MV_TLW_SETT_F3_200503
UPDATABLE N
REWRITE_ENABLED Y
REFRESH_MODE DEMAND
FAST_REFRESHABLE DIRLOAD_DML
LAST_REFRESH_TYPE COMPLETE
LAST_REFRESH_DATE 2005-3-17 13:05:12
STALENESS UNKNOWN
AFTER_FAST_REFRESH UNKNOWN
COMPILE_STATE VALID

安全起见，我最后将物化视图重建了，观察几天再说。