目录
一、原因
业务查询1个月的数据,需要15-20秒,过长。
二、分析并解决
1、通过sql_trace跟踪查询语句
(1)登录sys,设置追踪打开
alter system set sql_trace=true;
(2)查看目录
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace
(3)找到最新的
mesorcl_ora_8452.trc等
(4)生成可读文档
tkprof mesorcl_ora_25624.trc test2.log sys=no
2、获取SQL语句
select pm.IND_CODE 设备编号, case when pm.BUFFER30 = '1' then A.RUNTIME_EQ else B.RUNTIME_FILE end 开机时间, '0' 开机率, PM.IND_CODE 设备编号, PM.IND_Name 设备名称, pm.buffer21 部门名称 from MES_PM_MASTERDATA pm left join (SELECT ROUND(SUM((TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24), 2) as RUNTIME_EQ, B.Eq_Code FROM MES_DC_CT_EQ A INNER JOIN MES_DC_EQPARAM B ON A.CPN_CODE = B.CPN_CODE AND B.ENABLEFLAG = 'Y' AND B.IS_VALID = 'Y' WHERE A.ENABLEFLAG = 'Y' AND A.REC_VALUE = '1' and (TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 < 10 AND A.BUFFER1 IS NOT NULL AND A.CREATEDATE >= TO_DATE('2023/8/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS') AND A.CREATEDATE <= TO_DATE('2023/8/31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') group by B.Eq_Code) A on pm.IND_CODE = A.EQ_CODE LEFT JOIN (SELECT NVL(ROUND(SUM((TO_DATE(A.JBC56, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(A.JBC55, 'YYYY-MM-DD HH24:MI:SS')) * 24), 2), 0) as RUNTIME_FILE, A.JBC51 FROM APLJBG A WHERE A.JBC02 = '4' AND A.JBC56 >= '2023-08-01 00:00:00' AND A.JBC56 <= '2023-08-31 23:59:59' group by A.JBC51) B ON pm.IND_CODE = B.JBC51 where (pm.IND_OBJ_TYPE IN ('1000205001', '1000205002', '1000205003', '1000205004') OR pm.BUFFER57 IN ('ZCZ1201', 'ZCZ1301', 'ZCZ3004', 'ZCZ2409')) AND pm.IND_STATUS = '10' AND pm.ENABLEFLAG = 'Y'
3、查询语句
4、找到执行慢的子句
SELECT ROUND(SUM((TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24), 2) as RUNTIME_EQ, B.Eq_Code FROM MES_DC_CT_EQ A INNER JOIN MES_DC_EQPARAM B ON A.CPN_CODE = B.CPN_CODE AND B.ENABLEFLAG = 'Y' AND B.IS_VALID = 'Y' WHERE A.ENABLEFLAG = 'Y' AND A.REC_VALUE = '1' and (TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 < 10 AND A.BUFFER1 IS NOT NULL AND A.CREATEDATE >= TO_DATE('2023/8/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS') AND A.CREATEDATE <= TO_DATE('2023/8/31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') group by B.Eq_Code
5、分析原因
未走索引,全是全表扫描
6、查看表的MES_DC_CT_EQ 数量
数据量也不多。
7、经过各种索引增加,仍然是走全表扫描
/*
create index ix_MES_PM_MASDATA_INDOBJTYPE on MES_PM_MASTERDATA(IND_OBJ_TYPE); create index ix_MES_PM_MASDATA_BUFFER57 on MES_PM_MASTERDATA(BUFFER57); create index ix_MES_PM_MASDATA_IND_STATUS on MES_PM_MASTERDATA(IND_STATUS); create index ix_MES_PM_MASDATA_ENABLEFLAG on MES_PM_MASTERDATA(ENABLEFLAG); create index ix_MES_PM_MASDATA_IND_CODE on MES_PM_MASTERDATA(IND_CODE); create index ix_MES_DC_CT_EQ_CPN_CODE on MES_DC_CT_EQ(CPN_CODE); create index ix_MES_DC_CT_EQ_REC_DATE on MES_DC_CT_EQ(REC_DATE); create index ix_APLJBG_JBC02 on APLJBG(JBC02); create index ix_APLJBG_JBC51 on APLJBG(JBC51); --create index ix_MES_DC_CT_EQ_ENAB LEFLAG on MES_DC_CT_EQ(ENABLEFLAG); create index ix_APLJBG_JBC56 on APLJBG(JBC56); --create index ix_MES_DC_EQPARAM_CPN_CODE on MES_DC_EQPARAM(CPN_CODE); drop index ix_MES_PM_MASDATA_INDOBJTYPE ; drop index ix_MES_PM_MASDATA_BUFFER57 ; drop index ix_MES_PM_MASdata_IND_STATUS ; drop index ix_MES_PM_MASDATA_ENABLEFLAG ; drop index ix_MES_DC_CT_EQ_CPN_CODE ; drop index ix_MES_DC_CT_EQ_REC_DATE; drop index ix_APLJBG_JBC02; drop index ix_APLJBG_JBC51; drop index ix_MES_DC_EQPARAM_IS_VALID; drop index ix_MES_DC_CT_EQ_IS_VALID; */
8、查看子句,发现sql语句问题较大
SELECT ROUND(SUM((TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24),
2) as RUNTIME_EQ,
B.Eq_Code
FROM MES_DC_CT_EQ A
INNER JOIN MES_DC_EQPARAM B
ON A.CPN_CODE = B.CPN_CODE
AND B.ENABLEFLAG = 'Y'
AND B.IS_VALID = 'Y'
WHERE A.ENABLEFLAG = 'Y'
AND A.REC_VALUE = '1'
and (TO_DATE(A.REC_DATE, 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE(A.BUFFER1, 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 < 10
AND A.BUFFER1 IS NOT NULL
AND A.CREATEDATE >=
TO_DATE('2023/8/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND A.CREATEDATE <=
TO_DATE('2023/8/31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
group by B.Eq_Code
9、查看字段
desc MES_DC_CT_EQ
>desc MES_DC_CT_EQ Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------ ROW_ID NOT NULL NVARCHAR2(50) CPN_CODE NVARCHAR2(50) CPN_DESC NVARCHAR2(200) REC_DATE NVARCHAR2(50) LAST_REC_VALUE NVARCHAR2(50) REC_VALUE NVARCHAR2(50) REC_STATUS NVARCHAR2(2) LOG_MESS NVARCHAR2(500) ENABLEFLAG NVARCHAR2(2) BUFFER1 NVARCHAR2(50) BUFFER2 NVARCHAR2(50) BUFFER3 NVARCHAR2(50) BUFFER4 NVARCHAR2(50) BUFFER5 NVARCHAR2(50) REMARK NVARCHAR2(50) CREATEBY NVARCHAR2(50) CREATEDATE DATE LASTUPDATEBY NVARCHAR2(50) LASTUPDATEDATE DATE
10、分析字段
发现,REC_DATE和buffer1是字符串类型,而程序里写入的是date类型。经过转换,则会慢
11、将表修改测试
(1)导出表及所有关联
expdp mes_prd/123 direcotry=backup_prd dumpfile=mes_dc_ct_eq.dmp logfile=mes_dc_ct_eq.log tables=mes_dc_ct_eq
(2)导入表并重命名及关联
impdp mes_prd/123 directory=backup_prd dumpfile=mes_dc_ct_eq.dmp log=mes_dc_ct_eq_imp.log tables=mes_dc_ct_eq REMAP_TABLE=mes_dc_ct_eq:mes_dc_ct_eq_2
也可以通过
获取ddl方式(dbms_metadata.get_ddL)创建表结构
或create table as select * from ...方式,数据量大时会慢;
或使用append(会快一些):
create /*+ append */ table MES_DC_CT_EQ_2 as select * from MES_DC_CT_EQ;
(3)针对导入的报错查看
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX ORA-31684: Object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_REC_DATE" already exists ORA-31684: Object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_CPN_CODE" already exists ORA-31684: Object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_ENABLEFLAG" already exists Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_ENABLEFLAG" already exists ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_CPN_CODE" already exists ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"MES_PRD"."IX_MES_DC_CT_EQ_REC_DATE" already exists
(4)报错处理,重建索引
select dbms_metadata.get_ddl(object_type => 'INDEX',name =>'IX_MES_DC_CT_EQ_REC_DATE') from dual; CREATE INDEX "MES_PRD"."IX_MES_DC_CT_EQ_REC_DATE_2" ON "MES_PRD"."MES_DC_CT_EQ_2" ("REC_DATE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MES_PRD" select dbms_metadata.get_ddl(object_type => 'INDEX',name =>'IX_MES_DC_CT_EQ_CPN_CODE') from dual; CREATE INDEX "MES_PRD"."IX_MES_DC_CT_EQ_CPN_CODE_2" ON "MES_PRD"."MES_DC_CT_EQ_2" ("CPN_CODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MES_PRD" select dbms_metadata.get_ddl(object_type => 'INDEX',name =>'IX_MES_DC_CT_EQ_ENABLEFLAG') from dual; CREATE INDEX "MES_PRD"."IX_MES_DC_CT_EQ_ENABLEFLAG_2" ON "MES_PRD"."MES_DC_CT_EQ_2" ("ENABLEFLAG") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MES_PRD"
(5)增加新列,数据类型为date
alter table MES_DC_CT_EQ_2 add REC_DATE_2 date; alter table MES_DC_CT_EQ_2 add BUFFER1_2 date;
(6)向数据列中写入数据,和rec_date一致
update MES_DC_CT_EQ_2 SET REC_DATE_2 = TO_DATE(REC_DATE, 'YYYY-MM-DD HH24:MI:SS'); update MES_DC_CT_EQ_2 SET BUFFER1_2 = TO_DATE(BUFFER1, 'YYYY-MM-DD HH24:MI:SS');
(7)更改查询语句,更改为新的列,查询时间为1秒。
(8)问题解决