sql 使CPU使用100%，棘手！

askgyliu

既然你那么肯定，那麻烦你再做一个测试。把下面的SQL的EXPLAIN OUTPUT放上来看看？

insert into session.V_HML_EAL_tem( group_id ,pre_m_arpu_d , EXIST_CUST_C,
        ADD_CUST_C,LOSS_CUST_C, EXIST_AVG_ARPU,exist_M,exist_CLV,
        ADD_AVG_ARPU,add_M,Add_CLV, LOSS_AVG_ARPU,loss_M,loss_CLV,
        EXIST_SUM_NOW_ARPU,ADD_SUM_NOW_ARPU,LOSS_SUM_NOW_ARPU) (
   select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id,
           coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d),
           EXIST_CUST_C,ADD_CUST_C,LOSS_CUST_C, a1.arpu,a1.m,a1.clv, a2.arpu,
           a2.m,a2.clv, a3.arpu,a3.m,a3.clv, a1.EXIST_SUM_NOW_ARPU,
           a2.ADD_SUM_NOW_ARPU,a3.LOSS_SUM_NOW_ARPU
   from (
      select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU)
              as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
              EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU
      from
         (select distinct cust_id
         from session.V_PRE_EXP_GROUP_CUST1 ) a inner join
              ccp.PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id
              inner join ccp.CUST_CHURN_INFO_200710 aa on
              aa.cust_id=a.cust_id inner join ccp.CUST_CCP_INFO_200710 cc on
              cc.cust_id=a.cust_id
      group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join
      (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu,
              sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
              ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as
              ADD_SUM_NOW_ARPU
      from ccp.PAR_CUST_CUST_GRP_ASSOC_200710 b inner join
         (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU
         from ccp.CUST_CHURN_INFO_200710 base
         where on_net_time=4) cust on cust.cust_id=b.cust_id inner join
              ccp.CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id
      group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on
           a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full
           outer join (
      select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu,
              sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as
              LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as
              LOSS_SUM_NOW_ARPU
      from ccp.PAR_CUST_CUST_GRP_ASSOC_200710 a inner join
         (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU
         from ccp.CUST_CHURN_INFO_200710 b
         where b.LEAVE_DATE <= '3000-11-30 00:00:00') churn on
              churn.cust_id=a.cust_id inner join ccp.CUST_CCP_INFO_200710 cc
              on cc.cust_id=a.cust_id
      group by a.cust_group_id,churn.pre_m_arpu_d ) a3 on
           a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d )
with ur

wangzhonnew

原帖由 myfriend2010 于 2007-12-14 15:12 发表



我可以肯定问题就在临时表那边,如果不使用临时表就正常!而且就是这段代码造成的!这个askgyliu你就相信我吧!

唉,说了无数遍很有可能就是临时表统计信息的问题...你想想使用临时表和内嵌查询的最明显的区别在哪里....

myfriend2010

恩,我知道!

原帖由 wangzhonnew 于 2007-12-14 19:23 发表

唉,说了无数遍很有可能就是临时表统计信息的问题...你想想使用临时表和内嵌查询的最明显的区别在哪里....

wangzhonnew

原帖由 myfriend2010 于 2007-12-14 20:26 发表
恩,我知道!

那你有没有加索引,对临时表作runstats然后再看访问计划呢?反正从原先我看到的计划看,db2对临时表的card使用151,应该远远小于真实数值吧

myfriend2010

原帖由 askgyliu 于 2007-12-14 15:47 发表
既然你那么肯定，那麻烦你再做一个测试。把下面的SQL的EXPLAIN OUTPUT放上来看看？

insert into session.V_HML_EAL_tem( group_id ,pre_m_arpu_d , EXIST_CUST_C,
        ADD_CUST_C,LOSS_CUST_C, EXIST_AVG_ARPU,exist_M,exist_CLV,
        ADD_AVG_ARPU,add_M,Add_CLV, LOSS_AVG_ARPU,loss_M,loss_CLV,
        EXIST_SUM_NOW_ARPU,ADD_SUM_NOW_ARPU,LOSS_SUM_NOW_ARPU) (
   select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id,
           coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d),
           EXIST_CUST_C,ADD_CUST_C,LOSS_CUST_C, a1.arpu,a1.m,a1.clv, a2.arpu,
           a2.m,a2.clv, a3.arpu,a3.m,a3.clv, a1.EXIST_SUM_NOW_ARPU,
           a2.ADD_SUM_NOW_ARPU,a3.LOSS_SUM_NOW_ARPU
   from (
      select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU)
              as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
              EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU
      from
         (select distinct cust_id
         from session.V_PRE_EXP_GROUP_CUST1 ) a inner join
              ccp.PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id
              inner join ccp.CUST_CHURN_INFO_200710 aa on
              aa.cust_id=a.cust_id inner join ccp.CUST_CCP_INFO_200710 cc on
              cc.cust_id=a.cust_id
      group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join
      (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu,
              sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as
              ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as
              ADD_SUM_NOW_ARPU
      from ccp.PAR_CUST_CUST_GRP_ASSOC_200710 b inner join
         (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU
         from ccp.CUST_CHURN_INFO_200710 base
         where on_net_time=4) cust on cust.cust_id=b.cust_id inner join
              ccp.CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id
      group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on
           a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full
           outer join (
      select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu,
              sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as
              LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as
              LOSS_SUM_NOW_ARPU
      from ccp.PAR_CUST_CUST_GRP_ASSOC_200710 a inner join
         (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU
         from ccp.CUST_CHURN_INFO_200710 b
         where b.LEAVE_DATE  

ok 了

myfriend2010

马上贴出来

原帖由 wangzhonnew 于 2007-12-14 19:32 发表

那你有没有加索引,对临时表作runstats然后再看访问计划呢?反正从原先我看到的计划看,db2对临时表的card使用151,应该远远小于真实数值吧

wangzhonnew

你的那么多tablescan本身就是问题,为啥到现在也不修复呢?

myfriend2010

原帖由 myfriend2010 于 2007-12-14 19:37 发表
马上贴出来

exfmt4为对临时表runstats的数据
exfmt5为注释掉对临时表runstats的数据

myfriend2010

恩,我忙过这几天就整

原帖由 wangzhonnew 于 2007-12-14 19:39 发表
你的那么多tablescan本身就是问题,为啥到现在也不修复呢?

wangzhonnew

临时表真的只有151行?select count(*)出来看看呢

[ 本帖最后由 wangzhonnew 于 2007-12-14 20:59 编辑 ]