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

相关文章

来自专栏乐沙弥的世界

PL/SQL --> INSTEAD OF 触发器

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

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

生产系统调优之_敢于质疑(90天)

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后,速度有了一定的提升,从5秒的响应降低到了2秒。但是和预期还是有一定 的差距,按照80条查询请求在短...

2567
来自专栏Ryan Miao

oracle创建表相关

1 --创建表 2 create table person( 3 id number primary key, 4 name varchar2(40),...

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

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

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

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

mysql常用命令

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

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

关于物化视图疑问(32天)

--初始化操作, 创建两个用户一个,testo,一个test. 在testo上创建表,test上创建物化视图。 SQL> create user testo ...

3415
来自专栏乐沙弥的世界

SQL server 2005 切换分区表

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

893
来自专栏沃趣科技

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

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

1102
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

661
来自专栏james大数据架构

通用分页存储过程

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

2108

扫码关注云+社区