對自定義函式使用不當的調優案例
一、問題。
下面這條SQL的執行時間較長，不滿足業務需求。
select rs.managecom,
(select codename
from ldcode
where codetype = 'station'
and code = rs.managecom),
rs.insurername,
rs.contplancode,
case
when sum(prem) - sum(prem2) + sum(prem3) = 0 then
''
else
case
when substr(round((sum(rs.pay) + sum(rs.pay2)) /
(sum(prem) - sum(prem2) + sum(prem3)),
4) * 100,
0,
1) = '.' then
'0' || round((sum(rs.pay) + sum(rs.pay2)) /
(sum(prem) - sum(prem2) + sum(prem3)),
4) * 100 || '%'
else
round((sum(rs.pay) + sum(rs.pay2)) /
(sum(prem) - sum(prem2) + sum(prem3)),
4) * 100 || '%'
end
end,
sum(rs.pay),
sum(rs.pay2),
round(sum(prem), 2) - round(sum(prem2), 2) + round(sum(prem3), 2),
round(sum(prem), 2),
round(sum(prem2), 2),
round(sum(prem3), 2)
from (select g.managecom,
g.insurername,
(SELECT LN.CONTPLANNAME
FROM LCContPlan LN
WHERE LN.CONTPLANCODE = d.CONTPLANCODE
AND LN.GrpContno = d.GrpContno
AND ROWNUM = 1) contplancode,
d.insuredno,
nvl((select sum(a.realpay)
from llclaimdetail a, llclaim b
where a.clmno = b.clmno
and a.grpcontno = c.grpcontno
and a.customerno = d.insuredno
and a.givetype != '1'
and b.clmstate = '60'
and b.endcasedate >= date
'2015-01-01'
and b.endcasedate <= date '2015-11-30'),
0) pay,
nvl((select sum(d.realpay)
from LLClaimPolicyBak d, llclaim b
where d.grpcontno = c.grpcontno
and d.clmno = b.clmno
and d.insuredno = c.insuredno
and (d.clmstate not in ('60', '70', '100') or
d.clmstate is null)
and (b.endcasedate is null or b.endcasedate > date
'2015-11-30')
and d.makedate <= date
'2015-11-30'
and d.seqno =
(select max(seqno)
from LLClaimPolicyBak dd, llclaim bb
where dd.grpcontno = c.grpcontno
and dd.clmno = d.clmno
and dd.clmno = bb.clmno
and d.riskcode = dd.riskcode
and not exists
(select 'X'
from llclaimdetailb
where llclaimdetailb.clmno = dd.clmno
and llclaimdetailb.riskcode =
dd.riskcode
and llclaimdetailb.makedate =
dd.makedate)
and dd.makedate <= date
'2015-11-30'
and (dd.clmstate not in ('60', '70', '100') or
dd.clmstate is null)
and (bb.endcasedate is null or
bb.endcasedate > date '2015-11-30'))),
0) -
nvl((select sum(d.realpay)
from LLClaimPolicyBak d, llclaim b
where d.grpcontno = c.grpcontno
and d.clmno = b.clmno
and d.insuredno = c.insuredno
and (d.clmstate not in ('60', '70', '100') or
d.clmstate is null)
and (b.endcasedate is null or b.endcasedate > date
'2015-01-01' - 1)
and d.makedate <= date
'2015-01-01' - 1
and d.seqno =
(select max(seqno)
from LLClaimPolicyBak dd, llclaim bb
where dd.grpcontno = c.grpcontno
and dd.clmno = d.clmno
and dd.clmno = bb.clmno
and not exists
(select 'X'
from llclaimdetailb
where llclaimdetailb.clmno = dd.clmno
and llclaimdetailb.riskcode =
dd.riskcode
and llclaimdetailb.makedate =
dd.makedate)
and d.riskcode = dd.riskcode
and dd.makedate <= date
'2015-01-01' - 1
and (dd.clmstate not in ('60', '70', '100') or
dd.clmstate is null)
and (bb.endcasedate is null or
bb.endcasedate > date '2015-01-01' - 1))),
0) pay2,
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
1) prem,
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
2) prem2,
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
3) prem3
from lccont c, lcinsured d, lcgrpcont g
where c.grpcontno = d.grpcontno
and c.contno = d.contno
and c.insuredno = d.insuredno
and c.grpcontno = g.grpcontno
and g.appflag = '1'
and ((g.cvalidate >= date '2015-01-01' and g.cvalidate <= date
'2015-11-30') or
(g.insurprotocoldate >= date
'2015-01-01' and g.insurprotocoldate <= date '2015-11-30') or
(g.cvalidate <= date
'2015-01-01' and g.insurprotocoldate >= date '2015-11-30'))
and c.managecom like '86%'
and g.conttype = '2') rs
group by rs.managecom, rs.insurername, rs.contplancode;
以下為該SQL的執行計劃和統計資訊：
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:03:41.72 | 21M| 6 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | LDCODE | 2 | 1 | 2 |00:00:00.01 | 6 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_LDCODE | 2 | 1 | 2 |00:00:00.01 | 4 | 0 | | | |
|* 3 | COUNT STOPKEY | | 4800 | | 4800 |00:00:00.09 | 9600 | 0 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | LCCONTPLAN | 4800 | 1 | 4800 |00:00:00.08 | 9600 | 0 | | | |
|* 5 | INDEX RANGE SCAN | PK_LCCONTPLAN | 4800 | 1 | 4800 |00:00:00.05 | 4800 | 0 | | | |
| 6 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:02.22 | 140K| 1 | | | |
| 7 | NESTED LOOPS | | 60166 | 1 | 18122 |00:00:02.02 | 140K| 1 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAIL | 60166 | 1 | 24885 |00:00:01.44 | 90493 | 1 | | | |
|* 9 | INDEX RANGE SCAN | IDX_LLCLAIMDETAIL_CSTMNO | 60166 | 2 | 35213 |00:00:00.83 | 60776 | 1 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 24885 | 1 | 18122 |00:00:00.48 | 49777 | 0 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_LLCLAIM | 24885 | 1 | 24885 |00:00:00.20 | 24889 | 0 | | | |
| 12 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:09.15 | 1313K| 5 | | | |
|* 13 | FILTER | | 60166 | | 2425 |00:00:09.01 | 1313K| 5 | | | |
| 14 | NESTED LOOPS | | 60166 | 3 | 16062 |00:00:06.71 | 1120K| 5 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 60166 | 3 | 460K|00:00:03.55 | 199K| 5 | | | |
|* 16 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_INSUREDNO | 60166 | 117 | 598K|00:00:01.36 | 122K| 1 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 460K| 1 | 16062 |00:00:02.58 | 920K| 0 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_LLCLAIM | 460K| 1 | 460K|00:00:01.43 | 460K| 0 | | | |
| 19 | SORT AGGREGATE | | 12397 | 1 | 12397 |00:00:02.21 | 192K| 0 | | | |
| 20 | NESTED LOOPS ANTI | | 12397 | 1 | 104K|00:00:02.08 | 192K| 0 | | | |
| 21 | NESTED LOOPS | | 12397 | 1 | 143K|00:00:01.66 | 142K| 0 | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 12397 | 1 | 12397 |00:00:00.09 | 24796 | 0 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_LLCLAIM | 12397 | 1 | 12397 |00:00:00.05 | 12399 | 0 | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 12397 | 1 | 143K|00:00:01.55 | 117K| 0 | | | |
|* 25 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_CLMNO | 12397 | 1 | 1110K|00:00:00.11 | 29945 | 0 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAILB | 16011 | 1 | 2839 |00:00:00.28 | 50149 | 0 | | | |
|* 27 | INDEX RANGE SCAN | PK_LLCLAIMDETAILB | 16011 | 2 | 50957 |00:00:00.15 | 32849 | 0 | | | |
| 28 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:01.40 | 199K| 0 | | | |
|* 29 | FILTER | | 60166 | | 0 |00:00:01.29 | 199K| 0 | | | |
| 30 | NESTED LOOPS | | 60166 | 1 | 0 |00:00:01.24 | 199K| 0 | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 60166 | 1 | 0 |00:00:01.19 | 199K| 0 | | | |
|* 32 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_INSUREDNO | 60166 | 117 | 598K|00:00:00.39 | 122K| 0 | | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | PK_LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 35 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 36 | NESTED LOOPS ANTI | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 37 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 38 | TABLE ACCESS BY INDEX ROWID| LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 39 | INDEX UNIQUE SCAN | PK_LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID| LLCLAIMPOLICYBAK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 41 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_CLMNO | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAILB | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 43 | INDEX RANGE SCAN | PK_LLCLAIMDETAILB | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
| 44 | HASH GROUP BY | | 1 | 5 | 13 |00:03:41.72 | 21M| 6 | 728K| 728K| 989K (0)|
| 45 | VIEW | | 1 | 2233 | 60166 |00:03:41.17 | 21M| 6 | | | |
|* 46 | HASH JOIN | | 1 | 2233 | 60166 |00:00:03.30 | 7461 | 0 | 820K| 820K| 1183K (0)|
|* 47 | TABLE ACCESS FULL | LCGRPCONT | 1 | 48 | 48 |00:00:00.01 | 16 | 0 | | | |
|* 48 | HASH JOIN | | 1 | 2256 | 65410 |00:00:03.14 | 7445 | 0 | 5933K| 2261K| 6542K (0)|
|* 49 | TABLE ACCESS FULL | LCCONT | 1 | 49349 | 65410 |00:00:02.71 | 4163 | 0 | | | |
| 50 | TABLE ACCESS FULL | LCINSURED | 1 | 65400 | 65410 |00:00:00.07 | 3282 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODETYPE"='station' AND "CODE"=:B1)
3 - filter(ROWNUM=1)
5 - access("LN"."GRPCONTNO"=:B1 AND "LN"."CONTPLANCODE"=:B2)
8 - filter(("A"."GRPCONTNO"=:B1 AND "A"."GIVETYPE"<>'1'))
9 - access("A"."CUSTOMERNO"=:B1)
10 - filter(("B"."ENDCASEDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."CLMSTATE"='60' AND "B"."ENDCASEDATE"<=TO_DATE(' 2015-11-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
11 - access("A"."CLMNO"="B"."CLMNO")
13 - filter("D"."SEQNO"=)
15 - filter(("D"."GRPCONTNO"=:B1 AND "D"."MAKEDATE"<=TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("D"."CLMSTATE" IS NULL OR
("D"."CLMSTATE"<>'60' AND "D"."CLMSTATE"<>'70' AND "D"."CLMSTATE"<>'100'))))
16 - access("D"."INSUREDNO"=:B1)
17 - filter(("B"."ENDCASEDATE" IS NULL OR "B"."ENDCASEDATE">TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
18 - access("D"."CLMNO"="B"."CLMNO")
22 - filter(("BB"."ENDCASEDATE" IS NULL OR "BB"."ENDCASEDATE">TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
23 - access("BB"."CLMNO"=:B1)
24 - filter(("DD"."GRPCONTNO"=:B1 AND "DD"."RISKCODE"=:B2 AND "DD"."MAKEDATE"<=TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("DD"."CLMSTATE"
IS NULL OR ("DD"."CLMSTATE"<>'60' AND "DD"."CLMSTATE"<>'70' AND "DD"."CLMSTATE"<>'100'))))
25 - access("DD"."CLMNO"="BB"."CLMNO")
filter("DD"."CLMNO"=:B1)
26 - filter(("LLCLAIMDETAILB"."RISKCODE"=:B1 AND "LLCLAIMDETAILB"."MAKEDATE"<=TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"LLCLAIMDETAILB"."RISKCODE"="DD"."RISKCODE" AND "LLCLAIMDETAILB"."MAKEDATE"="DD"."MAKEDATE"))
27 - access("LLCLAIMDETAILB"."CLMNO"=:B1)
filter("LLCLAIMDETAILB"."CLMNO"="DD"."CLMNO")
29 - filter("D"."SEQNO"=)
31 - filter(("D"."MAKEDATE"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."GRPCONTNO"=:B1 AND ("D"."CLMSTATE" IS NULL OR
("D"."CLMSTATE"<>'60' AND "D"."CLMSTATE"<>'70' AND "D"."CLMSTATE"<>'100'))))
32 - access("D"."INSUREDNO"=:B1)
33 - filter(("B"."ENDCASEDATE">TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "B"."ENDCASEDATE" IS NULL))
34 - access("D"."CLMNO"="B"."CLMNO")
38 - filter(("BB"."ENDCASEDATE">TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "BB"."ENDCASEDATE" IS NULL))
39 - access("BB"."CLMNO"=:B1)
40 - filter(("DD"."MAKEDATE"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DD"."GRPCONTNO"=:B1 AND "DD"."RISKCODE"=:B2 AND ("DD"."CLMSTATE"
IS NULL OR ("DD"."CLMSTATE"<>'60' AND "DD"."CLMSTATE"<>'70' AND "DD"."CLMSTATE"<>'100'))))
41 - access("DD"."CLMNO"="BB"."CLMNO")
filter("DD"."CLMNO"=:B1)
42 - filter(("LLCLAIMDETAILB"."RISKCODE"=:B1 AND "LLCLAIMDETAILB"."MAKEDATE"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"LLCLAIMDETAILB"."RISKCODE"="DD"."RISKCODE" AND "LLCLAIMDETAILB"."MAKEDATE"="DD"."MAKEDATE"))
43 - access("LLCLAIMDETAILB"."CLMNO"=:B1)
filter("LLCLAIMDETAILB"."CLMNO"="DD"."CLMNO")
46 - access("C"."GRPCONTNO"="G"."GRPCONTNO")
47 - filter(("G"."CONTTYPE"='2' AND "G"."APPFLAG"='1' AND (("G"."INSURPROTOCOLDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"G"."INSURPROTOCOLDATE"<=TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR ("G"."CVALIDATE">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "G"."CVALIDATE"<=TO_DATE(' 2015-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR ("G"."INSURPROTOCOLDATE">=TO_DATE(' 2015-11-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "G"."CVALIDATE"<=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))))
48 - access("C"."GRPCONTNO"="D"."GRPCONTNO" AND "C"."CONTNO"="D"."CONTNO" AND "C"."INSUREDNO"="D"."INSUREDNO")
49 - filter("C"."MANAGECOM" LIKE '86%')
Statistics
----------------------------------------------------------
1944733 recursive calls
4331962 db block gets
17399665 consistent gets
2 physical reads
116 redo size
2735 bytes sent via SQL*Net to client
5649 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
180506 sorts (memory)
0 sorts (disk)
13 rows processed
二，處理過程。
從統計資訊上看，為了完成最終只有13行（13 rows processed）的一個查詢，邏輯讀17399665個塊次（17399665 consistent gets），以資料庫塊8K大小的預設值計算的話，意味著前後一共處理了130G左右資料。遞迴呼叫也很高（1944733 recursive calls）。
遞迴呼叫也是一種比較消耗資源的操作，當發生空間不足，需要更多的分割槽（extent)時；引起觸發器工作時；執行儲存過程、自定義函式等時；需要從磁碟讀取資料字典資訊時等情況下，都會引起遞迴呼叫。也可以簡單理解為需要執行本SQL之外的其它SQL時，就稱之為遞迴呼叫。
再從執行計劃上看，注意到starts列上有不少成千上萬的值，甚至還有460K的。而該列是表示相應物件被訪問的次數的。如果相應的物件的訪問方法還是全表掃描或是索引全掃描，那就更需要引起注意。
所以，初步看上去，調優的潛力很大。（返回行少，但資源消耗大的，通常調優的潛力都比較大。）
在閱讀SQL程式碼時，發現其中有這樣的程式碼：
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30')
顯然，這裡的splitstr_jzy和getSumPlanPrem都是自定義函式。考慮到前邊較高的遞迴呼叫，所以，很可能這種過高的遞迴呼叫是因這兩個自定義函式所產生的。
這時，第一個想法產生了，即暫時註釋掉這些函式的引用，看看情況會如何。如果效能有明顯提升，說明問題的根源肯定與這些自定義函式相關。我們就要把主要精力先放在這些自定義函式上。如果效能提升不大，那麼說明問題的根源與這些自定義函式關係不大，我們需要繼續分析其它方面的問題。
為節省往篇幅，這裡只提供與這些自定義函式相關的程式碼：
/*splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
1)*/ 0 prem,
/*splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
2)*/ 0 prem2,
/*splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
3)*/ 0 prem3
from lccont c, lcinsured d, lcgrpcont g
從以上程式碼可見，通過註釋掉相關函式，並固定設定一個0值來代替原函式執行的結果。之所以用一個固定值來代替，是因為相關自定義函式的呼叫處於子查詢中，外部查詢需要使用這些函式的結果，如果完全註釋掉，還要修改外部查詢的相關程式碼，動靜比較大。
再次執行註釋掉相關函式的SQL，統計資訊的結果如下：
Statistics
----------------------------------------------------------
2718 recursive calls
0 db block gets
1671681 consistent gets
3255 physical reads
260 redo size
2432 bytes sent via SQL*Net to client
5762 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
99 sorts (memory)
0 sorts (disk)
13 rows processed
很明顯，遞迴呼叫減少為約2700次，邏輯讀1671681個塊次，相較於註釋前，分別只是原來的千分之一和十分之一。而且實際的執行時間也只要不到20秒了。看來這兩個函式是我們主攻的方向。
通過對getSumPlanPrem原始碼的分析，發現其主要是對若干表的單表查詢，並將獲得的指定列的值賦給變數，然後根據對這些變數的比較和計算，最終形成三個數值結果，但返回時，是將三個數值結果轉換為字元，並以逗號分隔拼接後，以一個字串的形式返回。而這些單表的查詢中，有相當一部分的表上並沒有適用的索引（不是沒有索引，而是沒有適合相關查詢的索引。），而使得部分查詢需要訪問更多的資料塊才能得到需要的結果，需要消耗更多的資源。比如：
select c.prem
into tPrem
from lccont c
where c.grpcontno = cGrpContNo
and c.insuredno = cInsur edNo;
這樣一個查詢。表lccont有一個名為PK_LCCONT的主鍵，主鍵列為（CONTNO）。grpcontno列和insuredno列各有一個單列索引，而該表共有約6萬行資料。僅從這條語句的角度來看，無論是走這兩列中的任一個索引，最終都要回表去獲取PREM列的值，不如將grpcontno列、insuredno列和prem列編入一個複合索引中，這樣不僅不用回表，而且這個索引的選擇性會更好。這裡簡單展開一下，對於複合索引，建立時列的順序是很有講究的。要把WHERE條件中出現的列寫到前邊，如果有多個，哪個重複值高，就把哪一列寫到前面（通常而言）。這樣，當某個SQL中只有對第二列條件的過濾時，就有可能用上該複合索引（以索引跳躍掃描的方式來訪問），反之，把重複值低的放到前面，當只有對第二列條件的過濾時，這個複合索引很可能是用不上的。而造成這種狀況的原因是和複合索引的儲存結構相關的。
再次執行原SQL（未註釋自定義函式程式碼的SQL），統計資訊如下：
Statistics
----------------------------------------------------------
1944042 recursive calls
4331962 db block gets
4623587 consistent gets
351 physical reads
144 redo size
2735 bytes sent via SQL*Net to client
5650 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
180498 sorts (memory)
0 sorts (disk)
13 rows processed
邏輯讀降為4623587個塊次（4623587 consistent gets），只是原來的四分之一了。但由於呼叫自定義函式的次數沒有減少，所以，遞迴呼叫的次數沒有改變。
再來看這段對自定義函式呼叫的程式碼：
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
1) prem,
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
2)0 prem2,
splitstr_jzy(getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30'),
3) prem3
可以看到對getSumPlanPrem的呼叫重複了三次，且輸入的引數完全一樣。而splitstr_jzy函式的作用就是把以逗號分隔的，包括三個結果值的字串根據第二個輸入引數的值，把第N段的結果拆分出來。
如果我們這裡把它變成只呼叫一次，那麼該自定義函式的呼叫就會減少三分之二。那麼無論是遞迴呼叫的次數，還是函式執行產生的資源消耗都會減少。
所以，我們這裡只保留一次呼叫，而這個splitstr_jzy自定義函式的功能，完全可以用正規表示式來實現，即把外部查詢對原prem，prem2和prem3的引用，分別改為regexp_substr(prem_all,'[^,]+',1,1), regexp_substr(prem_all,'[^,]+',1,2), regexp_substr(prem_all,'[^,]+',1,3)。
再次修改後的程式碼大致為：
select ...
regexp_substr(prem_all,'[^,]+',1,1) prem,
regexp_substr(prem_all,'[^,]+',1,2) prem2,
regexp_substr(prem_all,'[^,]+',1,3) prem3,
...
from (
select ....
getSumPlanPrem(c.grpcontno,
d.insuredno,
date '2015-01-01',
date '2015-11-30') prem_all
from .....
where .....)
where ....
group by .....;
對這樣處理的SQL程式碼再次執行，其統計資訊如下：
Statistics
----------------------------------------------------------
527686 recursive calls
0 db block gets
2416931 consistent gets
93 physical reads
188 redo size
2917 bytes sent via SQL*Net to client
6671 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
60166 sorts (memory)
0 sorts (disk)
13 rows processed
遞迴呼叫的次數變為527686次，邏輯讀的次數為2416931次，相較於上一次改寫，又分別下降了約70%和50%。實際的執行時間也減少到約30秒。
而此時的執行計劃是：
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:32.02 | 2416K| | | |
| 1 | TABLE ACCESS BY INDEX ROWID | LDCODE | 2 | 1 | 2 |00:00:00.01 | 6 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_LDCODE | 2 | 1 | 2 |00:00:00.01 | 4 | | | |
|* 3 | COUNT STOPKEY | | 4800 | | 4800 |00:00:00.07 | 9600 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | LCCONTPLAN | 4800 | 1 | 4800 |00:00:00.05 | 9600 | | | |
|* 5 | INDEX RANGE SCAN | PK_LCCONTPLAN | 4800 | 1 | 4800 |00:00:00.03 | 4800 | | | |
| 6 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:01.30 | 140K| | | |
| 7 | NESTED LOOPS | | 60166 | 1 | 18122 |00:00:01.17 | 140K| | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAIL | 60166 | 1 | 24808 |00:00:00.82 | 90718 | | | |
|* 9 | INDEX RANGE SCAN | IDX_LLCLAIMDETAIL_CSTMNO | 60166 | 2 | 35102 |00:00:00.50 | 60780 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 24808 | 1 | 18122 |00:00:00.28 | 49622 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_LLCLAIM | 24808 | 1 | 24808 |00:00:00.14 | 24810 | | | |
| 12 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:07.19 | 1315K| | | |
|* 13 | FILTER | | 60166 | | 2421 |00:00:07.10 | 1315K| | | |
| 14 | NESTED LOOPS | | 60166 | 3 | 16062 |00:00:04.91 | 1121K| | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 60166 | 3 | 460K|00:00:01.90 | 201K| | | |
|* 16 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_INSUREDNO | 60166 | 117 | 610K|00:00:00.57 | 122K| | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 460K| 1 | 16062 |00:00:02.63 | 920K| | | |
|* 18 | INDEX UNIQUE SCAN | PK_LLCLAIM | 460K| 1 | 460K|00:00:01.48 | 460K| | | |
| 19 | SORT AGGREGATE | | 12397 | 1 | 12397 |00:00:02.10 | 193K| | | |
| 20 | NESTED LOOPS ANTI | | 12397 | 1 | 104K|00:00:01.98 | 193K| | | |
| 21 | NESTED LOOPS | | 12397 | 1 | 143K|00:00:01.61 | 143K| | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 12397 | 1 | 12397 |00:00:00.09 | 24796 | | | |
|* 23 | INDEX UNIQUE SCAN | PK_LLCLAIM | 12397 | 1 | 12397 |00:00:00.05 | 12399 | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 12397 | 1 | 143K|00:00:01.50 | 118K| | | |
|* 25 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_CLMNO | 12397 | 1 | 1116K|00:00:00.10 | 29984 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAILB | 16011 | 1 | 2849 |00:00:00.24 | 50276 | | | |
|* 27 | INDEX RANGE SCAN | PK_LLCLAIMDETAILB | 16011 | 2 | 51475 |00:00:00.13 | 32852 | | | |
| 28 | SORT AGGREGATE | | 60166 | 1 | 60166 |00:00:01.29 | 201K| | | |
|* 29 | FILTER | | 60166 | | 0 |00:00:01.20 | 201K| | | |
| 30 | NESTED LOOPS | | 60166 | 1 | 0 |00:00:01.16 | 201K| | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | LLCLAIMPOLICYBAK | 60166 | 1 | 0 |00:00:01.11 | 201K| | | |
|* 32 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_INSUREDNO | 60166 | 117 | 610K|00:00:00.35 | 122K| | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | PK_LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 35 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 36 | NESTED LOOPS ANTI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 37 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 38 | TABLE ACCESS BY INDEX ROWID| LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 39 | INDEX UNIQUE SCAN | PK_LLCLAIM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID| LLCLAIMPOLICYBAK | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 41 | INDEX RANGE SCAN | IDX_LLCLAIMPOLICYBAK_CLMNO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | LLCLAIMDETAILB | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 43 | INDEX RANGE SCAN | PK_LLCLAIMDETAILB | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 44 | HASH GROUP BY | | 1 | 5 | 13 |00:00:32.02 | 2416K| 728K| 728K| 994K (0)|
| 45 | VIEW | | 1 | 2233 | 60166 |00:00:30.08 | 2416K| | | |
|* 46 | HASH JOIN | | 1 | 2233 | 60166 |00:00:03.12 | 7461 | 820K| 820K| 1192K (0)|
|* 47 | TABLE ACCESS FULL | LCGRPCONT | 1 | 48 | 48 |00:00:00.01 | 16 | | | |
|* 48 | HASH JOIN | | 1 | 2256 | 65410 |00:00:03.08 | 7445 | 5933K| 2261K| 6473K (0)|
|* 49 | TABLE ACCESS FULL | LCCONT | 1 | 49349 | 65410 |00:00:02.78 | 4163 | | | |
| 50 | TABLE ACCESS FULL | LCINSURED | 1 | 65400 | 65410 |00:00:00.07 | 3282 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
而下一步的重點，則要是要消除這些成千上萬次的表連線次數。而結合謂詞資訊的分析，發現這裡大部分的成千上萬次表連線的原因，就是使用了大量的標量子查詢（即只返回一行一列的子查詢）。而這些子查詢又都出現在外部查詢的SELECT部分，這也就意味著，外部查詢返回多少行，這個子查詢就要執行多少次，相關的表就要被訪問多少次。所以，進一步優化的方法，就是改寫SQL，把這些子查詢移到FROM 後面，這樣相關表只要訪問一次就可以。
由於改寫過程中出現改寫後的結果與原SQL不一致，而使用者又不願提供原始資料供分析，以及擔心SQL改動過大而可能引起的不利因素等原因，所以，進一步的優化沒能進行下去。
按我本意，是想在將大部分的超高表連線次數消除後，繼續檢查相關表上統計資訊的準確性（從執行計劃中可見，有部分處理步驟中，估算的行數與實際的行數相差較大。），以確保CBO不致因統計資訊的問題而產生錯誤的執行計劃。如果一切順利，這個SQL的執行時間調到秒出是有可能的。
三、總結
通過這個案例，有以下幾點經驗：
1、當出現超高的遞迴呼叫時，要檢查SQL中是否有自定義函式的呼叫，並嘗試減少這些函式的呼叫，以及優化函式自身。
2、當有內建函式可以實現的功能時，儘量使用系統內建函式。
3、當邏輯讀較高，且伴隨有大量的表連線次數（starts列）時，可以通過改寫SQL，達到減少表連線次數，降低邏輯讀的目的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1869816/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化案例-自定義函式索引（五）SQL優化函式索引
- Oracle優化案例-自定義函式索引（五）Oracle優化函式索引
- 案例展示自定義C函式的實現過程函式
- SQL最佳化案例-自定義函式索引（五）SQL函式索引
- shell自定義函式函式
- Oracle 自定義函式Oracle函式
- perl自定義函式函式
- GRDB自定義的純函式函式
- HIVE中的自定義函式Hive函式
- Hive常用函式及自定義函式Hive函式
- hive 3.0.0自定義函式Hive函式
- Hive中自定義函式Hive函式
- MySQL建立自定義函式MySql函式
- python 自定義函式Python函式
- oracle 自定義聚合函式Oracle函式
- mysql自定義函式篇MySql函式
- Oracle中自定義函式Oracle函式
- 一個自定義函式函式
- Oracle自定義聚集函式Oracle函式
- ORACLE 自定義函式BUG?Oracle函式
- SQL SERVER 自定義函式SQLServer函式
- SQL 自定義函式FUNCTIONSQL函式Function
- 動畫函式的繪製及自定義動畫函式動畫函式
- 編號函式 自定義函式 集合型別 表的優化 資料傾斜函式型別優化
- 函式索引使用細節——自定義函式的索引化函式索引
- 轉:對字串的“sum”——在Oracle中自定義聚集函式的例子字串Oracle函式
- PHP 自定義函式用法及常用函式集合PHP函式
- java自定義equals函式和hashCode函式Java函式
- mysql 自定義分析函式 least 及 日期函式MySql函式AST
- laravel 自定義全域性函式Laravel函式
- Laravel 自定義函式存放位置Laravel函式
- Laravel 新增自定義助手函式Laravel函式
- Laravel自定義輔助函式Laravel函式
- Sql Server系列：自定義函式SQLServer函式
- Oracle自定義聚集函式薦Oracle函式
- Teradata自定義函式Replace函式
- Linux Shell 自定義函式Linux函式
- Hive函式（內建函式+自定義標準函式UDF）Hive函式