生产系统调优之_毫秒级的改进 (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没有什么差别了。