[20211229]toad下優化sql語句注意的問題.txt
[20211229]toad下優化sql語句注意的問題.txt
--//生產系統一條sql語句優化看看，順便提一下在toad下優化sql語句需要注意的地方。
1.環境:
xxxx1> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
xxxx1> @awr/sqlh bf4uwcbsc8zd2 % &day
BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2021-12-28 14:00:06 bf4uwcbsc8zd2 355084439 1 120885 120728 128716.0 26055125 0 0 0.0 0 0 0
2021-12-28 16:00:42 bf4uwcbsc8zd2 355084439 1 96320 96129 112501.0 20697273 0 0 0.0 1 0 0
2021-12-28 18:00:19 bf4uwcbsc8zd2 355084439 5 121376 121056 128973.8 26364612 0 0 0.0 0 0 0
2021-12-29 10:00:12 bf4uwcbsc8zd2 355084439 1 122641 122172 128999.0 26378605 0 0 0.0 0 0 0
--//sql_id=bf4uwcbsc8zd2 格式化如下，提示gather_plan_statistics我加入的。
--//執行次數很少僅僅幾次,但是每次執行需要122秒,使用者真有耐心,開發也是一樣.96秒那次應該是使用者中斷了.
2.測試分析:
--//抽取sql語句,並且格式化在toad下:
SELECT /*+ gather_plan_statistics */
"GY_FYBM"."FYMC"
,"GY_YLSF"."FYDW"
,"GY_YLSF"."FYDJ"
,"GY_YLSF"."PYDM"
,"GY_YLSF"."WBDM"
,"GY_YLSF"."FYGG"
,"GY_YLSF"."FYXH"
,"GY_YLSF"."FYGB"
,"GY_YLSF"."XMBM"
, (SELECT aka065
FROM yb_yhyb_dzml
WHERE ake001 = GY_YLSF.YBDM AND ROWNUM = 1)
AS YBLB
FROM "GY_YLSF", "GY_FYBM"
WHERE ("GY_FYBM"."FYXH" = "GY_YLSF"."FYXH")
AND (GY_YLSF.ZFPB = 0)
AND (GY_YLSF.ZYSY = 1)
AND GY_FYBM.FYMC LIKE '%%';
--//我估計介面上有一個地方可以輸入要查詢的FYMC值,不過明顯開發選擇兩邊加百分號的模糊查詢方式,我估計使用者懶,沒有輸入查詢值.
Plan hash value: 355084439
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | | 17M(100)| | 1001 |00:00:00.04 | 689 | | | |
|* 1 | COUNT STOPKEY | | 184 | | | | | | 0 |00:00:01.01 | 211K| | | |
|* 2 | TABLE ACCESS FULL | YB_YHYB_DZML | 184 | 2 | 30 | | 157 (0)| 00:00:01 | 0 |00:00:01.01 | 211K| | | |
|* 3 | HASH JOIN | | 1 | 133K| 13M| | 771 (1)| 00:00:01 | 1001 |00:00:00.04 | 689 | 5871K| 2474K| 8715K (0)|
| 4 | TABLE ACCESS FULL | GY_YLMX | 1 | 82634 | 806K| | 68 (0)| 00:00:01 | 84539 |00:00:00.01 | 252 | | | |
|* 5 | HASH JOIN | | 1 | 68147 | 6388K| 2744K| 702 (1)| 00:00:01 | 501 |00:00:00.02 | 437 | 5971K| 1708K| 7760K (0)|
|* 6 | TABLE ACCESS FULL| GY_FYBM | 1 | 71925 | 1896K| | 117 (1)| 00:00:01 | 72265 |00:00:00.01 | 428 | | | |
|* 7 | TABLE ACCESS FULL| GY_YLML | 1 | 40867 | 2753K| | 295 (1)| 00:00:01 | 233 |00:00:00.01 | 9 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("AKE001"=:B1)
3 - access("GY_YLML"."FYXH"="GY_YLMX"."FYXH")
5 - access("GY_FYBM"."FYXH"="GY_YLML"."FYXH")
6 - filter(("GY_FYBM"."FYMC" IS NOT NULL AND "GY_FYBM"."FYMC" LIKE '%%'))
7 - filter(("GY_YLML"."ZFPB"=0 AND "GY_YLML"."ZYSY"=1))
--//問題主要出在id =2 ,全表掃描184次，我在toad下做的測試，1秒返回,我當時就很納悶,什麼回事.
--//實際上在toad下我沒有選擇auto trace，僅僅顯示前面幾行。這樣看到的統計資訊就是上面的樣子.
--//這個也是在以後在使用toad優化sql語句時需要注意的地方。我的測試才1秒，實際上前面的查詢需要122秒。
--//很明顯yb_yhyb_dzml 沒有建立欄位AKE001 索引。建立後再次測試,這次開啟了auto trace,也就是會顯示全部結果集.
Plan hash value: 1796302826
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | | 227K(100)| | 129K|00:00:00.13 | 1895 | | | |
|* 1 | COUNT STOPKEY | | 22998 | | | | | | 0 |00:00:00.04 | 29211 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| YB_YHYB_DZML | 22998 | 2 | 30 | | 2 (0)| 00:00:01 | 0 |00:00:00.03 | 29211 | | | |
|* 3 | INDEX RANGE SCAN | I_YB_YHYB_DZML_AKE001 | 22998 | 4 | | | 1 (0)| 00:00:01 | 0 |00:00:00.02 | 29211 | | | |
|* 4 | HASH JOIN | | 1 | 133K| 13M| | 771 (1)| 00:00:01 | 129K|00:00:00.13 | 1895 | 5871K| 2474K| 8781K (0)|
| 5 | TABLE ACCESS FULL | GY_YLMX | 1 | 82634 | 806K| | 68 (0)| 00:00:01 | 84539 |00:00:00.01 | 252 | | | |
|* 6 | HASH JOIN | | 1 | 68147 | 6388K| 2744K| 702 (1)| 00:00:01 | 65495 |00:00:00.06 | 1643 | 5971K| 1708K| 7775K (0)|
|* 7 | TABLE ACCESS FULL | GY_FYBM | 1 | 71925 | 1896K| | 117 (1)| 00:00:01 | 72265 |00:00:00.01 | 428 | | | |
|* 8 | TABLE ACCESS FULL | GY_YLML | 1 | 40867 | 2753K| | 295 (1)| 00:00:01 | 41182 |00:00:00.01 | 1215 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//這次開啟了auto trace，你可以發現id=2,starts=22998.你可以發現前面查詢GY_YLML 的buffers=9,而這次1215.
--//注意前面id=7,E-Rows = 40867, A-Rows =233,差點以為需要建立ZFPB，ZYSY的複合索引,而且還以為oracle選擇的連線順序不對.
--//順便說一下GY_YLSF是一個檢視，開發命名規則不是很好。
--//注意一個細節id=2看到的A-Rows=0，也就是查詢不到結果。這也是id=1,2,3的buffers都是29211的緣故.
--//理論如果有返回,邏輯度更高,應該建立複合索引ake001,aka065 會更好一些。
--//建立複合索引後，不需要回表，當然目前返回記錄0也不需要回表.刪除I_YB_YHYB_DZML_AKE001重建複合索引,測試如下:
--//查詢返回有點多129K,我檢索共享池發現確實存在像GY_FYBM.FYMC LIKE '%子%'之類的類似查詢.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 227K(100)| | 129K|00:00:00.13 | 1895 | 0 | | | |
|* 1 | COUNT STOPKEY | | 22998 | | | | | | 0 |00:00:00.03 | 29211 | 8 | | | |
|* 2 | INDEX RANGE SCAN | I_YB_YHYB_DZML_AKE001_AKA065 | 22998 | 2 | 30 | | 2 (0)| 00:00:01 | 0 |00:00:00.02 | 29211 | 8 | | | |
|* 3 | HASH JOIN | | 1 | 133K| 13M| | 771 (1)| 00:00:01 | 129K|00:00:00.13 | 1895 | 0 | 5871K| 2474K| 8751K (0)|
| 4 | TABLE ACCESS FULL | GY_YLMX | 1 | 82634 | 806K| | 68 (0)| 00:00:01 | 84539 |00:00:00.01 | 252 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 68147 | 6388K| 2744K| 702 (1)| 00:00:01 | 65495 |00:00:00.06 | 1643 | 0 | 5971K| 1708K| 7742K (0)|
|* 6 | TABLE ACCESS FULL| GY_FYBM | 1 | 71925 | 1896K| | 117 (1)| 00:00:01 | 72265 |00:00:00.01 | 428 | 0 | | | |
|* 7 | TABLE ACCESS FULL| GY_YLML | 1 | 40867 | 2753K| | 295 (1)| 00:00:01 | 41182 |00:00:00.02 | 1215 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3.總結:
1.看執行計劃時最佳要開啟auto trace,至少要執行完成,不要顯示前幾行就看執行計劃,這樣不準.
2.另外注意一個地方,在toad下執行語句不會peek繫結變數,這樣導致要使用直方圖的執行計劃不對，應該寫入常量代替。
3.開發應該合理地選擇標量子查詢,不要濫用或者講應該慎用標量子查詢.
4.理論講aka001不變，aka065也不變，可以不要標量子查詢，寫成如下：
SELECT /*+ gather_plan_statistics */
"GY_FYBM"."FYMC"
,"GY_YLSF"."FYDW"
,"GY_YLSF"."FYDJ"
,"GY_YLSF"."PYDM"
,"GY_YLSF"."WBDM"
,"GY_YLSF"."FYGG"
,"GY_YLSF"."FYXH"
,"GY_YLSF"."FYGB"
,"GY_YLSF"."XMBM"
-- , (SELECT aka065 FROM yb_yhyb_dzml WHERE ake001 = GY_YLSF.YBDM AND ROWNUM = 1) AS YBLB
, a.aka065 AS YBLB
FROM "GY_YLSF", "GY_FYBM" , (select aka065,ake001 from yb_yhyb_dzml group by aka065,ake001) a
WHERE ("GY_FYBM"."FYXH" = "GY_YLSF"."FYXH")
AND (GY_YLSF.ZFPB = 0)
AND (GY_YLSF.ZYSY = 1)
AND GY_FYBM.FYMC LIKE '%%'
and GY_YLSF.YBDM = a.ake001(+) ;
Plan hash value: 3153486774
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | | 1089 (100)| | 129K|00:00:00.11 | 2959 | | | |
|* 1 | HASH JOIN | | 1 | 132K| 16M| | 1089 (1)| 00:00:01 | 129K|00:00:00.11 | 2959 | 5871K| 2474K| 8835K (0)|
| 2 | TABLE ACCESS FULL | GY_YLMX | 1 | 84538 | 825K| | 70 (2)| 00:00:01 | 84538 |00:00:00.01 | 251 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 68819 | 8266K| | 1019 (1)| 00:00:01 | 65525 |00:00:00.07 | 2707 | 2227K| 2096K| 2139K (0)|
| 4 | VIEW | | 1 | 17362 | 457K| | 316 (2)| 00:00:01 | 17362 |00:00:00.01 | 1147 | | | |
| 5 | HASH GROUP BY | | 1 | 17362 | 254K| | 316 (2)| 00:00:01 | 17362 |00:00:00.01 | 1147 | 2104K| 1684K| 1881K (0)|
| 6 | TABLE ACCESS FULL | YB_YHYB_DZML | 1 | 68154 | 998K| | 314 (1)| 00:00:01 | 68154 |00:00:00.01 | 1147 | | | |
|* 7 | HASH JOIN | | 1 | 68147 | 6388K| 2744K| 702 (1)| 00:00:01 | 65525 |00:00:00.05 | 1560 | 5971K| 1708K| 7834K (0)|
|* 8 | TABLE ACCESS FULL | GY_FYBM | 1 | 71925 | 1896K| | 117 (1)| 00:00:01 | 72292 |00:00:00.01 | 427 | | | |
|* 9 | TABLE ACCESS FULL | GY_YLML | 1 | 40867 | 2753K| | 295 (1)| 00:00:01 | 41184 |00:00:00.01 | 1132 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2850069/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 對sql語句的優化問題SQL優化
- [20211229]sql語句包含中文儲存clob的編碼問題.txtSQL
- oracle效能問題：sql語句優化OracleSQL優化
- [20210205]警惕toad下優化直方圖相關sql語句.txt優化直方圖SQL
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- SQL語句的優化SQL優化
- SQL語句優化SQL優化
- toad執行sql語句SQL
- SQL Server優化之SQL語句優化SQLServer優化
- SQL 語句的優化方法SQL優化
- 對sql語句的最佳化問題SQL
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 求助：SQL語句優化SQL優化
- 優化 SQL 語句的步驟優化SQL
- 一個SQL語句的優化SQL優化
- 關於sql語句的優化SQL優化
- 一條sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20201210]sql語句優化.txtSQL優化
- MySQL之SQL語句優化MySql優化
- SQL語句優化（轉載）SQL優化
- 常用SQL語句優化技巧SQL優化
- Oracle之sql語句優化OracleSQL優化
- jivejdon sql語句問題SQL
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 優化SQL 語句 in 和not in 的替代方案優化SQL
- SQL 語句的注意事項SQL
- [20120327]toad與sqlplus下執行sql語句的一個細節.txtSQL
- 使用SQL調整顧問進行語句優化SQL優化