第9个案例—WuHanDY工厂记工查询超时（原因是时间字段为varchar2类型）

2023年10月7日 837点热度 1人点赞 0条评论

一、公司反馈记工查询超时
- 1、记工查询超时现象
- 2、根据SQL查看如下
二、跟踪SQL语句
- 1、获取SQL语句
- 2、获取语句：
三、SQL语句优化
- 1、通过sql优化建议查看
- 2、通过查看时间比较字段

一、公司反馈记工查询超时

1、记工查询超时现象

记工查询超时，通过检查发现查询几天的可以查出，但查询一个月的会报错。

通过修改应用端时间参数time_out为1500s，仍然报错：ORA_00103。

2、根据SQL查看如下

最终报错原因是执行sql语句时间过长导致，查询一个月的数据需要34-40分钟。

二、跟踪SQL语句

1、获取SQL语句

-https://www.topunix.com/post-1782.html#SQL-2

2、获取语句：

SELECT 
ttt.车间上浮,
ttt.工序, 
ttt.工序名称, 
ttt.工作中心, ttt.操作人, 
SUM(ttt.支数) AS 支数, 
SUM(ttt.记工数) AS 记工数, 
SUM(ttt.机器标准工时) AS 机器标准工时,
SUM(ttt.人工标准工时) AS 人工标准工时, 
SUM(ttt.折合工时) AS 折合工时, 
SUM(NVL(ttt.正式系数, 0)) AS 正式系数, 
SUM(ttt.内返系数) 内返系数, 
SUM(ttt.工资工时) AS 工资工时, 
ROUND(SUM(面积), 2) AS 面积, ttt.工号
FROM (SELECT jg.cjsf AS 车间上浮, jg.buffer11 AS 工序,
DEPT.DEPTNAME AS 工序名称, wc.stext AS 工作中心, jg.per_name AS 操作人, jg.buffer19,
CASE WHEN JG.BUFFER11 IN ('BO03','') 
THEN ROUND(MAX(ban.zhishu))
ELSE ROUND(SUM(jg.jgs), 3)
END AS 支数,
ROUND(SUM(jg.total), 2) AS 记工数, ROUND(AVG(jg.buffer13), 2) AS 机器标准工时, 
ROUND(AVG(jg.buffer14), 2) AS 人工标准工时, ROUND(SUM(jg.ofm02), 2) AS 折合工时,
ROUND(SUM(po2.系数) - (MIN(po2.系数) * NVL(MAX(NFXS.NFZS), 0)), 2) 正式系数,
ROUND(NVL((SUM(NFXS.系数) / SUM(jg.buffer16 * jg.total) * MAX(NFXS.NFZS)), 0), 2) 内返系数,
ROUND(SUM(jg.buffer3), 2) AS 工资工时, 
ROUND(SUM(BAN.Banchang * ban.zhouchang / 1000000), 2) AS 面积,
jg.per_code AS 工号
FROM V_mes_prd_jigong jg
LEFT JOIN MES_BAN_STATUS BAN ON JG.BUFFER19 = BAN.BANHAO
LEFT JOIN mes_dept dept ON jG.buffer11 = dept.deptid AND dept.enableflag = 'Y'
INNER JOIN mes_wcmappingdept d ON jg.buffer11 = d.deptcode AND d.enableflag = 'Y'
INNER JOIN mes_workcenterlang wc ON wc.head_id = d.wccode AND wc.enableflag = 'Y'
LEFT JOIN (

SELECT count(*),PRD.BANHAO, PSB1.BUFFER15, PSB1.BUFFER21,
CASE
WHEN sadept.code1 = '00801' THEN TO_NUMBER(MAX(PRD.huagaoratio)) / TO_NUMBER(MIN(BAN.ZHISHU))
WHEN sadept.code1 = '00803' THEN TO_NUMBER(MAX(PRD.huagaoratio)) / TO_NUMBER(MIN(BAN.ZHISHU))
WHEN sadept.code1 = '00807' THEN TO_NUMBER(MAX(PRD.huagaoratio))
WHEN sadept.code1 = '00802' THEN TO_NUMBER(MAX(PRD.tuxiangratio)) / TO_NUMBER(MIN(BAN.ZHISHU))
WHEN sadept.code1 = '00804' THEN TO_NUMBER(MAX(PRD.tuxiangratio)) / TO_NUMBER(MIN(BAN.ZHISHU))
WHEN sadept.code1 = '00806' THEN TO_NUMBER(SUM(PRD.tuxiangratio))
WHEN sadept.code1 = '00805' THEN TO_NUMBER(MAX(PRD.pjratio)) / TO_NUMBER(MIN(BAN.ZHISHU))
ELSE 0
END AS 系数
FROM MES_PRD_ORDER PRD
LEFT JOIN V_mes_prdsubmit_ITEM psb1 ON psb1.Prdrowid = PRD.row_id AND psb1.enableflag = 'Y'
LEFT JOIN sa_department sadept ON sadept.code2 = psb1.buffer15
LEFT JOIN MES_BAN_STATUS BAN ON PRD.BANHAO = BAN.BANHAO
LEFT JOIN mes_so_main somain ON somain.banhao = PRD.BANHAO
WHERE PRD.ENABLEFLAG = 'Y' AND PRD.PRD_TYPE != 'DD16'
AND PSB1.BUFFER15 IN ('0201','020101','020109','020104','BO01','020106','BO03','020105','BO02','020102','BJ01','020103','BJ02')
AND psb1.buffer10 >= '2023-09-01 00:00:00' AND psb1.buffer10 <= '2023-09-26 23:59:59'
GROUP BY PRD.BANHAO, psb1.buffer15, PSB1.BUFFER21, sadept.code1
) PO2 
ON JG.BUFFER11 = PO2.BUFFER15 AND JG.BUFFER19 = PO2.BANHAO AND JG.per_name = PO2.BUFFER21
LEFT JOIN (
SELECT count(*),PRD.BANHAO, PSB1.BUFFER15, COUNT(prd.banhao) AS NFZS, PSB1.BUFFER21,
CASE
WHEN sadept.code1 = '00801' THEN TO_NUMBER(MAX(PRD.huagaoratio)) / COUNT(prd.banhao)
WHEN sadept.code1 = '00803' THEN TO_NUMBER(MAX(PRD.huagaoratio)) / COUNT(prd.banhao)
WHEN sadept.code1 = '00807' THEN TO_NUMBER(MAX(PRD.huagaoratio))
WHEN sadept.code1 = '00802' THEN TO_NUMBER(MAX(PRD.tuxiangratio)) / COUNT(prd.banhao)
WHEN sadept.code1 = '00804' THEN TO_NUMBER(MAX(PRD.tuxiangratio)) / COUNT(prd.banhao)
WHEN sadept.code1 = '00806' THEN TO_NUMBER(SUM(PRD.tuxiangratio))
WHEN sadept.code1 = '00805' THEN TO_NUMBER(MAX(PRD.pjratio)) / COUNT(prd.banhao)
ELSE 0
END AS 系数
FROM MES_PRD_ORDER PRD
LEFT JOIN V_mes_prdsubmit_ITEM psb1 ON psb1.Prdrowid = PRD.row_id AND psb1.enableflag = 'Y'
LEFT JOIN sa_department sadept ON sadept.code2 = psb1.buffer15
LEFT JOIN mes_so_main somain ON somain.banhao = PRD.BANHAO
WHERE PRD.ENABLEFLAG = 'Y' AND PRD.PRD_TYPE = 'DD16'
AND PSB1.BUFFER15 IN ('0201','020101','020109','020104','BO01','020106','BO03','020105','BO02','020102','BJ01','020103','BJ02')
AND psb1.buffer10 >= '2023-09-01 00:00:00' AND psb1.buffer10 <= '2023-09-26 23:59:59'
GROUP BY PRD.BANHAO, psb1.buffer15, psb1.BUFFER21, sadept.code1
) NFXS 
ON JG.BUFFER11 = NFXS.BUFFER15 AND JG.BUFFER19 = NFXS.BANHAO AND JG.per_name = NFXS.BUFFER21
WHERE jg.enableflag = 'Y' AND jg.buffer9 = 'S'
AND jg.buffer11 IN ('0201','020101','020109','020104','BO01','020106','BO03','020105','BO02','020102','BJ01','020103','BJ02') 
AND jg.buffer21 >= '2023-09-18 00:00:00' AND jg.buffer21 <= '2023-09-26 23:59:59'
GROUP BY jg.per_name, jg.per_code, jg.cjsf, jg.buffer11, DEPT.DEPTNAME, wc.stext, jg.buffer19
) ttt
GROUP BY ttt.车间上浮, ttt.工序, ttt.工序名称, ttt.工作中心, ttt.操作人, ttt.工号

三、SQL语句优化

1、通过sql优化建议查看

select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;

根据Oracle优化建议，做并行查询以及创建索引并不能显著提升查询性能。

2、通过查看时间比较字段

buffer21是字符串类型，但实际数据却是date类型，对表结构进行修改。

（1）创建新表MES_PRD_JIGONG_2

create /*+ append */ table MES_PRD_JIGONG_2 as select * from MES_PRD_JIGONG;

（2）增加数据列

alter table MES_PRD_JIGONG_2 add buffer21_2 date;

（3）更新数据列

update MES_PRD_JIGONG_2 SET buffer21_2 = TO_DATE(buffer21, 'YYYY-MM-DD HH24:MI:SS');

（4）创建列数据的索引

create index idx_buffer21_2 on MES_PRD_JIGONG_2(buffer21_2);

（5）表的信息统计收集

begin
dbms_stats.gather_table_stats(ownname => 'MES_PRD',
tabname => 'MES_PRD_JIGONG_2');
end;
begin
dbms_stats.gather_index_stats(ownname => 'MES_PRD',
indname => 'idx_buffer21_2');
end;

（6）将查询语句进行更改：

 jg.buffer21 >= '2023-09-12 00:00:00' AND jg.buffer21 <= '2023-09-25 23:59:59'

改为：

jg.buffer21_2 >= to_date('2023-09-23 00:00:00','YYYY-MM-DD hh24:mi:ss') 
AND 
jg.buffer21_2 <= to_date('2023-09-25 23:59:59','YYYY-MM-DD hh24:mi:ss')

（7）查询结果对比：

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