[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 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//这样执行效率才是最佳的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2889312/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
