生产系统调优之_毫秒级的改进 (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 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

通用分页存储过程

/*通用分页存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WH...

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

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2414
来自专栏自由而无用的灵魂的碎碎念

oracle 10g 手动创建scott(tiger) schema

转自:http://cnhtm.itpub.net/post/39970/496967

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

mysql常用命令

这几天学习了一下mysql,对于mysql的命令总结如下,发现很多方面和oracle还是差别挺大的。 # mysql -uroot -p Enter passw...

3966
来自专栏数据和云

利用分析函数改写范围判断自关联查询

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

1264
来自专栏沃趣科技

SQL优化案例-分区索引之无前缀索引(六)

无前缀索引:分区索引不包含分区字段就叫无前缀索引,那么什么时候用无前缀索引和前缀索引呢?

1192
来自专栏乐沙弥的世界

SQL server 2005 切换分区表

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx

973
来自专栏Java学习123

ORACLE数据库日常维护

5338
来自专栏乐沙弥的世界

PL/SQL --> INSTEAD OF 触发器

INSTEAD OF 触发器常用于管理编写不可更新的视图,INSTEAD-OF触发器必须是行级的。

782
来自专栏跟着阿笨一起玩NET

winfrom如何做一个语法着色控件

本文转载:http://www.cnblogs.com/hexin0614/archive/2012/01/17/2324224.html

581

扫码关注云+社区

领取腾讯云代金券