手工设置cardinality的优化案例
1、问题描述：
某客户反馈，有条看了一年多的SQL没有搞定，数据库版本11.2.0.1，SQL文本如下
select *
from (select 1
from (SELECT DISTINCT m.policyNo,
m.endorSeqNo,
r.planCode,
r.riskCode,
a.itemno,
a.itemCode,
a.itemName,
to_char(r.startDate, 'yyyy-MM-dd hh24:mi:ss') as startDate,
to_char(decode(substr(r.riskCode, 0, 2),
'08',
decode(r.delayfinalenddate,
null,
r.enddate,
r.delayfinalenddate),
r.enddate),
'yyyy-MM-dd hh24:mi:ss') as endDate,
decode(substr(r.riskCode, 0, 2),
'08',
(select gu.licenseno || '_' ||
gu.brandname
from gupolicycopyitemmotor gu
where gu.policyno = a.policyno
and gu.endorseqno = a.endorseqno
and gu.plancode = a.plancode
and gu.riskcode = a.riskcode
and gu.itemno = a.itemno),
a.itemNo) as itemNameDisplay,
k.riskCname,
k.riskTname,
k.riskEname,
m.channeltip,
n.codecname,
m.insuredname
FROM GuPolicyCopyMain m,
GuPolicyCopyRisk r,
GuPolicyCopyItemMain a,
GuPolicyCopyEndorHead h,
GUPolicyCopyItemEngineering e,
GgRisk k,
GgCode n
WHERE m.policyNo = r.policyNo
and (k.riskclass = '11' or k.riskclass = '10')
AND m.endorSeqNo = r.endorSeqNo
AND m.policyNo = a.policyNo
AND m.endorSeqNo = a.endorSeqNo
AND r.riskcode = a.riskcode
AND m.policyNo = h.policyNo
AND m.endorSeqNo = h.endorSeqNo
AND r.riskCode = k.riskCode
AND n.codecode = m.channeltip
AND n.codetype = 'UnderWriteChannelTip'
AND k.opencoverind <> '1'
AND e.policyNo(+) = a.policyNo
AND e.endorSeqNo(+) = a.endorSeqNo
AND e.itemNo(+) = a.itemNo
AND (r.delayenddate is null OR
(TIMESTAMP'2020-12-10 00:00:00.000' not between
r.delaystartdate and r.delayenddate))
AND (h.validDate <= TIMESTAMP'2020-12-10 00:00:00.000' and
TIMESTAMP'2020-12-10 00:00:00.000' between
e.MaintenStartDate AND e.MaintenEndDate + interval
'23:59:59' hour to
second OR TIMESTAMP'2020-12-10 00:00:00.000' between
r.RetroactiveStartDate and
r.RetroactiveEndDate + interval '23:59:59' hour to
second OR TIMESTAMP'2020-12-10 00:00:00.000' between
r.startDate and r.endDate)
AND h.endorSeqNo =
(SELECT MAX(endorSeqNo)
FROM GuPolicyCopyEndorHead t
WHERE t.policyNo = h.policyNo
AND t.validDate <=
TIMESTAMP'2020-12-10 00:00:00.000')
AND r.riskCode <> '1205'
AND ((substr(r.riskcode, 0, 2) = '08' and
substr(m.Companycode, 0, 4) not in
('010105',
'0101',
'0102',
'0103',
'0104',
'0105',
'0106') and substr(m.Companycode, 0, 6) not in
('010105',
'0101',
'0102',
'0103',
'0104',
'0105',
'0106')) or
substr(r.riskcode, 0, 2) <> '08')
AND m.insuredCode in
(SELECT ClientCode
FROM GSClientPersona
WHERE IdentifyNumber = '532128198412070317')
AND (r.policyNo LIKE '6%')
AND (((((m.CompanyCode LIKE '01%')))))))
where rownum <= :1
执行计划如下:
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5395 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | | 5395 (1)| 00:01:05 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMMOTOR | 1 | 81 | 3 (0)| 00:00:01 | | |
|* 4 | INDEX UNIQUE SCAN | PK_CITEM_CAR4 | 1 | | 2 (0)| 00:00:01 | | |
|* 5 | SORT GROUP BY STOPKEY | | 1 | 446 | 5395 (1)| 00:01:05 | | |
| 6 | NESTED LOOPS | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 446 | 5390 (1)| 00:01:05 | | |
| 8 | NESTED LOOPS | | 1 | 362 | 5389 (1)| 00:01:05 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 299 | 5385 (1)| 00:01:05 | | |
| 10 | NESTED LOOPS | | 1 | 263 | 5383 (1)| 00:01:05 | | |
| 11 | NESTED LOOPS | | 1 | 206 | 5380 (1)| 00:01:05 | | |
|* 12 | HASH JOIN | | 1 | 142 | 5377 (1)| 00:01:05 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | GSCLIENTPERSONA | 1 | 32 | 5 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | IDX1_GSCLIENTPERSONA | 1 | | 3 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | | | | | | |
| 16 | NESTED LOOPS | | 1520 | 163K| 5372 (1)| 00:01:05 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1519 | 53165 | 3849 (1)| 00:00:47 | | |
|* 18 | INDEX RANGE SCAN | PK_PHEAD2 | 1519 | | 2731 (1)| 00:00:33 | | |
| 19 | SORT AGGREGATE | | 1 | 35 | | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 4 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 3 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | PK_GUPOLICYCO21 | 1 | | 1 (0)| 00:00:01 | | |
|* 23 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 75 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 24 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 64 | 3 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | IDX_GGCODE_CODECODE | 1 | | 2 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMMAIN | 1 | 57 | 3 (0)| 00:00:01 | | |
|* 27 | INDEX RANGE SCAN | IDX_GUPOLICY_POLICYNO | 1 | | 2 (0)| 00:00:01 | | |
| 28 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMENGINEERING | 1 | 36 | 2 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | ID_PC_ENG_REF_ITEMMAIN | 1 | | 1 (0)| 00:00:01 | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 63 | 4 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | PK_GUPOLICYCOPY | 1 | | 3 (0)| 00:00:01 | | |
|* 32 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 84 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
4 - access("GU"."POLICYNO"=:B1 AND "GU"."ENDORSEQNO"=:B2 AND "GU"."PLANCODE"=:B3 AND "GU"."RISKCODE"=:B4 AND "GU"."ITEMNO"=:B5)
5 - filter(ROWNUM<=500)
12 - access("M"."INSUREDCODE"="CLIENTCODE")
14 - access("IDENTIFYNUMBER"='532128198412070317')
18 - access("H"."POLICYNO" LIKE '6%')
filter(("H"."POLICYNO" LIKE '6%' AND "H"."ENDORSEQNO"=))
20 - filter(INTERNAL_FUNCTION("T"."VALIDDATE")<=TIMESTAMP' 2020-12-10 00:00:00.000000000')
21 - access("T"."POLICYNO"=:B1)
22 - access("M"."POLICYNO"="H"."POLICYNO" AND "M"."ENDORSEQNO"="H"."ENDORSEQNO")
filter("M"."POLICYNO" LIKE '6%')
23 - filter("M"."COMPANYCODE" LIKE '01%')
24 - filter("N"."CODETYPE"='UnderWriteChannelTip')
25 - access("N"."CODECODE"="M"."CHANNELTIP")
26 - filter(("M"."ENDORSEQNO"="A"."ENDORSEQNO" AND "A"."RISKCODE"<>'1205'))
27 - access("M"."POLICYNO"="A"."POLICYNO")
filter("A"."POLICYNO" LIKE '6%')
29 - access("E"."POLICYNO"="A"."POLICYNO" AND "E"."ENDORSEQNO"="A"."ENDORSEQNO" AND "E"."ITEMNO"="A"."ITEMNO")
filter("E"."POLICYNO" LIKE '6%')
30 - filter((("R"."DELAYENDDATE" IS NULL OR INTERNAL_FUNCTION("R"."DELAYSTARTDATE")>TIMESTAMP' 2020-12-10 00:00:00.000000000' OR
INTERNAL_FUNCTION("R"."DELAYENDDATE")<TIMESTAMP' 2020-12-10 00:00:00.000000000') AND
((INTERNAL_FUNCTION("H"."VALIDDATE")<=TIMESTAMP' 2020-12-10 00:00:00.000000000' AND
INTERNAL_FUNCTION("E"."MAINTENSTARTDATE")<=TIMESTAMP' 2020-12-10 00:00:00.000000000' AND
INTERNAL_FUNCTION("E"."MAINTENENDDATE")+INTERVAL'+00 23:59:59.000000' DAY(2) TO SECOND(6)>=TIMESTAMP' 2020-12-10
00:00:00.000000000') OR (INTERNAL_FUNCTION("R"."RETROACTIVESTARTDATE")<=TIMESTAMP' 2020-12-10 00:00:00.000000000' AND
INTERNAL_FUNCTION("R"."RETROACTIVEENDDATE")+INTERVAL'+00 23:59:59.000000' DAY(2) TO SECOND(6)>=TIMESTAMP' 2020-12-10
00:00:00.000000000') OR (INTERNAL_FUNCTION("R"."STARTDATE")<=TIMESTAMP' 2020-12-10 00:00:00.000000000' AND
INTERNAL_FUNCTION("R"."ENDDATE")>=TIMESTAMP' 2020-12-10 00:00:00.000000000')) AND ((SUBSTR("R"."RISKCODE",0,2)='08' AND
SUBSTR("M"."COMPANYCODE",0,4)<>'010105' AND SUBSTR("M"."COMPANYCODE",0,4)<>'0101' AND SUBSTR("M"."COMPANYCODE",0,4)<>'0102' AND
SUBSTR("M"."COMPANYCODE",0,4)<>'0103' AND SUBSTR("M"."COMPANYCODE",0,4)<>'0104' AND SUBSTR("M"."COMPANYCODE",0,4)<>'0105' AND
SUBSTR("M"."COMPANYCODE",0,4)<>'0106' AND SUBSTR("M"."COMPANYCODE",0,6)<>'010105' AND SUBSTR("M"."COMPANYCODE",0,6)<>'0101' AND
SUBSTR("M"."COMPANYCODE",0,6)<>'0102' AND SUBSTR("M"."COMPANYCODE",0,6)<>'0103' AND SUBSTR("M"."COMPANYCODE",0,6)<>'0104' AND
SUBSTR("M"."COMPANYCODE",0,6)<>'0105' AND SUBSTR("M"."COMPANYCODE",0,6)<>'0106') OR SUBSTR("R"."RISKCODE",0,2)<>'08')))
31 - access("M"."POLICYNO"="R"."POLICYNO" AND "M"."ENDORSEQNO"="R"."ENDORSEQNO" AND "R"."RISKCODE"="A"."RISKCODE")
filter(("R"."POLICYNO" LIKE '6%' AND "R"."RISKCODE"<>'1205' AND "R"."RISKCODE"="A"."RISKCODE"))
32 - access("R"."RISKCODE"="K"."RISKCODE")
filter("K"."RISKCODE"<>'1205')
33 - filter((INTERNAL_FUNCTION("K"."RISKCLASS") AND "K"."OPENCOVERIND"<>'1'))
二、问题分析
通过查询该SQL历史执行情况，逻辑读高达1200W，耗时120妙，但每次平均返回3行。
观察执行计划，该SQL走NL没问题，但为什么逻辑读却高达1200W？分析发现，ID=18 --> ID=21行数估算差距过大，估算返回1520条数据，而实际上返回400W+，这就是为什么该SQL产生1200W逻辑读的原因。进一步分析发现，表和索引的统计信息是最新的，为了快速解决问题，我们将GUPOLICYCOPYENDORHEAD的cardinality设置为400W，再次观察执行计划怎么走：
。。。省略
SELECT /*+cardinality(h 408788300) cardinality(@tt 408788300) */ DISTINCT m.policyNo
。。。省略
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:11.26 | 500K| 499K| | | |
|* 1 | COUNT STOPKEY | | 1 | | 3 |00:00:11.26 | 500K| 499K| | | |
| 2 | VIEW | | 1 | 1 | 3 |00:00:11.26 | 500K| 499K| | | |
|* 3 | SORT GROUP BY STOPKEY | | 1 | 1 | 3 |00:00:11.26 | 500K| 499K| 2048 | 2048 | 2048 (0)|
| 4 | NESTED LOOPS | | 1 | 1 | 3 |00:00:11.25 | 500K| 499K| | | |
| 5 | NESTED LOOPS | | 1 | 1 | 3 |00:00:11.25 | 500K| 499K| | | |
| 6 | NESTED LOOPS | | 1 | 1 | 3 |00:00:11.25 | 500K| 499K| | | |
| 7 | NESTED LOOPS | | 1 | 1 | 3 |00:00:11.25 | 500K| 499K| | | |
| 8 | NESTED LOOPS OUTER | | 1 | 1 | 4 |00:00:11.25 | 500K| 499K| | | |
| 9 | NESTED LOOPS | | 1 | 1 | 4 |00:00:11.25 | 500K| 499K| | | |
| 10 | NESTED LOOPS | | 1 | 1 | 4 |00:00:11.25 | 500K| 499K| | | |
|* 11 | HASH JOIN | | 1 | 1 | 4 |00:00:11.25 | 499K| 499K| 1134K| 1134K| 546K (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| GSCLIENTPERSONA | 1 | 1 | 2 |00:00:00.01 | 5 | 0 | | | |
|* 13 | INDEX RANGE SCAN | IDX1_GSCLIENTPERSONA | 1 | 1 | 2 |00:00:00.01 | 3 | 0 | | | |
| 14 | PARTITION RANGE ALL | | 1 | 374K| 5094K|00:00:08.80 | 499K| 499K| | | |
|* 15 | TABLE ACCESS FULL | GUPOLICYCOPYMAIN | 12 | 374K| 5094K|00:00:07.49 | 499K| 499K| | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | GGCODE | 4 | 1 | 4 |00:00:00.01 | 18 | 0 | | | |
|* 17 | INDEX RANGE SCAN | IDX_GGCODE_CODECODE | 4 | 1 | 8 |00:00:00.01 | 10 | 0 | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMMAIN | 4 | 1 | 4 |00:00:00.01 | 14 | 0 | | | |
|* 19 | INDEX RANGE SCAN | IDX_GUPOLICY_POLICYNO | 4 | 1 | 4 |00:00:00.01 | 10 | 0 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMENGINEERING | 4 | 1 | 0 |00:00:00.01 | 6 | 1 | | | |
|* 21 | INDEX RANGE SCAN | ID_PC_ENG_REF_ITEMMAIN | 4 | 1 | 0 |00:00:00.01 | 6 | 1 | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 4 | 1 | 3 |00:00:00.01 | 17 | 0 | | | |
|* 23 | INDEX RANGE SCAN | PK_GUPOLICYCOPY | 4 | 1 | 4 |00:00:00.01 | 14 | 0 | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | GGRISK | 3 | 1 | 3 |00:00:00.01 | 3 | 0 | | | |
|* 25 | INDEX UNIQUE SCAN | PK_GGRISK | 3 | 1 | 3 |00:00:00.01 | 2 | 0 | | | |
|* 26 | INDEX RANGE SCAN | PK_PHEAD2 | 3 | 1 | 3 |00:00:00.01 | 8 | 0 | | | |
|* 27 | VIEW PUSHED PREDICATE | VW_SQ_1 | 3 | 1 | 3 |00:00:00.01 | 8 | 0 | | | |
| 28 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 8 | 0 | | | |
| 29 | FIRST ROW | | 3 | 1 | 3 |00:00:00.01 | 8 | 0 | | | |
|* 30 | INDEX RANGE SCAN (MIN/MAX) | PK_PHEAD2 | 3 | 1 | 3 |00:00:00.01 | 8 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
这里我们发现执行计划变了，GUPOLICYCOPYENDORHEAD不是性能问题点了，现在问题转换为GUPOLICYCOPYMAIN的全表扫描，剩下的问题就简单了，在GUPOLICYCOPYMAIN上创建INSUREDCODE单列索引，该SQL消耗101逻辑读，妙出结果。
3、问题总结
该SQL主要问题是在GUPOLICYCOPYENDORHEAD表走LIKE匹配索引产生的ROWS估算相关太大(有兴趣的同学可以测试研究一下ORACLE对LIKE的COST算法以及为什么产生这么大的ROWS估算差距)，为了快速解决问题，先将GUPOLICYCOPYENDORHEAD的设置准确，再次观察执行计划发现，GSClientPersona表就可以过滤掉GUPOLICYCOPYMAIN大量数据，所以两表应该走NL，创建合适的索引即可。
对SQL优化有兴趣的同学，可以添加微信号：chenjunbee
或关注微信公众号，定期更新优化案例
浙公网安备 33010602011771号