sql 使CPU使用100%，棘手！

askgyliu

原帖由 myfriend2010 于 2007-12-12 18:18 发表
to:askgyliu
A: 这边无论临时表还是fact表都是SMS的!呵呵

那 storage configuration for FACt and TEMP 是 不 是 一 样 的 ？ 比 如 说 FACT有 N个 硬 盘 ， 而 TEMP又 M个 硬 盘 ？

askgyliu

把 你 的 FACT跟 TEMP放 到 同 个 FS上 看 看 啊 ？

askgyliu

原帖由 myfriend2010 于 2007-12-12 18:18 发表
to:askgyliu
Q:这最近的三个SQL跟最开始的两个SQL是不相同的？

A:是啊,我把10月份的表重新生成并分析后,就这个样子了!这是最新的执行计划,以前的执行计划无法得到了!

我 再 看 了 你 5楼 的 贴 ， 和 EXFMT1中 的 SQL， 我 确 信 它 们 是 完 全 不 相 同 的 。

就 以 你 EXFMT1中 的 SQL来 分 析 一 下 吧 。

实 际 是 有 三 个 部 分 ：
P1：
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 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

P2：
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

P3：
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

你 的 SQL 再 把 这 三 个 部 分 给 FULL OUTER JOIN 起 来 。

askgyliu

几 个 问 题 ：

1) FULL OUTER JOIN 是 真 的 需 要 的 吗 ？ 分 析 一 下 你 的 DATA PATTERN。 FULL OUTER JOIN 和 RIGHT OUTER JOIN 经 常 会 有 莫 名 其 妙 的 PERFORMANCE 问 题 。

2) 几 个 TABLES 的 相 互 关 系 是 什 么 ？ 是 UNIQUE JOIN 吗 ？ 不 是 的 话 ， 有 没 有 办 法 先 把 它 们 弄 UNIQUE 再 JOIN？

3) 从 你 的 三 个 部 分 来 看 ， V_PRE_EXP_GROUP_CUST1， PAR_CUST_CUST_GRP_ASSOC_200710， CUST_CHURN_INFO_200710 和 CUST_CCP_INFO_200710 必 需 在 CUST_ID 是 UNIQUE的 ， 不 然 的 话 结 果 会 是 DOUBLE COUNTING。 那 是 真 的 你 所 要 的 吗 ？

4) 实 际 上 P2 和 P3 是 可 能  合 并 成 一 个 部 分 。

5) 可 以 把 三 个 部 分 先 独 自 各 自 做 成 一 个 TEMP TABLE， 再 JOIN 吗 ？

askgyliu

假设所有TABLES的CUST_ID都是UNIQUE的，下面的SQL会得到同样的结果，而对每个TABLE只需要用到一次TABLE SCAN。

      select  b.cust_group_id as group_id, aa.pre_m_arpu_d,
--              sum(case when a.cust_id is not null then aa.PRE_M_ARPU end) as a1_arpu,
--              sum(case when a.cust_id is not null then cc.m end) as a1_m,
--              sum(case when a.cust_id is not null then cc.CLV end) as a1_clv,
--              sum(case when a.cust_id is not null then 1 end) as EXIST_CUST_C,
--              sum(case when a.cust_id is not null then aa.NOW_ARPU end) as EXIST_SUM_NOW_ARPU,

--              sum(case when aa.on_net_time=4 then aa.PRE_M_ARPU end) as a2_arpu,
--              sum(case when aa.on_net_time=4 then cc.m end) as a2_m,
--              sum(case when aa.on_net_time=4 then cc.CLV end) as a2_clv,
--              sum(case when aa.on_net_time=4 then b.cust_id end) as ADD_CUST_C,
--              sum(case when aa.on_net_time=4 then aa.NOW_ARPU end) as ADD_SUM_NOW_ARPU ,

--              sum(case when aa.leave_date<='3000-11-30 00:00:00' then aa.PRE_M_ARPU end) as a3_arpu,
--              sum(case when aa.leave_date<='3000-11-30 00:00:00' then cc.m end) as a3_m,
--              sum(case when aa.leave_date<='3000-11-30 00:00:00' then cc.CLV end) as a3_clv,
--              sum(case when aa.leave_date<='3000-11-30 00:00:00' then 1 end) as LOSS_CUST_C,
              sum(case when aa.leave_date<='3000-11-30 00:00:00' then aa.NOW_ARPU end) as LOSS_SUM_NOW_ARPU ,
              COUNT(b.CUST_ID) as OCCURANCE,
              COUNT(DISTINCT b.CUST_ID) as OCCURANCE2

      from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join CUST_CHURN_INFO_200710 aa
             on aa.cust_id=b.cust_id
           inner join CUST_CCP_INFO_200710 cc
             on cc.cust_id=b.cust_id
           left outer join V_PRE_EXP_GROUP_CUST1 a
             on b.cust_id = a.cust_id
      where (a.cust_id is not null or
             aa.on_net_time=4 or
             aa.LEAVE_DATE <= '3000-11-30 00:00:00')
      group by b.cust_group_id,aa.pre_m_arpu_d

askgyliu

用同样的数据测试如下。

我的版本会有两条结果，LZ的有三条。但在GROUP_ID+PRE_M_ARPU_D的总结上还是一样的。

/home/db2inst/sql_performance/test2 > db2 -tf tt.sql

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                34.70                             34.70
                    2. A                                                17.30                             17.30

  2 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -tf t1.sql

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                    -                             34.70
                    2. A                                                17.30                             17.30
                    1. A                                                34.70                                 -

  3 record(s) selected.

myfriend2010

TEMP 和 FACt 在同一个盘上,不同的目录下,都是SMS的!

原帖由 askgyliu 于 2007-12-12 22:26 发表


那 storage configuration for FACt and TEMP 是 不 是 一 样 的 ？ 比 如 说 FACT有 N个 硬 盘 ， 而 TEMP又 M个 硬 盘 ？

askgyliu

把LZ的SQL分成三部分分开执行，结果如下：

/home/db2inst/sql_performance/test2 > db2 -tvf p1.sql
select b.cust_group_id as group_id, aa.pre_m_arpu_d, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         EXIST_SUM_NOW_ARPU
---------------------- -------------------- ---------------------------------
                    2. A                                                17.30

  1 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -tvf p2.sql
select b.cust_group_id as group_id, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from 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 CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU
---------------------- -------------------- ---------------------------------
                    1. A                                                34.70
                    2. A                                                17.30

  2 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -tvf p3.sql
select a.cust_group_id as group_id, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from 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 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 CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by a.cust_group_id,churn.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         LOSS_SUM_NOW_ARPU
---------------------- -------------------- ---------------------------------
                    1. A                                                34.70
                    2. A                                                17.30

  2 record(s) selected.

有意思的是，这三个FULL OUTER JOIN在一起居然产生了三行数据。我的承认这个是我所料不及的。我以为这里只是产生两行结果。

myfriend2010

PAR_CUST_CUST_GRP_ASSOC_200710 中CUST_ID不是唯一的!而且重复性很大,在这个表中只有cust和cust_group_id组合才是唯一索引!

原帖由 askgyliu 于 2007-12-13 13:13 发表
假设所有TABLES的CUST_ID都是UNIQUE的，下面的SQL会得到同样的结果，而对每个TABLE只需要用到一次TABLE SCAN。

      select  b.cust_group_id as group_id, aa.pre_m_arpu_d,
--              sum(case when a.cust_id is not null then aa.PRE_M_ARPU end) as a1_arpu,
--              sum(case when a.cust_id is not null then cc.m end) as a1_m,
--              sum(case when a.cust_id is not null then cc.CLV end) as a1_clv,
--              sum(case when a.cust_id is not null then 1 end) as EXIST_CUST_C,
--              sum(case when a.cust_id is not null then aa.NOW_ARPU end) as EXIST_SUM_NOW_ARPU,

--              sum(case when aa.on_net_time=4 then aa.PRE_M_ARPU end) as a2_arpu,
--              sum(case when aa.on_net_time=4 then cc.m end) as a2_m,
--              sum(case when aa.on_net_time=4 then cc.CLV end) as a2_clv,
--              sum(case when aa.on_net_time=4 then b.cust_id end) as ADD_CUST_C,
--              sum(case when aa.on_net_time=4 then aa.NOW_ARPU end) as ADD_SUM_NOW_ARPU ,

--              sum(case when aa.leave_date

myfriend2010

==下午给你搞定!

原帖由 wangzhonnew 于 2007-12-12 22:16 发表

1) add index to cust_id
2) runstats to the temp table