生產系統調優之_毫秒級的改進
生產中有一個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沒有什麼差別了。
因為表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沒有什麼差別了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1174783/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 生產系統調優之_敢於質疑
- 生產系統pl/sql調優案例SQL
- 生產sql調優之統計資訊分析SQL
- 服裝生產管理軟體鞋帽生產系統的優點
- 產品的生態系統
- Openssh-7.9p1升級+系統調優
- 生產系統下，RMAN的2級備份。以及恢復
- Linux系統效能調優之效能分析Linux
- 生產經營管理之表帳單系統
- 張馳諮詢：精益生產培訓延續改進生產流程的7個方法！
- 圍繞央行系統升級所產生的常見問題
- 按照oracle效能改進方法論的步驟來優化系統！Oracle優化
- Linux優化之IO子系統監控與調優Linux優化
- 【話題討論】漫談生產系統升級的一點思考
- Linux系統調優Linux
- 馬司系統調優
- 說說生產系統索引的重建索引
- 杭州ERP生產管理系統開發的應用與優勢
- 需求改進&系統設計
- 生產進度管理系統提高企業員工的工作效率
- oracle優化之生產系統不改程式碼解決SQL效能問題的幾種方法Oracle優化SQL
- 【sql調優】系統資訊統計SQL
- 【拖雷】Taobao監控系統之改進——檔案傳輸
- 生產服務GC調優實踐基本流程總結GC
- 談ERP系統的生產排程
- 關於生產管理系統中的BOM!
- 旨在改進威脅檢測的系統
- Oracle 11g系統調優之dbms_sqltune包的使用OracleSQL
- 需求改進與系統設計
- 電影票務管理系統改進
- （3）Linux效能調優之Linux檔案系統Linux
- SuperAwesome：兒童遊戲生態系統是如何改變的遊戲
- Solaris如何改變系統執行級
- 對軟體專案中產生的需求進行分級管理
- 關於生產系統鎖問題的排查
- 新的系統是怎樣產生的？（軟工系列文章之六） (轉)軟工
- APS高階計劃排程系統和生產排產系統
- Linux系統調優介紹Linux