对自定义函数使用不当的调优案例

最新推荐文章于 2021-07-19 17:20:50 发布

ckjt2463

最新推荐文章于 2021-07-19 17:20:50 发布

阅读量299

点赞数

文章标签：数据库

一、问题。
下面这条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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22207394/viewspace-1869816/