sql 使CPU使用100%，棘手！

askgyliu

Two key factors to optimize query in EEE DB2
1) To localize the processing as far as possible, and
2) To evenly distribute the processing among all the participating nodes by evenly distributing data among all the participating nodes

To achieve point 1, point 2 can and must be sacrificed.

Also, I will very, very hesitate to see any table having partitioning key with more than one column.

Last but not least, I would think to paste/study DDL for any performance issue should be the first check point, instead of pasting access plan.

myfriend2010

高实在是高！

PAR_CUST_CUST_GRP_ASSOC_200710确实是2个partition key ！但是跑9月份的数据没有问题啊，PAR_CUST_CUST_GRP_ASSOC_200709也是2个partition key~！

原帖由 wangzhonnew 于 2007-12-6 21:41 发表
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

myfriend2010

原帖由 askgyliu 于 2007-12-6 21:52 发表
Two key factors to optimize query in EEE DB2
1) To localize the processing as far as possible, and
2) To evenly distribute the processing among all the participating nodes by evenly distributing data among all the participating nodes

To achieve point 1, point 2 can and must be sacrificed.

Also, I will very, very hesitate to see any table having partitioning key with more than one column.

Last but not least, I would think to paste/study DDL for any performance issue should be the first check point, instead of pasting access plan.

马上去把partition key改成1个来试一下！！

myfriend2010

昨天去把这张表：CUST_CCP_INFO_200710的数据重新生成了一下！结果跑10月份的数据就没有问题了！没有出现CPU100%的现象！

但是之前CUST_CCP_INFO_200710表reorgchk也没有问题啊！

看我runstatus语句：
RUNSTATS ON TABLE "CCP"."CUST_CCP_INFO_200710"
  ON ALL COLUMNS
  WITH DISTRIBUTION ON KEY COLUMNS
  ALLOW READ ACCESS;

askgyliu

Maybe you can upload the DDL for the tables, including all the indexes, the node group where the tables are.

myfriend2010

DDL如下：
-- Start of generated script for 169.254.11.164-db2-DW (ccp)
--  Dec-07-2007 at 09:25:41

CREATE TABLE "CCP"."CUST_CCP_INFO_200710"
("CUST_ID"            DECIMAL(17, 0),
  "REGION_COD"         DECIMAL(6, 0),
  "NOW_REAL_S"         VARCHAR(50),
  "PREV_M_FORE_S"      VARCHAR(50),
  "POLI_PRIV"          DECIMAL(16, 3),
  "POLI_PRIV_D"        VARCHAR(20),
  "M"                  DECIMAL(16, 2),
  "M_D"                VARCHAR(20),
  "NOW_REAL_LOSS"      DECIMAL(1, 0),
  "PREV_M2_PRED_LOSS"  DECIMAL(1, 0),
  "PREV_M_REAL_S"      VARCHAR(50),
  "R"                  DECIMAL(16, 3),
  "LOSS_INDEX"         DECIMAL(8, 0),
  "CLV"                DECIMAL(16, 2),
  "CLV_D"              VARCHAR(20),
  "CLI_RETURN"         DECIMAL(16, 3),
  "CLI_EXP_RETURN"     DECIMAL(16, 3),
  "CLI_V_LEVEL"        VARCHAR(20),
  "CLI_LEVEL"          VARCHAR(20),
  "NEWLY_CARE_T"       DATE,
  "NOW_POLI"           VARCHAR(500),
  "NOW_PRED_S"         VARCHAR(50),
  "NOW_PRED_LOSS"      DECIMAL(1, 0),
  "FIT_ACT_TYPE"       VARCHAR(100),
  "LOSS_RIS_LEVEL"     VARCHAR(30),
  "FIX_COST"           DECIMAL(10, 2),
  "ONCE_COST"          DECIMAL(10, 2),
  "FIT_TC"             VARCHAR(200),
  "FIT_TC_PRI_SALE"    VARCHAR(1000),
  "FIT_TC_PRI_LOS"     VARCHAR(1000),
  "FIT_INTEG_POLI"     VARCHAR(20),
  "ACC_INTEG"          DECIMAL(10, 0),
  "USAB_INTEG"         DECIMAL(10, 0),
  "CUST_BRAND"         VARCHAR(240),
  "ENTER_DATE"         TIMESTAMP,
  "LOAD_TIME"          TIMESTAMP,
  "CUST_BIRTH"         DATE,
  "AREA_CODE"          DECIMAL(5, 0)
)
  DATA CAPTURE NONE
IN "CCP"
  PARTITIONING KEY
   (CUST_ID
   ) USING HASHING;

ALTER TABLE "CCP"."CUST_CCP_INFO_200710"
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE
  LOG INDEX BUILD NULL;

CREATE TABLE "CCP"."CUST_CHURN_INFO_200710"
("CUST_ID"             DECIMAL(17, 0)  NOT NULL,
  "CUST_NAME"           VARCHAR(150),
  "ACC_NBR"             VARCHAR(24),
  "CUST_AGE"            INTEGER,
  "CUST_SEX"            VARCHAR(6),
  "REGION_COD"          DECIMAL(6, 0),
  "PROD_TYPE"           VARCHAR(100),
  "USER_TYPE"           VARCHAR(150),
  "LEAVE_DATE"          TIMESTAMP,
  "NOW_TALK_S"          VARCHAR(50),
  "PREV_M1_TALK_S"      VARCHAR(50),
  "NOW_TALK_AM"         DECIMAL(16, 2),
  "NOW_TALK"            DECIMAL(16, 2),
  "NOW_ARPU"            DECIMAL(16, 2),
  "PRE_M_TALK_AM"       DECIMAL(16, 2),
  "PRE_M_TALK"          DECIMAL(16, 2),
  "PRE_M_ARPU"          DECIMAL(16, 2),
  "PRE_M_ARPU_D"        VARCHAR(20),
  "PRE_M_PRIV"          DECIMAL(16, 3),
  "PRE_M_PRIV_D"        VARCHAR(20),
  "ARPU_STAND_COE_D"    DECIMAL(16, 3),
  "PRE_M_INLOCAL_FEE"   DECIMAL(16, 2),
  "PRE_M_INLOCAL_TALK"  DECIMAL(16, 2),
  "PRE_M_INLOCAL_TALK_D"VARCHAR(20),
  "INLOCAL_PRIV"        DECIMAL(16, 3),
  "INLOCAL_R"           DECIMAL(16, 3),
  "INLOCAL_TALK_R"      DECIMAL(16, 3),
  "PRE_M_BELOCAL_FEE"   DECIMAL(16, 2),
  "PRE_M_BELOCAL_TALK"  DECIMAL(16, 2),
  "PRE_M_BELOCAL_TALK_D"VARCHAR(20),
  "BELOCAL_PRIV"        DECIMAL(16, 3),
  "BELOCAL_R"           DECIMAL(16, 3),
  "BELOCAL_TALK_R"      DECIMAL(16, 3),
  "PRE_M_NET_FEE"       DECIMAL(16, 2),
  "PRE_M_NET_TALK"      DECIMAL(16, 2),
  "PRE_M_NET_TALK_D"    VARCHAR(20),
  "NET_PRIV"            DECIMAL(16, 3),
  "NET_R"               DECIMAL(16, 3),
  "NET_TALK_R"          DECIMAL(16, 3),
  "PRE_M_IP_FEE"        DECIMAL(16, 2),
  "PRE_M_IP_TALK"       DECIMAL(16, 2),
  "PRE_M_IP_TALK_D"     VARCHAR(20),
  "IP_PRIV"             DECIMAL(16, 3),
  "IP_R"                DECIMAL(16, 3),
  "IP_TALK_R"           DECIMAL(16, 3),
  "PRE_M_HIRE"          DECIMAL(16, 2),
  "PRE_M_HIRE_BUS"      DECIMAL(16, 2),
  "HIRE_R"              DECIMAL(16, 3),
  "HIRE_BUS_R"          DECIMAL(16, 3),
  "HIRE_PRIV"           DECIMAL(16, 3),
  "PRE_M_CHIT"          DECIMAL(16, 2),
  "PRE_M_CHIT_BUS"      DECIMAL(16, 2),
  "CHIT_BUS_D"          VARCHAR(20),
  "CHIT_PRIV"           DECIMAL(16, 3),
  "CHIT_R"              DECIMAL(16, 3),
  "CHIT_BUS_R"          DECIMAL(16, 3),
  "PRE_M_OTH_ADD"       DECIMAL(16, 2),
  "PRE_M_OTH_ADD_BUS"   DECIMAL(16, 2),
  "OTH_ADD_PRIV"        DECIMAL(16, 3),
  "OTH_ADD_R"           DECIMAL(16, 3),
  "OTH_ADD_BUS_R"       DECIMAL(16, 3),
  "ON_NET_TIME"         DECIMAL(16, 2),
  "NOW_TALK_AM_HR"      DECIMAL(16, 3),
  "PREV_M1_TALK_AM_HR"  DECIMAL(16, 3),
  "PREV_M2_TALK_AM_HR"  DECIMAL(16, 3),
  "NOW_TALK_HR"         DECIMAL(16, 3),
  "PREV_M1_TALK_HR"     DECIMAL(16, 3),
  "PREV_M2_TALK_HR"     DECIMAL(16, 3),
  "NOW_ARPU_HR"         DECIMAL(16, 3),
  "PREV_M1_ARPU_HR"     DECIMAL(16, 3),
  "PREV_M2_ARPU_HR"     DECIMAL(16, 3),
  "NOW_TALK_AM_CP"      DECIMAL(16, 3),
  "PREV_M1_TALK_AM_CP"  DECIMAL(16, 3),
  "PREV_M2_TALK_AM_CP"  DECIMAL(16, 3),
  "NOW_TALK_CP"         DECIMAL(16, 3),
  "PREV_M1_TALK_CP"     DECIMAL(16, 3),
  "PREV_M2_TALK_CP"     DECIMAL(16, 3),
  "NOW_ARPU_CP"         DECIMAL(16, 3),
  "PREV_M1_ARPU_CP"     DECIMAL(16, 3),
  "PREV_M2_ARPU_CP"     DECIMAL(16, 3),
  "PRE_M_11808_TALK"    DECIMAL(16, 2),
  "PRE_M_11808_FEE"     DECIMAL(16, 2),
  "PRIV_11808"          DECIMAL(16, 3),
  "R_11808"             DECIMAL(16, 3),
  "TALK_11808_R"        DECIMAL(16, 3),
  "PRE_M_INLOCAL_C"     DECIMAL(8, 0),
  "PRE_M_INLOCAL_DUR"   DECIMAL(16, 0),
  "PRE_M_NET_C"         DECIMAL(8, 0),
  "PRE_M_NET_DUR"       DECIMAL(16, 0),
  "PRE_M_BELOCAL_C"     DECIMAL(8, 0),
  "PRE_M_BELOCAL_DUR"   DECIMAL(16, 0),
  "PRE_M_IP_C"          DECIMAL(8, 0),
  "PRE_M_IP_DUR"        DECIMAL(16, 0),
  "PRE_M_11808_C"       DECIMAL(8, 0),
  "PRE_M_11808_DUR"     DECIMAL(16, 0),
  "TALK_STRU"           VARCHAR(20),
  "ON_NET_TIME_D"       VARCHAR(20),
  "NOW_USE_S"           VARCHAR(20),
  "STRAT_G"             VARCHAR(240),
  "NOW_REAL_S"          VARCHAR(50),
  "LOAD_TIME"           TIMESTAMP,
  "NEXT_M1_REAL_S"      VARCHAR(50),
  "AREA_CODE"           DECIMAL(6, 0)
)
  DATA CAPTURE NONE
IN "CCP"
  PARTITIONING KEY
   (CUST_ID
   ) USING HASHING
  NOT LOGGED INITIALLY;

ALTER TABLE "CCP"."CUST_CHURN_INFO_200710"
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE
  LOG INDEX BUILD NULL;

CREATE TABLE "CCP"."PAR_CUST_CUST_GRP_ASSOC_200710"
("CUST_ID"        DECIMAL(17, 0),
  "CUST_GROUP_ID"  DECIMAL(20, 0),
  "MODEL_ID"       DECIMAL(8, 0),
  "LOAD_TIME"      TIMESTAMP
)
  DATA CAPTURE NONE
IN "CCP"
  PARTITIONING KEY
   (CUST_ID,
    CUST_GROUP_ID
   ) USING HASHING
  NOT LOGGED INITIALLY;

ALTER TABLE "CCP"."PAR_CUST_CUST_GRP_ASSOC_200710"
  LOCKSIZE ROW
  APPEND ON
  NOT VOLATILE
  LOG INDEX BUILD NULL;



-- End of generated script for 169.254.11.164-db2-DW (ccp)

myfriend2010

没有就PAR_CUST_CUST_GRP_ASSOC_200710上有一个index

CREATE INDEX "CCP"."PAR_CUST_ID_0710"
  ON "CCP"."PAR_CUST_CUST_GRP_ASSOC_200710"
("CUST_ID" ASC
)
  PCTFREE 10
  DISALLOW REVERSE SCANS;

wangzhonnew

hmm... not sure why repopulating the MQT make db2 pickup the correct plan... still thinking it's something about statistics~~~ but anyway, are you going to try to modify the partition key for base table to only one column? if do please let us know if it can further improve the performance~~~

wangzhonnew

btw could you tell me if the 4 partitions are located on the same physical host? or different boxes?

askgyliu

原帖由 wangzhonnew 于 2007-12-7 10:54 发表
still thinking it's something about statistics~~~

This is also possible.

Cardinality may be wrong in the statistics collection.

Remember the test case I presented sometimes ago that statistics about the same table can be very different if collected from different node?

For joining between two tables with some remarkable volume but without specific filtering condition, like what is shown here, the optimizer should rightly select MS join or Hash join instead of NL join.