oracle下 分区表部分分区查询慢问题
我建立了一个按月分区的表但是查询该表中的11月和12月份的数据是很慢,其他月份正常,表分析,重建,rename 换表空间都试过了,但是还是没有解决,请问有人知道怎么解决吗?求指教!!
表结构如下:
DROP TABLE TG_CDR_PRM_STAG CASCADE CONSTRAINTS PURGE;
--
-- TG_CDR_PRM_STAG (Table)
--
-- Row count:44257440
CREATE TABLE TG_CDR_PRM_STAG
(
USER_ID NUMBER(16),
SOURCE_TYPE CHAR(2 BYTE),
BIZ_TYPE VARCHAR2(4 BYTE),
SP_CODE VARCHAR2(20 BYTE),
SP_TRANS_CODE VARCHAR2(20 BYTE),
SP_SERVTYPE CHAR(2 BYTE),
SP_PRODUCT_ID VARCHAR2(20 BYTE),
CITY_CODE CHAR(3 BYTE),
BILLINGCYCLID CHAR(6 BYTE),
CYCLID CHAR(6 BYTE),
CPID VARCHAR2(20 BYTE),
NET_TYPE CHAR(5 BYTE),
SRV_CODE VARCHAR2(20 BYTE),
UNTAKEN_FEE NUMBER(10),
DEFAULT_FEE NUMBER(10),
GRANT_FEE NUMBER(10),
REAL_ICDR_NUM NUMBER(10),
REAL_IFEE NUMBER(15),
DISCOUNT_FEE NUMBER(20),
REAL_ORDER_IFEE NUMBER(20),
REAL_MONFEE NUMBER(20),
REAL_ORDER_USERNUM NUMBER(20),
REAL_MONTH_USERNUM NUMBER(20),
REAL_CFEE NUMBER(20),
REAL_USERNUM NUMBER(20),
MO_SMS_NUM NUMBER(10),
MT_SMS_NUM NUMBER(10),
DUE_ICDR_NUM NUMBER(10),
DUE_IFEE NUMBER(15),
OVER20_ICDR_NUM NUMBER(10),
OVER20_IFEE NUMBER(10),
DUE_CFEE NUMBER(10),
DISCOUNT_CHARGE NUMBER(10),
GRANT_CHARGE NUMBER(10),
ORDER_CNT NUMBER(15),
TIMESEC NUMBER(15),
TOTAL_OCTETS NUMBER(15),
IN_OCTETS NUMBER(15),
OUT_OCTETS NUMBER(15),
DUE_ORDER_IFEE NUMBER(20),
DUE_MONFEE NUMBER(20),
USER_CNT NUMBER(20),
DUE_ORDER_USERNUM NUMBER(20),
DUE_MONTH_USERNUM NUMBER(20),
RSRCID VARCHAR2(20 BYTE),
AREAID CHAR(6 BYTE),
RECORD_TYPE VARCHAR2(4 BYTE),
RECORD_TIME CHAR(14 BYTE),
SERVICE_CODE VARCHAR2(20 BYTE),
USE_MSISDN VARCHAR2(20 BYTE),
START_TIME CHAR(14 BYTE),
END_TIME CHAR(14 BYTE),
DEAL_TIME CHAR(14 BYTE),
PARTITION_MONTH CHAR(2 BYTE),
FEE_TYPE VARCHAR2(4 BYTE),
FEE_POINT VARCHAR2(20 BYTE),
SERVICE_ID VARCHAR2(2 BYTE),
RESERVER1 VARCHAR2(20 BYTE),
RESERVER2 VARCHAR2(20 BYTE),
RESERVER3 VARCHAR2(20 BYTE),
RESERVER4 VARCHAR2(20 BYTE),
RESERVER5 VARCHAR2(20 BYTE),
RESERVER6 VARCHAR2(20 BYTE),
RESERVER7 VARCHAR2(20 BYTE),
RESERVER8 VARCHAR2(20 BYTE)
)
TABLESPACE TS_TAB_ACCTBASE1
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (PARTITION_MONTH)
(
PARTITION P01 VALUES LESS THAN ('02')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P02 VALUES LESS THAN ('03')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P03 VALUES LESS THAN ('04')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P04 VALUES LESS THAN ('05')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P05 VALUES LESS THAN ('06')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P06 VALUES LESS THAN ('07')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P07 VALUES LESS THAN ('08')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P08 VALUES LESS THAN ('09')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P09 VALUES LESS THAN ('10')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P10 VALUES LESS THAN ('11')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P11 VALUES LESS THAN ('12')
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P12 VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
TABLESPACE TS_TAB_ACCTBASE1
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
oracle下?分区表部分分区查询慢问题 oracle
[解决办法]
看先是不是表空间有问题,最好把用到的表空间都更新下然后,在试试。