目录
一、原因
业务查询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
（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）问题解决
