第8个案例—sql优化（HN公司查询开机率慢，优化）-原因是日期字段定义为varchar2

2023年9月19日 324点热度 0人点赞 0条评论

一、原因

业务查询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）问题解决

本作品采用知识共享署名 4.0 国际许可协议进行许可