[20220428]优化的困惑12.txt

来源：这里教程网时间：2026-03-03 17:36:24 作者：

[20220428]优化的困惑12.txt --//最近一直我优化数据库,该项目上线1年,我使用我改写TPT的ash_index_helper.sql脚本,我发现在使用中存在困惑,做一个记录. 1.环境: > @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.使用ash_index_helper分析: > @ash/ash_index_helper % pppppp_hhh.YF_YPGG &day 1 1 "plan_card<=10 and TABLE_ROWS>=1e4" -- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( https://tanelpoder.com ) SECONDS AAS CPU WAIT Accessed_Table Plan_Operation PLAN_CARD TABLE_ROWS FILTER_PCT SQL_EXECS ELA_SEC/EXEC PREDICATES SQL_ID MODULE1 ------- --- ----- ----- -------------- -------------------------------------- --------- ---------- ---------- --------- ------------ ------------------------------------------------------------ ------------- -------------------- 1 .0 100% 0% YF_YPGG TABLE ACCESS FULL [pppppp_hhh.YF_YPGG] 1 113960 .000877501 379 0.179 [F:] ("YF_YPGG"."MZSY"=:SYS_B_18 AND "YF_YPGG"."ZFBZ"=:SYS_ 6f3k62wqs3cdq portal.exe B_19) > @ bind_cap 6f3k62wqs3cdq 18 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- ------------------ ---------- ------------------- --------------- ------------ 6f3k62wqs3cdq 0 YES :SYS_B_18 19 22 2022-04-24 08:23:04 NUMBER 1 3 YES :SYS_B_18 19 22 2022-04-23 11:20:34 NUMBER 1 4 YES :SYS_B_18 19 22 2022-04-22 09:55:47 NUMBER 1 10 YES :SYS_B_18 19 22 2022-04-24 07:50:24 NUMBER 1 11 YES :SYS_B_18 19 22 2022-04-24 08:25:23 NUMBER 1 > @ bind_cap 6f3k62wqs3cdq 19 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- --------- -------- ---------- ------------------- --------------- ------------ 6f3k62wqs3cdq 0 YES :SYS_B_19 20 22 2022-04-24 08:23:04 NUMBER 0 3 YES :SYS_B_19 20 22 2022-04-23 11:20:34 NUMBER 0 4 YES :SYS_B_19 20 22 2022-04-22 09:55:47 NUMBER 0 10 YES :SYS_B_19 20 22 2022-04-24 07:50:24 NUMBER 0 11 YES :SYS_B_19 20 22 2022-04-24 08:25:23 NUMBER 0 --//你可以发现带入的参数是mzsy=1,zfbz=0. > select count(*) from pppppp_hhh.YF_YPGG; COUNT(*) ---------- 119851 > select count(*) from pppppp_hhh.YF_YPGG where zfbz=0 and mzsy=1; COUNT(*) ---------- 119842 > select count(*) from pppppp_hhh.YF_YPGG where zfbz=0 ; COUNT(*) ---------- 119851 > select count(*) from pppppp_hhh.YF_YPGG where mzsy=1 ; COUNT(*) ---------- 119842 --//这样无论如何也不应该出现PLAN_CARD=1的情况。为什么呢?贴出sql语句,仅仅包括where条件.我直接换成真实的值. SELECT DISTINCT .... FROM YK_TYPK ,YK_YPBM ,YF_YPXX ,V_EMR_YFKCMXTODJSL ,YF_YPGG WHERE ( YK_YPBM.YPXH = YK_TYPK.YPXH ) AND ( YK_YPBM.BMFL <= 2 ) AND ( YF_YPXX.YFZF = 0 ) AND ( YF_YPXX.YPXH = YK_TYPK.YPXH ) AND YK_TYPK.ZFPB = 0 AND (YF_YPXX.YPXH = V_EMR_YFKCMXTODJSL.YPXH) AND (YF_YPXX.YFSB = V_EMR_YFKCMXTODJSL.YFSB) AND (YF_YPXX.YPXH = YF_YPGG.YPXH ) AND (YF_YPXX.YFSB = YF_YPGG.YFSB) AND (YF_YPGG.MZSY = 1) AND (YF_YPGG.ZFBZ = 0) AND V_EMR_YFKCMXTODJSL.KCSL > 0 AND YF_YPXX.JGID = 3 ORDER BY PYDM,YK_TYPK.YPXH ASC; --//打开统计后,执行计划如下: -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 397 (100)| | 8902 |00:00:00.20 | 60757 | | | | | 1 | SORT ORDER BY | | 1 | 3 | 636 | 397 (2)| 00:00:01 | 8902 |00:00:00.20 | 60757 | 2250K| 697K| 1999K (0)| | 2 | HASH UNIQUE | | 1 | 3 | 636 | 396 (2)| 00:00:01 | 8902 |00:00:00.19 | 60757 | 10M| 1846K| 3217K (0)| | 3 | NESTED LOOPS | | 1 | 3 | 636 | 395 (2)| 00:00:01 | 18757 |00:00:00.17 | 60757 | | | | | 4 | NESTED LOOPS | | 1 | 3 | 636 | 395 (2)| 00:00:01 | 22082 |00:00:00.15 | 50646 | | | | |* 5 | HASH JOIN | | 1 | 1 | 184 | 392 (2)| 00:00:01 | 7497 |00:00:00.13 | 43096 | 1769K| 1133K| 1857K (0)| | 6 | NESTED LOOPS | | 1 | 1 | 159 | 390 (2)| 00:00:01 | 6732 |00:00:00.09 | 42312 | | | | | 7 | NESTED LOOPS | | 1 | 1 | 148 | 389 (2)| 00:00:01 | 6732 |00:00:00.08 | 28855 | | | | | 8 | NESTED LOOPS | | 1 | 1 | 36 | 388 (2)| 00:00:01 | 6736 |00:00:00.06 | 15227 | | | | | 9 | VIEW | V_EMR_YFKCMXTODJSL | 1 | 1 | 22 | 387 (2)| 00:00:01 | 6850 |00:00:00.05 | 1497 | | | | |* 10 | FILTER | | 1 | | | | | 6850 |00:00:00.05 | 1497 | | | | | 11 | HASH GROUP BY | | 1 | 1 | 64 | 387 (2)| 00:00:01 | 15151 |00:00:00.04 | 1497 | 2126K| 1362K| 2064K (0)| |* 12 | HASH JOIN RIGHT OUTER | | 1 | 34990 | 2186K| 385 (1)| 00:00:01 | 25595 |00:00:00.03 | 1497 | 1506K| 1506K| 1516K (0)| | 13 | VIEW | | 1 | 1 | 26 | 7 (15)| 00:00:01 | 43 |00:00:00.01 | 67 | | | | | 14 | HASH GROUP BY | | 1 | 1 | 33 | 7 (15)| 00:00:01 | 43 |00:00:00.01 | 67 | 1071K| 1071K| 1385K (0)| | 15 | NESTED LOOPS | | 1 | 1 | 33 | 6 (0)| 00:00:01 | 45 |00:00:00.01 | 67 | | | | | 16 | NESTED LOOPS | | 1 | 2 | 33 | 6 (0)| 00:00:01 | 45 |00:00:00.01 | 22 | | | | | 17 | TABLE ACCESS FULL | YF_KCDJ | 1 | 2 | 44 | 4 (0)| 00:00:01 | 45 |00:00:00.01 | 8 | | | | |* 18 | INDEX UNIQUE SCAN | PK_MS_CF01 | 45 | 1 | | 1 (0)| 00:00:01 | 45 |00:00:00.01 | 14 | | | | | 19 | TABLE ACCESS BY INDEX ROWID| MS_CF01 | 45 | 1 | 11 | 1 (0)| 00:00:01 | 45 |00:00:00.01 | 45 | | | | |* 20 | HASH JOIN | | 1 | 34990 | 1298K| 378 (1)| 00:00:01 | 25595 |00:00:00.03 | 1430 | 3317K| 1896K| 4696K (0)| |* 21 | TABLE ACCESS FULL | YF_KCMX | 1 | 34990 | 751K| 142 (0)| 00:00:01 | 34991 |00:00:00.01 | 518 | | | | | 22 | TABLE ACCESS FULL | YF_YPXX | 1 | 101K| 1589K| 235 (1)| 00:00:01 | 107K|00:00:00.01 | 912 | | | | |* 23 | TABLE ACCESS BY INDEX ROWID | YF_YPXX | 6850 | 1 | 14 | 1 (0)| 00:00:01 | 6736 |00:00:00.01 | 13730 | | | | |* 24 | INDEX UNIQUE SCAN | PK_YF_YPXX | 6850 | 1 | | 0 (0)| | 6850 |00:00:00.01 | 6868 | | | | |* 25 | TABLE ACCESS BY INDEX ROWID | YK_YPML | 6736 | 1 | 112 | 1 (0)| 00:00:01 | 6732 |00:00:00.01 | 13628 | | | | |* 26 | INDEX UNIQUE SCAN | PK_YK_YPML | 6736 | 1 | | 0 (0)| | 6735 |00:00:00.01 | 6742 | | | | | 27 | TABLE ACCESS BY INDEX ROWID | YK_YPXX | 6732 | 1 | 11 | 1 (0)| 00:00:01 | 6732 |00:00:00.01 | 13457 | | | | |* 28 | INDEX UNIQUE SCAN | PK_YK_YPXX | 6732 | 1 | | 0 (0)| | 6732 |00:00:00.01 | 6725 | | | | | 29 | TABLE ACCESS FULL | YF_YPGG | 1 | 1 | 25 | 2 (0)| 00:00:01 | 119K|00:00:00.01 | 784 | | | | |* 30 | INDEX RANGE SCAN | IDX_YK_YPBM_YPXH | 7497 | 3 | | 1 (0)| 00:00:01 | 22082 |00:00:00.01 | 7550 | | | | |* 31 | TABLE ACCESS BY INDEX ROWID | YK_YPBM | 22082 | 3 | 84 | 3 (0)| 00:00:01 | 18757 |00:00:00.01 | 10111 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ... 29 - filter(("YF_YPGG"."MZSY"=:SYS_B_18 AND "YF_YPGG"."ZFBZ"=:SYS_B_19)) --//重新分析表也一样,再放弃时仔细看执行计划发现实际上是视图V_EMR_YFKCMXTODJSL仅仅估计1行,导致后面的估计也是1行,id=9,实际A-rows=6850 --//执行计划也出现很多nested loops,导致逻辑读很多. --//有点奇怪的地方是id=29 执行全表扫描,但是连接方式选择hash join(id=5)的情况.是因为执行计划自适应的原因,因为返回119K,如 --//果走nested loop,相当于循环119K次,如果这样逻辑读更加可怕. --//从这个例子也提示不要想当然通过tpt ash_index_helper.sql脚本PREDICATES条件确定马上建立索引,要再仔细观察测试. --//要减少逻辑读,改用hash join链接才对.加入如下提示： /*+ CARDINALITY(V_EMR_YFKCMXTODJSL 7000) */ Plan hash value: 1446147555 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 3354 (100)| | 8954 |00:00:00.17 | 3625 | | | | | 1 | SORT ORDER BY | | 1 | 23071 | 4889K| 5288K| 3354 (1)| 00:00:01 | 8954 |00:00:00.17 | 3625 | 2250K| 697K| 1999K (0)| | 2 | HASH UNIQUE | | 1 | 23071 | 4889K| 5288K| 2266 (1)| 00:00:01 | 8954 |00:00:00.15 | 3625 | 10M| 1846K| 3530K (0)| |* 3 | HASH JOIN | | 1 | 23071 | 4889K| | 1178 (1)| 00:00:01 | 18871 |00:00:00.13 | 3625 | 2161K| 1647K| 2498K (0)| |* 4 | TABLE ACCESS FULL | YK_YPBM | 1 | 16124 | 440K| | 36 (0)| 00:00:01 | 16141 |00:00:00.01 | 130 | | | | |* 5 | HASH JOIN | | 1 | 8445 | 1558K| | 1142 (1)| 00:00:01 | 7536 |00:00:00.12 | 3495 | 1922K| 1922K| 1681K (0)| |* 6 | TABLE ACCESS FULL | YK_YPXX | 1 | 4916 | 54076 | | 16 (0)| 00:00:01 | 5939 |00:00:00.01 | 57 | | | | |* 7 | HASH JOIN | | 1 | 7825 | 1360K| | 1126 (1)| 00:00:01 | 7536 |00:00:00.12 | 3438 | 1348K| 1198K| 1532K (0)| |* 8 | TABLE ACCESS FULL | YK_YPML | 1 | 4555 | 498K| | 56 (0)| 00:00:01 | 4706 |00:00:00.01 | 208 | | | | |* 9 | HASH JOIN | | 1 | 7825 | 504K| | 1070 (1)| 00:00:01 | 7540 |00:00:00.11 | 3230 | 1599K| 1599K| 1756K (0)| |* 10 | HASH JOIN | | 1 | 7000 | 246K| | 853 (1)| 00:00:01 | 6777 |00:00:00.07 | 2446 | 1695K| 1695K| 2181K (0)| | 11 | VIEW | V_EMR_YFKCMXTODJSL | 1 | 7000 | 150K| | 618 (1)| 00:00:01 | 6891 |00:00:00.04 | 1534 | | | | |* 12 | FILTER | | 1 | | | | | | 6891 |00:00:00.04 | 1534 | | | | | 13 | HASH GROUP BY | | 1 | 1757 | 109K| | 618 (1)| 00:00:01 | 15194 |00:00:00.04 | 1534 | 2114K| 1353K| 1984K (0)| |* 14 | HASH JOIN RIGHT OUTER | | 1 | 35130 | 2195K| | 616 (1)| 00:00:01 | 25728 |00:00:00.03 | 1534 | 1506K| 1506K| 1707K (0)| | 15 | VIEW | | 1 | 231 | 6006 | | 238 (1)| 00:00:01 | 64 |00:00:00.01 | 104 | | | | | 16 | HASH GROUP BY | | 1 | 231 | 9240 | | 238 (1)| 00:00:01 | 64 |00:00:00.01 | 104 | 1071K| 1071K| 1392K (0)| | 17 | NESTED LOOPS | | 1 | 231 | 9240 | | 237 (1)| 00:00:01 | 82 |00:00:00.01 | 104 | | | | | 18 | NESTED LOOPS | | 1 | 231 | 9240 | | 237 (1)| 00:00:01 | 82 |00:00:00.01 | 22 | | | | | 19 | VIEW | VW_GBC_6 | 1 | 231 | 6699 | | 5 (20)| 00:00:01 | 82 |00:00:00.01 | 8 | | | | | 20 | HASH GROUP BY | | 1 | 231 | 5544 | | 5 (20)| 00:00:01 | 82 |00:00:00.01 | 8 | 1048K| 1048K| 1439K (0)| | 21 | TABLE ACCESS FULL | YF_KCDJ | 1 | 231 | 5544 | | 4 (0)| 00:00:01 | 82 |00:00:00.01 | 8 | | | | |* 22 | INDEX UNIQUE SCAN | PK_MS_CF01 | 82 | 1 | | | 1 (0)| 00:00:01 | 82 |00:00:00.01 | 14 | | | | | 23 | TABLE ACCESS BY INDEX ROWID| MS_CF01 | 82 | 1 | 11 | | 2 (0)| 00:00:01 | 82 |00:00:00.01 | 82 | | | | |* 24 | HASH JOIN | | 1 | 35130 | 1303K| | 378 (1)| 00:00:01 | 25728 |00:00:00.03 | 1430 | 3317K| 1896K| 4720K (0)| |* 25 | TABLE ACCESS FULL | YF_KCMX | 1 | 35130 | 754K| | 142 (0)| 00:00:01 | 35124 |00:00:00.01 | 518 | | | | | 26 | TABLE ACCESS FULL | YF_YPXX | 1 | 101K| 1589K| | 235 (1)| 00:00:01 | 107K|00:00:00.01 | 912 | | | | |* 27 | TABLE ACCESS FULL | YF_YPXX | 1 | 96283 | 1316K| | 235 (1)| 00:00:01 | 101K|00:00:00.01 | 912 | | | | |* 28 | TABLE ACCESS FULL | YF_YPGG | 1 | 119K| 3510K| | 216 (1)| 00:00:01 | 119K|00:00:00.01 | 784 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//这样执行效率才是最佳的。