[20211229]toad下优化sql语句注意的问题.txt

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

[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 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------

编辑推荐：

返回列表

[20211229]toad下优化sql语句注意的问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推