[20220428]優化的困惑12.txt
[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 ( )
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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2889312/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210203]max優化的困惑.txt優化
- [20200401]優化的困惑5.txt優化
- [20200408]優化的困惑6.txt優化
- [20220507]優化的困惑13.txt優化
- [20200808]優化的困惑10.txt優化
- [20201224]sql優化困惑.txtSQL優化
- [20200320]SQL語句優化的困惑.txtSQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- 找工作的困惑
- 自學java的困惑Java
- 學習java的困惑Java
- 寫程式碼的困惑
- 關於JavaScript的困惑JavaScript
- java新學者的困惑Java
- 對spring的困惑？？？？Spring
- 困惑度的計算
- 用NIO UDPSERVER的困惑UDPServer
- 單例模式static的困惑單例模式
- MySQL優化（1）——–常用的優化步驟MySql優化
- 中年程式設計師的困惑程式設計師
- 分散式與叢集的困惑分散式
- 關於物件導向的困惑！物件
- 偶的伺服器：nio,困惑！伺服器
- 求助，動態代理模式的困惑模式
- 對SPRING事務的困惑Spring
- 初學設計模式的困惑設計模式
- 軟體測試員的困惑
- 困惑的軟體測試員
- 前端效能優化（JS/CSS優化，SEO優化）前端優化JSCSS
- hive的優化Hive優化
- web的優化Web優化
- mysql的優化MySql優化
- Cacti的優化優化
- 效能優化案例-SQL優化優化SQL
- MSSQL優化之索引優化SQL優化索引
- CUDA優化之指令優化優化
- 數值最優化—優化問題的解(二)優化
- seo優化中不容忽視的頁面優化優化