拓扑园

  • O&M
    • Universal部署
    • PHP+VUE+Laravel相关
  • Oracle性能优化
  • Oracle项目案例
    • Oracle近期项目案例(目录)
    • Oracle实战问题解析(目录)
    • Oracle数据库名变更流程(2种方式)
    • Oracle数据库目录更换流程(使用Oracle的clone工具)
    • Oracle数据库迁移方案(目录)
    • 标准化文档系列
  • Oracle基础知识
    • LLL的Oracle培训(分类)
    • LLL的docker培训(分类)
    • 标准化文档系列--(分类)
    • Oracle核心经典分析(分类)
    • 图灵小队----(分类并包含以下文章)
    • --MySQL8.0/Oracle/Memcached/Redis等安装配置于RHEL/OL6/7/8.X系列-运行环境最优配置
    • --PG安装配置于RHEL/9X系列-运行环境最优配置
    • --自动维护任务详解-开启、关闭信息统计收集(统计信息)
    • --图灵小队—Oracle/PostgreSQL下创建一个用户测试表(自行定义数据行)
    • --图灵小队-Oracle存储过程导出表的明细_UTL_FILE(文章)
    • --图灵小队-Oracle数据库删除/卸载操作指南(文章)
    • --图灵小队-Oracle常用性能查询SQL语句(文章)
    • --图灵小队-Oracle数据库上线前检查(文章)
    • --图灵小队-Oracle常用SQL语句(文章)
    • --图灵小队—Linux/Oracle脚本/MySQL合集(持续更新)
    • --图灵小队-Oracle技巧记录(文章)
    • ADG
    • RAC
    • ASM
    • OGG
    • RMAN
    • EXPDP/IMPDP
    • 工厂数据导入导出系列
  • MySQL
    • MySQL数据库规范
    • MySQL项目案例
    • MySQL安装配置
    • MYSQL集群项目
    • MySQL常见处理
    • MySQL-Sysbench专题
    • MySQL-Percona Toolkit专题
  • Linux
    • Shell编程
    • kubernetes
    • docker
    • Linux
    • PHP
    • Nginx
    • haproxy
    • mail
    • 网站
    • 域名
    • 网址收藏
  • 数据中心
    • 新框架系统集合
    • 工作文档
    • EBS数据文件扩容
    • VMware虚拟化
    • EBS系列
    • 大数据
    • SVN
    • zabbix
    • SAP
    • 备份相关
    • FC交换机
    • SVN
  • K-Studing
    • D8-Python学习
    • Oracle/MySQl等面试题
    • LG-MySQL
    • LG-Docker/K8S
    • LG-PostgreSQL
    • LG-ORACLE_BBED
    • LG-ORACLE
    • LG-Elasticsearch(ES)+ELK
    • Oracle-19C-OCP
    • WERN_ORACLE培训
    • redis数据库
    • Nginx培训学习系列
  • 其他
    • 外研英语4年级下册-听力
    • 影视系列
    • 如何使用iTunes软件通过抓包下载旧版本的ios的app
天高任鸟飞
Oracle/MySQL数据库恢复/数据迁移/生产规范报告技术交流:TEL:18562510581(微信同号);加微信入群
  1. 首页
  2. Oracle项目案例
  3. Oracle实战问题解析
  4. 正文

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

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

目录

  • 一、公司反馈记工查询超时
    • 1、记工查询超时现象
    • 2、根据SQL查看如下
  • 二、跟踪SQL语句
    • 1、获取SQL语句
    • 2、获取语句:
  • 三、SQL语句优化
    • 1、通过sql优化建议查看
    • 2、通过查看时间比较字段
      • (1)创建新表MES_PRD_JIGONG_2
      • (2)增加数据列
      • (3)更新数据列
      • (4)创建列数据的索引
      • (5)表的信息统计收集
      • (6)将查询语句进行更改:
      • (7)查询结果对比:

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

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 国际许可协议 进行许可
标签: 暂无
最后更新:2023年10月8日

admin

这个人很懒,什么都没留下

打赏 点赞
< 上一篇
下一篇 >

COPYRIGHT © 2022 拓扑园. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

鲁ICP备2021020523号

鲁ICP备2021020523号