一个很简单的SQL, CPU usage 非常高。
select * from alt.traffic_record where service =? and start_time > ? and start_time < ?
table 有cluster index on these 2 columns (SERVICE, START_TIME),非partition index, 不知道问题出在什么地方,为什么CPU usage如此之高!?
Table DDL is as below:
CREATE TABLE "ALT "."TRAFFIC_RECORD" (
"OID" INTEGER NOT NULL ,
"SERVICE" INTEGER ,
"CUSTOMER" INTEGER ,
"BILLENTRY" BIGINT ,
"START_TIME" TIMESTAMP NOT NULL ,
"CALLING_NUMBER" VARCHAR(20 OCTETS) NOT NULL ,
"CALLED_NUMBER" VARCHAR(20 OCTETS) NOT NULL ,
"BILLABLE_NUMBER" VARCHAR(20 OCTETS) ,
"PLAN" VARCHAR(80 OCTETS) ,
"COST" INTEGER ,
"DESTINATION" VARCHAR(80 OCTETS) ,
"CALL_CHARGE" INTEGER ,
"GST_FLAG" CHAR(1 OCTETS) ,
"RATE_DATE" DATE ,
"BILLING_CODE" CHAR(8 OCTETS) ,
"SESSION_ID" VARCHAR(40 OCTETS) NOT NULL ,
"CALL_SOURCE" SMALLINT NOT NULL ,
"CREATED" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"CREATED_BY" VARCHAR(32 OCTETS) NOT NULL ,
"LAST_MODIFIED" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"MODIFIED_BY" VARCHAR(32 OCTETS) NOT NULL ,
"CALL_CHARGE_V5" BIGINT ,
"TYPE_CATEGORY_ID" SMALLINT ,
"DESTINATION_TISP_ID" SMALLINT ,
"COST_BEFORE_DISCOUNT" INTEGER ,
"QUANTITY" BIGINT NOT NULL ,
"ROW_CHANGE_TS" TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP )
INDEX IN "DATAREC_IDX" PARTITION BY RANGE("START_TIME")
(PART "Q3M7_2012" STARTING('2012-07-01-00.00.00.000000') ENDING('2012-08-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q3_TAB",
PART "Q3M8_2012" STARTING('2012-08-01-00.00.00.000000') ENDING('2012-09-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q3_TAB",
PART "Q3M9_2012" STARTING('2012-09-01-00.00.00.000000') ENDING('2012-10-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q3_TAB",
PART "Q4M10_2012" STARTING('2012-10-01-00.00.00.000000') ENDING('2012-11-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q4_TAB",
PART "Q4M11_2012" STARTING('2012-11-01-00.00.00.000000') ENDING('2012-12-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q4_TAB",
PART "Q4M12_2012" STARTING('2012-12-01-00.00.00.000000') ENDING('2013-01-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2012Q4_TAB",
PART "Q1M01_2013" STARTING('2013-01-01-00.00.00.000000') ENDING('2013-02-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2013Q1_TAB",
PART "Q1M02_2013" STARTING('2013-02-01-00.00.00.000000') ENDING('2013-03-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2013Q1_TAB",
PART "Q1M03_2013" STARTING('2013-03-01-00.00.00.000000') ENDING('2013-04-01-00.00.00.000000') EXCLUSIVE IN "DATAREC_2013Q1_TAB",
PART "PART9" STARTING('2013-04-01-00.00.00.000000') ENDING('2013-05-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART10" STARTING('2013-05-01-00.00.00.000000') ENDING('2013-06-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART11" STARTING('2013-06-01-00.00.00.000000') ENDING('2013-07-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART12" STARTING('2013-07-01-00.00.00.000000') ENDING('2013-08-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART13" STARTING('2013-08-01-00.00.00.000000') ENDING('2013-09-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART14" STARTING('2013-09-01-00.00.00.000000') ENDING('2013-10-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART15" STARTING('2013-10-01-00.00.00.000000') ENDING('2013-11-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART16" STARTING('2013-11-01-00.00.00.000000') ENDING('2013-12-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART17" STARTING('2013-12-01-00.00.00.000000') ENDING('2014-01-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART18" STARTING('2014-01-01-00.00.00.000000') ENDING('2014-02-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART19" STARTING('2014-02-01-00.00.00.000000') ENDING('2014-03-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART20" STARTING('2014-03-01-00.00.00.000000') ENDING('2014-04-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART21" STARTING('2014-04-01-00.00.00.000000') ENDING('2014-05-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART22" STARTING('2014-05-01-00.00.00.000000') ENDING('2014-06-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART23" STARTING('2014-06-01-00.00.00.000000') ENDING('2014-07-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART24" STARTING('2014-07-01-00.00.00.000000') ENDING('2014-08-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART25" STARTING('2014-08-01-00.00.00.000000') ENDING('2014-09-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART26" STARTING('2014-09-01-00.00.00.000000') ENDING('2014-10-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART27" STARTING('2014-10-01-00.00.00.000000') ENDING('2014-11-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART28" STARTING('2014-11-01-00.00.00.000000') ENDING('2014-12-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART29" STARTING('2014-12-01-00.00.00.000000') ENDING('2015-01-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART30" STARTING('2015-01-01-00.00.00.000000') ENDING('2015-02-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART31" STARTING('2015-02-01-00.00.00.000000') ENDING('2015-03-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART32" STARTING('2015-03-01-00.00.00.000000') ENDING('2015-04-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART33" STARTING('2015-04-01-00.00.00.000000') ENDING('2015-05-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART34" STARTING('2015-05-01-00.00.00.000000') ENDING('2015-06-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART35" STARTING('2015-06-01-00.00.00.000000') ENDING('2015-07-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART36" STARTING('2015-07-01-00.00.00.000000') ENDING('2015-08-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART37" STARTING('2015-08-01-00.00.00.000000') ENDING('2015-09-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART38" STARTING('2015-09-01-00.00.00.000000') ENDING('2015-10-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART39" STARTING('2015-10-01-00.00.00.000000') ENDING('2015-11-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART40" STARTING('2015-11-01-00.00.00.000000') ENDING('2015-12-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART41" STARTING('2015-12-01-00.00.00.000000') ENDING('2016-01-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART42" STARTING('2016-01-01-00.00.00.000000') ENDING('2016-02-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART43" STARTING('2016-02-01-00.00.00.000000') ENDING('2016-03-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART44" STARTING('2016-03-01-00.00.00.000000') ENDING('2016-04-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART45" STARTING('2016-04-01-00.00.00.000000') ENDING('2016-05-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART46" STARTING('2016-05-01-00.00.00.000000') ENDING('2016-06-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART47" STARTING('2016-06-01-00.00.00.000000') ENDING('2016-07-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K",
PART "PART48" STARTING('2016-07-01-00.00.00.000000') ENDING('2016-08-01-00.00.00.000000') EXCLUSIVE IN "TRAFFIC_REC_DAT_4K")
ORGANIZE BY ROW;
CREATE INDEX "ALT "."IX_TR_START_SERV" ON "ALT "."TRAFFIC_RECORD"
("START_TIME" ASC,
"SERVICE" ASC)
NOT PARTITIONED IN "DATAREC_IDX"
CLUSTER
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
收起