博客围绕一条执行时间过长的SQL展开，指出其瓶颈在于三张表错误的连接方式及由此导致的全表扫描。通过在join内部加hint，强制以TMP表为驱动表并采用NL连接方式，使执行时间缩短到80ms。还分析了索引列选择性差的问题，以及谓词推入对执行时间的影响。
问题sql当前执行时间800ms左右，系统需要它在150ms以内出结果，出现问题的sql如下：
SELECT /*+monitor test1 DYNAMIC_SAMPLING TMP 1 */
MIN(A.EU) OVER(PARTITION BY A.FINC) EU,
A...
B...
FROM CK.CHK_TMP TMP
JOIN EI_N.CPI_I B
ON TMP.KV = B.EID
JOIN (SELECT
/*+DYNAMIC_SAMPLING TMP 1 */
F.FINC,
SUM(F.SHAREPLACE) OVER(PARTITION BY F.FINC) SUMSREP,
F....
FROM CK.CHK_TMP TMP
JOIN EI_N.CPI_I D
ON TMP.KV = D.EID
JOIN EI_N.CPI_PR F
ON D.FINC = F.FINC) A
ON A.FINC = B.FINC
WHERE (B.SHARD <> A.SUMSREP OR
(B.SHARD IS NOT NULL AND A.SUMSREP IS NULL) OR
(B.SHARD IS NULL AND A.SUMSREP IS NOT NULL))
AND B.ISPUB = '0'
AND B.UPDATEDATE >= DATE '2013-1-1'
当前执行计划如下：
Plan Hash Value : 1484034248
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 326462 | 31340352 | 55991 | 00:00:03 |
| 1 | HASH UNIQUE | | 326462 | 31340352 | 55991 | 00:00:03 |
| 2 | WINDOW SORT | | 326462 | 31340352 | 55991 | 00:00:03 |
| * 3 | HASH JOIN | | 326462 | 31340352 | 41668 | 00:00:02 |
| * 4 | HASH JOIN | | 4620 | 258720 | 413 | 00:00:01 |
| 5 | TABLE ACCESS FULL | CHK_TMP | 8168 | 106184 | 29 | 00:00:01 |
| * 6 | TABLE ACCESS FULL | CPI_I | 11660 | 501380 | 384 | 00:00:01 |
| 7 | VIEW | | 1247605 | 49904200 | 41252 | 00:00:02 |
| 8 | WINDOW SORT | | 1247605 | 78599115 | 41252 | 00:00:02 |
| * 9 | HASH JOIN | | 1247605 | 78599115 | 22374 | 00:00:01 |
| * 10 | HASH JOIN | | 8168 | 253208 | 146 | 00:00:01 |
| 11 | TABLE ACCESS FULL | CHK_TMP | 8168 | 106184 | 29 | 00:00:01 |
| 12 | VIEW | index$_join$_006 | 20613 | 371034 | 116 | 00:00:01 |
| * 13 | HASH JOIN | | | | | |
| 14 | INDEX FAST FULL SCAN | NNK_CPI_I | 20613 | 371034 | 68 | 00:00:01 |
| 15 | INDEX FAST FULL SCAN | PK_CPI_I | 20613 | 371034 | 78 | 00:00:01 |
| 16 | TABLE ACCESS FULL | CPI_PR | 3148493 | 100751776 | 22221 | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("A"."FINC"="B"."FINC")
* 3 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL)
* 4 - access("TMP"."KV"="B"."EID")
* 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0')
* 9 - access("D"."FINC"="F"."FINC")
* 10 - access("TMP"."KV"="D"."EID")
* 13 - access(ROWID=ROWID)
这个执行计划CHK_TMP和CPI_I表哈希连接，结果集记为A，CHK_TMP和CPI_PR都和CPI_I表连接，NNK_CPI_I（FINC）和PK_CPI_I CHK_TMP（eid）和做哈希连接，结果集与CHK_TMP哈希连接，结果集再与CPI_PR哈希连接，结果集记为B，然后A与B再做哈希连接。
第一个哈希连接，因为CPI_I表只有两万多条数据，所以这一步执行时间大致等于CPI_I表全表扫描的时间，所以瓶颈不在这里。
而CPI_PR表有3167993多完数据，这张表如果全表扫描了那么sql整体一定无法在150ms内执行完毕，而且两个索引的快速全扫描，虽然是多块读，但是这里的执行顺序是存在很大问题的，chk_tmp作为驱动表，应该以它为起始驱动，一步一步向后驱动，而不是其他条件结果再来与它驱动。而且有一点值得注意，CPI_PR表在连接列明明存在2个可用的索引，但是没有用到。
所以这里的瓶颈就是：三张表错误的连接方式，以及由于该原因导致的全表扫描。当然了根本原因还是连接方式的问题。
那么如何解决这个问题呢？
在join （。。。） A的内部加hint，ordered use_nl(TMP D F)，强制以TMP表作为驱动表，并且以NL的方式连接。因为CPI_PR表太大，哈希连接很耗时。当然这只是一次尝试。
这次sql的执行时间缩短到了80ms，通过了应用的硬性规定。下面是通过sqlmonitor抓出来的执行计划：
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.06 | 0.06 | 0.00 | 1 | 2163 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=440150911)
==========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | |
| 1 | HASH UNIQUE | | 271K | 447K | | | 1 | | | | |
| 2 | WINDOW SORT | | 271K | 447K | | | 1 | | | | |
| 3 | NESTED LOOPS | | 271K | 426K | 1 | +0 | 1 | 0 | | | |
| 4 | HASH JOIN | | 4620 | 413 | 1 | +0 | 1 | 94 | 2M | | |
| 5 | TABLE ACCESS FULL | CHK_TMP | 8168 | 29 | 1 | +0 | 1 | 100 | | | |
| 6 | TABLE ACCESS FULL | CPI_I | 11660 | 384 | 1 | +0 | 1 | 14512 | | | |
| 7 | VIEW PUSHED PREDICATE | | 1 | 92 | 1 | +0 | 94 | 0 | | | |
| 8 | WINDOW BUFFER | | 1 | 92 | 1 | +0 | 94 | 473 | 6144 | | |
| 9 | NESTED LOOPS | | 1 | 92 | 1 | +0 | 94 | 473 | | | |
| 10 | NESTED LOOPS | | 1 | 92 | 1 | +0 | 94 | 473 | | | |
| 11 | NESTED LOOPS | | 1 | 88 | 1 | +0 | 94 | 94 | | | |
| 12 | TABLE ACCESS FULL | CHK_TMP | 8168 | 29 | 1 | +0 | 94 | 9400 | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 1 | 88 | 1 | +0 | 9400 | 94 | | | |
| 14 | BITMAP AND | | | | 1 | +0 | 9400 | 94 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS | | | | 1 | +0 | 9400 | 9400 | | | |
| 16 | INDEX RANGE SCAN | NNK_CPI_I | 1 | | 1 | +0 | 9400 | 9400 | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS | | | | 1 | +0 | 9400 | 4988 | | | |
| 18 | INDEX RANGE SCAN | PK_CPI_I | 1 | | 1 | +0 | 9400 | 9400 | | | |
| 19 | INDEX RANGE SCAN | IDX_CPI_PR_FPP | 1 | 3 | 1 | +0 | 94 | 473 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | CPI_PR | 1 | 4 | 1 | +0 | 473 | 473 | | | |
==========================================================================================================================================================================
Plan Hash Value : 440150911
sql monitor不存在每一步的注释，补充一下:
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("TMP"."KV"="B"."EID")
* 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0')
* 7 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL)
* 16 - access("D"."FINC"="B"."FINC")
* 18 - access("TMP"."KV"="D"."EID")
* 19 - access("F"."FINC"="B"."FINC")
* 19 - filter("D"."FINC"="F"."FINC")
时间满足了要求，但是紧接着出现了一个非常有趣的执行计划。可以看到出现了BITMAP CONVERSION FROM ROWIDS等位图索引的执行计划。但是NNK_CPI_I和PK_CPI_I并非是位图索引。执行计划中可以看到，通过对两个索引的范围扫，将rowid转换为bitmap（BITMAP CONVERSION FROM ROWIDS）进行匹配（BITMAP AND），然后再转换为rowid（BITMAP CONVERSION TO ROWIDS），结果集与CHK_TMP表进行嵌套循环连接。可以看到之前谈过的sql的瓶颈问题中，仍然存在不合理的连接方式，同一张表的两个索引进行连接匹配。再考虑如下问题，以及为什么CPI_PR这张表最初没有全表扫描，就会明白问题出现在什么地方了。
首先，为什么B*TREE索引会在执行计划中出现位图索引的关键字呢？
众所周知，Oracle对执行计划的选择是按照cost值来计算的。虽然在这个案例中，我的优化方向是缩短执行时间，但是这并不是优化器的工作方式。如果把B*TREE索引转化为位图方式匹配，cost值小的话，那这种方法也可以被Oracle采用。但是这种方式的cost值高达447338，那么最开始是多少呢？只有55991。所以一般这种方式不会被Oracle选择，但是这种方式是客观存在的，只是因为cost值一般比较大，所以普通B*TREE索引看不到bitmap的关键字。出现这种方法，也就意味着表上的索引列选择性不好。注意这种方式非常消耗CPU，这对很多系统都很不友好。
结合CPI_PR的全表扫描，结论就很明显了：FINC列选择性很差，没有也不好创建合适的索引。
select count(distinct FINC) from ei_n.CPI_PR
COUNT(DISTINCTFINC)
1 17038
select count(*) from ei_n.CPI_PR
COUNT(*)
1 3167996
可以看到，这个选择性真的很差。走索引的代价偏高。
select column_name,
NUM_DISTINCT,
DENSITY,
NUM_NULLS,
LAST_ANALYZED,
HISTOGRAM
from dba_tab_col_statistics
where table_name = 'CPI_PR'
and owner = 'NEWS'
and column_name='FINC';
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED HISTOGRAM
1 FINC 16780 .0000595947556615018 0 2018/12/6 23:30:37 NONE
没有直方图，这里只收集一下索引列的直方图
21:14:43 SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NEWS',tabname=>'CPI_PR',method_opt=>'for all indexed columns size skewonly',estimate_percent=>dbms_stats.auto_sample_size,no_invalidate=>false,cascade=>true,degree => 10);
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.36
收集之后的列的直方图信息：
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED HISTOGRAM
1 FINC 17138 .000058 0 2019/3/18 21:14:44 HYBRID
因为没有合适的索引，所以这里直方图意义不大。
上面的执行计划中可以看到，这里存在一个谓词推入，导致里面的A部分执行了9000多次，阻止谓词推入后，执行时间100ms，cost值变大，但是这里的执行计划很符合心理预期了，也比较好理解。
不多解释了，看看吧：
Plan Hash Value : 1209305020
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 326462 | 31340352 | 595284 | 00:00:24 |
| 1 | HASH UNIQUE | | 326462 | 31340352 | 595284 | 00:00:24 |
| 2 | WINDOW SORT | | 326462 | 31340352 | 595284 | 00:00:24 |
| * 3 | HASH JOIN | | 326462 | 31340352 | 580960 | 00:00:23 |
| * 4 | HASH JOIN | | 4620 | 258720 | 413 | 00:00:01 |
| 5 | TABLE ACCESS FULL | CHK_TMP | 8168 | 106184 | 29 | 00:00:01 |
| * 6 | TABLE ACCESS FULL | CPI_I | 11660 | 501380 | 384 | 00:00:01 |
| 7 | VIEW | | 1247605 | 49904200 | 580544 | 00:00:23 |
| 8 | WINDOW SORT | | 1247605 | 78599115 | 580544 | 00:00:23 |
| 9 | COUNT | | | | | |
| * 10 | FILTER | | | | | |
| 11 | NESTED LOOPS | | 1247605 | 78599115 | 561667 | 00:00:22 |
| 12 | NESTED LOOPS | | 1502912 | 78599115 | 561667 | 00:00:22 |
| 13 | NESTED LOOPS | | 8168 | 253208 | 8199 | 00:00:01 |
| 14 | TABLE ACCESS FULL | CHK_TMP | 8168 | 106184 | 29 | 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | CPI_I | 1 | 18 | 1 | 00:00:01 |
| * 16 | INDEX UNIQUE SCAN | PK_CPI_I | 1 | | 0 | 00:00:01 |
| * 17 | INDEX RANGE SCAN | PK_LG_CPI_PR | 184 | | 3 | 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | CPI_PR | 153 | 4896 | 81 | 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("A"."FINC"="B"."FINC")
* 3 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL)
* 4 - access("TMP"."KV"="B"."EID")
* 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0')
* 10 - filter(ROWNUM>0)
* 16 - access("TMP"."KV"="D"."EID")
* 17 - access("D"."FINC"="F"."FINC")
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2638709/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31480688/viewspace-2638709/
扫一扫
1861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
