sql 使CPU使用100%，棘手！

myfriend2010

好像是访问数据库的具体某张表的时候CPU使用就会达到100%！

这张表也reorg过，runstatus了！不知为什么？访问这张表的大数据量的计算CPU就会100%！

会不会和数据库、表空间、表的某一个参数有关系？

myfriend2010

windows 下，db2 V8.1.7

bpmfhu

看看sql的计划

nuoran

有没有打开awe？
是否sortheap太大导致DB2放弃索引而选择排序导致cpu高开销？
能找到sql的话,expln一下，或者抓个db和app的快照在100％时期。

myfriend2010

2 个sql
                   select
                b.cust_group_id group_id,
                avg(case when a.cust_age >0 then a.cust_age end) avg_age,
                sum(a.now_talk_am)/count(b.cust_id) now_talk_am_cust,
                sum(a.now_talk)/count(b.cust_id) now_talk_cust,
                sum(a.now_arpu)/count(b.cust_id) now_income_cust,
                sum(a.pre_m_talk_am)/count(b.cust_id) pre_m_talk_am_cust,
                sum(a.pre_m_talk)/count(b.cust_id) pre_m_talk_cust,
                sum(a.pre_m_arpu)/count(b.cust_id) pre_m_income_cust,
                sum(a.pre_m_arpu) income,
                sum(c.m) m,
                sum(c.m)/count(b.cust_id) m_cust,
                case when  count(case when a.cust_sex='女' then b.cust_id end) =0 then null else
                cast(count(case when a.cust_sex='男' then b.cust_id end) as decimal(16,2))/count(case when a.cust_sex='女' then b.cust_id end) end sex_r
                from CUST_CHURN_INFO_200710 a
                inner join CUST_CCP_INFO_200710  c on c.cust_id=b.cust_id
                inner join PAR_CUST_CUST_GRP_ASSOC_200710 b  on a.cust_id=b.cust_id
                   group by b.cust_group_id;

和

                   select
                b.cust_group_id group_id,
                avg(case when a.cust_age >0 then a.cust_age end) avg_age,
                sum(a.now_talk_am)/count(b.cust_id) now_talk_am_cust,
                sum(a.now_talk)/count(b.cust_id) now_talk_cust,
                sum(a.now_arpu)/count(b.cust_id) now_income_cust,
                sum(a.pre_m_talk_am)/count(b.cust_id) pre_m_talk_am_cust,
                sum(a.pre_m_talk)/count(b.cust_id) pre_m_talk_cust,
                sum(a.pre_m_arpu)/count(b.cust_id) pre_m_income_cust,
                sum(a.pre_m_arpu) income,
                sum(c.m) m,
                sum(c.m)/count(b.cust_id) m_cust,
                case when  count(case when a.cust_sex='女' then b.cust_id end) =0 then null else
                cast(count(case when a.cust_sex='男' then b.cust_id end) as decimal(16,2))/count(case when a.cust_sex='女' then b.cust_id end) end sex_r
                from CUST_CHURN_INFO_200709 a
                inner join CUST_CCP_INFO_200709  c on c.cust_id=b.cust_id
                inner join PAR_CUST_CUST_GRP_ASSOC_200709 b  on a.cust_id=b.cust_id
                   group by b.cust_group_id;

myfriend2010

附件为db2exfmt 结果

diablo2

抓快照看看

myfriend2010

快照

myfriend2010

已经上传

diablo2

总计排序                        = 38
总计排序时间（毫秒）            = 未收集
总计排序溢出                    = 22


估计和这有关