sql 使CPU使用100%，棘手！

myfriend2010

。。。。
怎么处理？

myfriend2010

刚捕获的snapshot

myfriend2010

初步诊断为CUST_CHURN_INFO_200710、CUST_CCP_INFO_200710、PAR_CUST_CUST_GRP_ASSOC_200710 这3张表那个表可能有问题！因为跑第二个sql没有问题！

我觉得sql的优化必要不是很大，系统参数的设置可能也不会有问题！因为第二个sql跑没有问题！表的数量级一样！

wangzhonnew

导致高cpu的应该是下面的这个部分吧
                                                  2.40294e+010
                                                     NLJOIN
                                                     (   7)
                                                  3.03698e+009
                                                  1.24674e+006
                                                  /-----+----\
                                          4.04749e+006       5722.82
                                             TBSCAN          TBSCAN
                                             (   8)          (  33)
                                          3.58409e+006       229839
                                          1.11692e+006       129816
对临时表和CUST_CCP_INFO_20的NLJN产生2.40294e+010行……这个花销看起来不小的说

askgyliu

Why don't paste the DDL/number of rows/number of distinct cust_id  for all the tables?

wangzhonnew

hmm.. i guess PAR_CUST_ID_0710 is pk, as there's only 1st card and 1st card=full card...
what is the current partition column for those tables?
and did you include distribute clause in runstats?

[ 本帖最后由 wangzhonnew 于 2007-12-6 22:34 编辑 ]

wangzhonnew

and possible to create index to
CCP.CUST_CCP_INFO_200710."M"+CCP.CUST_CCP_INFO_200710."CUST_ID"

wangzhonnew

is it true that partition columns for table PAR_CUST_CUST_GRP_ASSOC_200710 are CUST_ID+CUST_GROUP_ID, but for other 2 MQT the partition column is CUST_ID?

it looks there are so many table queues to switch the partition columns

askgyliu

原帖由 wangzhonnew 于 2007-12-6 21:41 发表
it looks there are so many table queues to switch the partition columns

Precisely. I believe this is the problem.

Look at the one having no problem (_09 query), the access plan is to retrieve data from individual table, and broadcast locally, MS Joined.

But for the problematic query (_10), the very first access is in chaos.

wangzhonnew

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


Precisely. I believe this is the problem.

Look at the one having no problem (_09 query), the access plan is to retrieve data from individual table, and broadcast locally, MS Joined.

But for the problematic query (_10), the very first access is in chaos.

yeah, i think it may be better to have the base table only partitioned by cust_id column, because all your join predicates are only on top of this column rather than cust_id+cust_group_id