生产系统调优之_毫秒级的改进 (92天)

生产中有一个sql语句,做了union-all操作,对于时间的要求是极其严格的,目前已经从2秒的改进调整到了1秒以内,在此基础上还想做进一步的调整,因为极其频繁的查询,如果一丁点的改进都会在时间上的飞跃,以下的sql语句目前时间控制在不到半秒的样子。 因为表SMALL_OFFER_PARAM 是一个数据字典表,查询的字段上没有相关的索引。目前采用了exisits来做关联。 SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SB.CUSTOMER_ID, SB.SUB_STATUS, SB.BUSINESS_ENTITY_ID, SB.LANGUAGE, SB.ROUTING_POLICY_ID, SB.L9_PORT_IND, SB.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER SB, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SB.SUBSCRIBER_NO = AR.AGREEMENT_NO AND EXISTS (select /*+ INDEX(OP SMALL_OFFER_PARAM_1IX) */ 1 from SMALL_OFFER_PARAM OP where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) AND GUIDING_IND = 'Y' ) AND SB.CUSTOMER_ID = CS.CUSTOMER_ID AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SB.EXPIRATION_DATE OR SB.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE) AND SB.SUB_STATUS != 'T' UNION ALL SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SH.CUSTOMER_ID, SH.SUB_STATUS, SH.BUSINESS_ENTITY_ID, SH.LANGUAGE, SH.ROUTING_POLICY_ID, SH.L9_PORT_IND, SH.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER_HISTORY SH, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SH.SUBSCRIBER_NO = AR.AGREEMENT_NO AND EXISTS (select /*+ INDEX(OP SMALL_OFFER_PARAM_1IX) */ 1 from SMALL_OFFER_PARAM OP where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) AND GUIDING_IND = 'Y') AND SH.CUSTOMER_ID = CS.CUSTOMER_ID AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SH.EXPIRATION_DATE OR SH.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE) AND SH.SUB_STATUS NOT IN ('C', 'T') 查询稳定下来,资源情况如下: Elapsed: 00:00:00.12 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5724 consistent gets 0 physical reads 0 redo size 1900 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed 在此基础上做了进一步的改进,采用了result_cache来做数据的缓存,使得后面的查询能够复用之前的结果集。 逻辑度有了一定的提高。时间从0.12降低到了0.08s,看似没有多大的改进。如果细细算一下。 如果0.12秒,8万条连续的查询将会持续约2.67个小时。 如果0.08秒,8万条连续的查询将会持续约1.78个小时。 SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SB.CUSTOMER_ID, SB.SUB_STATUS, SB.BUSINESS_ENTITY_ID, SB.LANGUAGE, SB.ROUTING_POLICY_ID, SB.L9_PORT_IND, SB.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER SB, (select /*+ RESULT_CACHE */ DISTINCT PARAM_NAME as PARAM_NAME from SMALL_OFFER_PARAM where GUIDING_IND = 'Y') OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SB.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SB.CUSTOMER_ID = CS.CUSTOMER_ID AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SB.EXPIRATION_DATE OR SB.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE) AND SB.SUB_STATUS != 'T' UNION ALL SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SH.CUSTOMER_ID, SH.SUB_STATUS, SH.BUSINESS_ENTITY_ID, SH.LANGUAGE, SH.ROUTING_POLICY_ID, SH.L9_PORT_IND, SH.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER_HISTORY SH, (select /*+ RESULT_CACHE */ DISTINCT PARAM_NAME as PARAM_NAME from SMALL_OFFER_PARAM where GUIDING_IND = 'Y') OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SH.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SH.CUSTOMER_ID = CS.CUSTOMER_ID AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SH.EXPIRATION_DATE OR SH.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE) AND SH.SUB_STATUS NOT IN ('C', 'T') 使用资源情况如下: Elapsed: 00:00:00.08 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5156 consistent gets 0 physical reads 0 redo size 1900 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed 当然了,再次基础上更近一步。使用with子句 with op as ( select /*+ RESULT_CACHE */ DISTINCT PARAM_NAME as PARAM_NAME from SMALL_OFFER_PARAM where GUIDING_IND = 'Y' ) SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SB.CUSTOMER_ID, SB.SUB_STATUS, SB.BUSINESS_ENTITY_ID, SB.LANGUAGE, SB.ROUTING_POLICY_ID, SB.L9_PORT_IND, SB.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER SB, OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SB.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SB.CUSTOMER_ID = CS.CUSTOMER_ID AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SB.EXPIRATION_DATE OR SB.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE) AND SB.SUB_STATUS != 'T' UNION ALL SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SH.CUSTOMER_ID, SH.SUB_STATUS, SH.BUSINESS_ENTITY_ID, SH.LANGUAGE, SH.ROUTING_POLICY_ID, SH.L9_PORT_IND, SH.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER_HISTORY SH, OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SH.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SH.CUSTOMER_ID = CS.CUSTOMER_ID AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SH.EXPIRATION_DATE OR SH.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE) AND SH.SUB_STATUS NOT IN ('C', 'T') 查询稳定以后,资源使用情况如下,响应时间一下子降低到了0.03,相比0.12s提高了4倍。逻辑读降低从5000多降低到了1122左右。 不过每次会有一个物理读。 Elapsed: 00:00:00.03 Statistics ---------------------------------------------------------- 2 recursive calls 4 db block gets 1122 consistent gets 1 physical reads 0 redo size 1900 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed 在这种情况下,使用result-cache的hint,就没有什么主要的提升了。 with op as ( select DISTINCT PARAM_NAME as PARAM_NAME from SMALL_OFFER_PARAM where GUIDING_IND = 'Y' ) SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SB.CUSTOMER_ID, SB.SUB_STATUS, SB.BUSINESS_ENTITY_ID, SB.LANGUAGE, SB.ROUTING_POLICY_ID, SB.L9_PORT_IND, SB.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER SB, OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SB.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SB.CUSTOMER_ID = CS.CUSTOMER_ID AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SB.EXPIRATION_DATE OR SB.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE) AND SB.SUB_STATUS != 'T' UNION ALL SELECT DISTINCT 'K', AR.RESOURCE_VALUE, AR.RESOURCE_TYPE, GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')), LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000'), NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'), '47001231000000')), AR.AGREEMENT_NO, SH.CUSTOMER_ID, SH.SUB_STATUS, SH.BUSINESS_ENTITY_ID, SH.LANGUAGE, SH.ROUTING_POLICY_ID, SH.L9_PORT_IND, SH.L9_SPLIT_PERIOD FROM HUGE_RESOURCE AR, SUBSCRIBER_HISTORY SH, OP, CUSTOMER CS WHERE AR.AGREEMENT_NO = 1056851 AND AR.AGREEMENT_KEY = MOD(1056851, 100) AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL) AND AR.RANGE_IND = 'N' AND SH.SUBSCRIBER_NO = AR.AGREEMENT_NO AND (OP.PARAM_NAME = AR.RESOURCE_PRM_CD or OP.PARAM_NAME = AR.BASE_PARAM_NAME) AND SH.CUSTOMER_ID = CS.CUSTOMER_ID AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS')) AND (AR.EFFECTIVE_DATE < SH.EXPIRATION_DATE OR SH.EXPIRATION_DATE IS NULL) AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE) AND SH.SUB_STATUS NOT IN ('C', 'T') Elapsed: 00:00:00.03 Statistics ---------------------------------------------------------- 2 recursive calls 4 db block gets 1122 consistent gets 1 physical reads 0 redo size 1900 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed 测试结果和加了hint没有什么差别了。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-06-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

2716
来自专栏CreateAMind

https://github.com/CPFL/Autoware 自动驾驶框架比较齐全

Integrated open-source software for urban autonomous driving, maintained by Tier...

1822
来自专栏乐沙弥的世界

检查及设置合理的undo表空间

      UNDO是用于实现并发控制以及构建一致性读,也就是在数据变更之前产生前镜像,以保证用户能够回滚或撤销对数据库所作的修改。是Oracle数据库完整性的...

692
来自专栏我爱编程

利用cairo画Fractal Tree

1763
来自专栏数据和云

深入分析:12C ASM Normal冗余中PDB文件块号与AU关系与恢复

在 10G 和 11G 中,DBA 可以根据文件名,确定这个文件在 ASM 磁盘组上的分布,然后 dd 出来每一个 AU,最后拼凑成一个完成的数据文件。

1034
来自专栏杨建荣的学习笔记

海量数据迁移之外部表加载(100天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

3178
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度...

3288
来自专栏杨建荣的学习笔记

关于consistent gets(r5笔记第12天)

在sql调优的时候,一个关键指标就是consistent gets,如果这个指标很低,一般认为sql语句执行还是很高效的,反之效率会很低。但是这个指标我们知之甚...

3215
来自专栏乐沙弥的世界

Oracle Scheduler Chain 的用法

      Oracle CHAIN,也就是链,是将一串需要完成的作业连在一起,根据每一个步骤完成的不同结果来确定下面的哪一个动作需要被完成。实际上也就是if ...

631
来自专栏数据和云

RMAN CATALOG命令手动注册磁带库中的备份片

ORACLE 官方文档中介绍 CATALOG 命令只能注册在磁盘中的备份片,在现在多数环境中备份时,备份集都是放到磁带库中,那么 CATALOG 命令真就不支持...

1021

扫码关注云+社区