[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2850069/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
