[20220507]优化的困惑13.txt

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

[20220507]优化的困惑13.txt --//生产系统一条sql语句,优化时遇到的问题,做一个记录: 1.环境: > @ 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. 2.语句如下: --//sql_id=1br2zkb5wx8np. SELECT gy_jbbm.jbxh,gy_jbbm.jbmc,gy_jbbm.icd9,gy_jbbm.pydm FROM gy_jbbm LEFT OUTER JOIN emr_xyjbbm ON GY_JBBM.jbxh = emr_xyjbbm.jbxh AND emr_xyjbbm.zxbz = 0 where (GY_JBBM.DMLB = 10) AND ( (GY_JBBM.ICD9 LIKE '%龋病%' ) OR (GY_JBBM.JBMC LIKE '%龋病%' ) OR (GY_JBBM.PYDM LIKE '%龋病%' ) OR (EMR_XYJBBM.BMMC LIKE '%龋病%' ) OR (EMR_XYJBBM.PYDM LIKE '%龋病%' ) ) ORDER BY GY_JBBM.ICD9,GY_JBBM.JBMC,GY_JBBM.PYDM; --//注:为了测试方便我直接带入真实的值. --//打开统计后执行计划如下: Plan hash value: 3718689590 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | | | | 7277 (100)| | 4 |00:00:00.15 | 1385 | | | | | 1 | SORT ORDER BY | | 1 | 152K| 30M| 32M| 7277 (1)| 00:00:01 | 4 |00:00:00.15 | 1385 | 2048 | 2048 | 2048 (0)| |* 2 | FILTER | | 1 | | | | | | 4 |00:00:00.15 | 1385 | | | | | 3 | NESTED LOOPS OUTER | | 1 | 152K| 30M| | 375 (1)| 00:00:01 | 152K|00:00:00.08 | 1385 | | | | |* 4 | INDEX FAST FULL SCAN | I_GY_JBBM_ALL | 1 | 152K| 6863K| | 374 (1)| 00:00:01 | 152K|00:00:00.03 | 1385 | | | | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMR_XYJBBM | 152K| 1 | 162 | | 0 (0)| | 0 |00:00:00.02 | 0 | | | | |* 6 | INDEX RANGE SCAN | IDX_EMR_XYJBBM_JBXH | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((("GY_JBBM"."ICD9" LIKE '%龋病%' AND "GY_JBBM"."ICD9" IS NOT NULL AND "GY_JBBM"."ICD9" IS NOT NULL) OR ("GY_JBBM"."JBMC" LIKE '%龋病%' AND "GY_JBBM"."JBMC" IS NOT NULL AND "GY_JBBM"."JBMC" IS NOT NULL) OR ("GY_JBBM"."PYDM" LIKE '%龋病%' AND "GY_JBBM"."PYDM" IS NOT NULL AND "GY_JBBM"."PYDM" IS NOT NULL) OR "EMR_XYJBBM"."BMMC" LIKE '%龋病%' OR "EMR_XYJBBM"."PYDM" LIKE '%龋病%')) 4 - filter("GY_JBBM"."DMLB"=10) 5 - filter("EMR_XYJBBM"."ZXBZ"=0) 6 - access("GY_JBBM"."JBXH"="EMR_XYJBBM"."JBXH") --//补充说明,实际上这条sql语句没有什么好优化的,我建立I_GY_JBBM_ALL索引,覆盖了gy_jbbm的全部显示字段.这样一定程度不要全表 --//扫描gy_jbbm,减少一定的逻辑读.奇怪的是id=2的FILTER出现在最后,如果你仔细看Predicate Information (identified by operation id): --//可以发现oracle 自动加入了像如下条件两遍: "GY_JBBM"."ICD9" IS NOT NULL AND "GY_JBBM"."ICD9" IS NOT NULL --//不知道为什么?实际上开始我并没有认真看执行计划,我开始想为什么过滤(GY_JBBM.ICD9 LIKE '%龋病%' ) 不发生在id=4,而是出现在最后id=2. --//这样直接执行实际上"很慢"的,好在EMR_XYJBBM是一个没有记录的空表,不然id=5,starts=152K次,逻辑读会很大. --//我再次仔细看了sql语句.我个人并不喜欢ansi的语法,或者讲更加喜欢使用+的语法,我以前经常改写不知道那边写入+号. --//我自己做了一个简单记忆左连接正好相反在右边的表使用+. --//其实开发上面的写法要非常小心,不小心很容易发生错误,参考链接: --//http://blog.itpub.net/267265/viewspace-1988395/ => [20160213]关于ansi语法.txt --//我想更多的是上面的写法可能并不是开发想要写法.或者讲我认为开发写错了. --//使用expandz.sql 展开看看。 $ cat tpt/expandz.sql set long 40000 set serveroutput on column arg new_value arg set term off select decode(&2,11,'sql2','utility') arg from dual; set term on prompt declare l_sqltext clob := null; l_result clob := null; begin select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1'; -- dbms_output.put_line(l_sqltext); -- dbms_sql2.expand_sql_text(l_sqltext,l_result); dbms_&arg..expand_sql_text(l_sqltext,l_result); dbms_output.put_line(l_result); end; / set serveroutput off > @ cs PPPP_HHHH alter session set current_schema=PPPP_HHHH Session altered. > @ expandz 1br2zkb5wx8np 19 SELECT "A1"."QCSJ_C000000000300000_0" "JBXH" , "A1"."JBMC_2" "JBMC" , "A1"."ICD9_4" "ICD9" , "A1"."QCSJ_C000000000300002_3" "PYDM" FROM ( SELECT "A3"."JBXH" "QCSJ_C000000000300000_0" , "A3"."DMLB" "DMLB_1" , "A3"."JBMC" "JBMC_2" , "A3"."PYDM" "QCSJ_C000000000300002_3" , "A3"."ICD9" "ICD9_4" , "A3"."ZXBZ" "QCSJ_C000000000300004" , "A2"."JBXH" "QCSJ_C000000000300001" , "A2"."BMMC" "BMMC_7" , "A2"."PYDM" "QCSJ_C000000000300003_8" , "A2"."ZXBZ" "QCSJ_C000000000300005" FROM "PPPPPP_HHH"."GY_JBBM" "A3" , "PPPPPP_HHH"."EMR_XYJBBM" "A2" WHERE "A3"."JBXH" = "A2"."JBXH" AND "A2"."ZXBZ" = :B1) "A1" WHERE "A1"."DMLB_1" = :B2 AND ("A1"."ICD9_4" LIKE :B3 OR "A1"."JBMC_2" LIKE :B4 OR "A1"."QCSJ_C000000000300002_3" LIKE :B5 OR "A1"."BMMC_7" LIKE :B6 OR "A1"."QCSJ_C000000000300003_8" LIKE :B7) ORDER BY "A1"."ICD9_4" , "A1"."JBMC_2" , "A1"."QCSJ_C000000000300002_3" PL/SQL procedure successfully completed. --//注我做了格式化处理,可以发现展开后like在最后处理。另外还可以发现oracle展开后根本不存在外连接，为什么不懂。 --//我带入执行，没有结果，很明显展开是错误的。 SELECT "A1"."QCSJ_C000000000300000_0" "JBXH" , "A1"."JBMC_2" "JBMC" , "A1"."ICD9_4" "ICD9" , "A1"."QCSJ_C000000000300002_3" "PYDM" FROM ( SELECT "A3"."JBXH" "QCSJ_C000000000300000_0" , "A3"."DMLB" "DMLB_1" , "A3"."JBMC" "JBMC_2" , "A3"."PYDM" "QCSJ_C000000000300002_3" , "A3"."ICD9" "ICD9_4" , "A3"."ZXBZ" "QCSJ_C000000000300004" , "A2"."JBXH" "QCSJ_C000000000300001" , "A2"."BMMC" "BMMC_7" , "A2"."PYDM" "QCSJ_C000000000300003_8" , "A2"."ZXBZ" "QCSJ_C000000000300005" FROM "PPPPPP_HHH"."GY_JBBM" "A3" , "PPPPPP_HHH"."EMR_XYJBBM" "A2" WHERE "A3"."JBXH" = "A2"."JBXH" AND "A2"."ZXBZ" = 0) "A1" WHERE "A1"."DMLB_1" = 10 AND ("A1"."ICD9_4" LIKE '%龋病%' OR "A1"."JBMC_2" LIKE '%龋病%' OR "A1"."QCSJ_C000000000300002_3" LIKE '%龋病%' OR "A1"."BMMC_7" LIKE '%龋病%' OR "A1"."QCSJ_C000000000300003_8" LIKE '%龋病%') ORDER BY "A1"."ICD9_4" , "A1"."JBMC_2" , "A1"."QCSJ_C000000000300002_3" $ cat 10053x.sql execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1'); > @ 10053x 1br2zkb5wx8np 0 PL/SQL procedure successfully completed. --//查看跟踪文件： Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "GY_JBBM"."JBXH" "JBXH" , "GY_JBBM"."JBMC" "JBMC" , "GY_JBBM"."ICD9" "ICD9" , "GY_JBBM"."PYDM" "PYDM" FROM "PPPPPP_HHH"."GY_JBBM" "GY_JBBM" , "PPPPPP_HHH"."EMR_XYJBBM" "EMR_XYJBBM" WHERE "GY_JBBM"."DMLB" = :B1 AND ("GY_JBBM"."ICD9" LIKE :B2 OR "GY_JBBM"."JBMC" LIKE :B3 OR "GY_JBBM"."PYDM" LIKE :B4 OR "EMR_XYJBBM"."BMMC" LIKE :B5 OR "EMR_XYJBBM"."PYDM" LIKE :B6) AND "GY_JBBM"."JBXH" = "EMR_XYJBBM"."JBXH"(+) AND "EMR_XYJBBM"."ZXBZ"(+) = :B7 ORDER BY "GY_JBBM"."ICD9" , "GY_JBBM"."JBMC" , "GY_JBBM"."PYDM" kkoqbc: optimizing query block SEL$2BFA4EE4 (#1) --//你可以发现oracle实际上最后还是转换成+的语法。 3.改写: --//首先我个人认为上面的写法开发写错了.我使用加号改写,注意可能不是等价的改写!! SELECT /*+ gather_plan_statistics */ gy_jbbm.jbxh , gy_jbbm.jbmc , gy_jbbm.icd9 , gy_jbbm.pydm FROM gy_jbbm , emr_xyjbbm WHERE GY_JBBM.jbxh = emr_xyjbbm.jbxh(+) AND emr_xyjbbm.zxbz(+) = 0 AND (GY_JBBM.DMLB = 10) AND ( (GY_JBBM.ICD9 LIKE '%龋病%') OR (GY_JBBM.JBMC LIKE '%龋病%') OR (GY_JBBM.PYDM LIKE '%龋病%') OR (EMR_XYJBBM.BMMC LIKE '%龋病%') OR (EMR_XYJBBM.PYDM LIKE '%龋病%')) ORDER BY GY_JBBM.ICD9 , GY_JBBM.JBMC , GY_JBBM.PYDM; --//对比前面的10053x.sql看到，该语句是等价转换。 --//执行计划如下: Plan hash value: 3718689590 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | | | | 7277 (100)| | 4 |00:00:00.13 | 1385 | | | | | 1 | SORT ORDER BY | | 1 | 152K| 30M| 32M| 7277 (1)| 00:00:01 | 4 |00:00:00.13 | 1385 | 2048 | 2048 | 2048 (0)| |* 2 | FILTER | | 1 | | | | | | 4 |00:00:00.05 | 1385 | | | | | 3 | NESTED LOOPS OUTER | | 1 | 152K| 30M| | 375 (1)| 00:00:01 | 152K|00:00:00.08 | 1385 | | | | |* 4 | INDEX FAST FULL SCAN | I_GY_JBBM_ALL | 1 | 152K| 6863K| | 374 (1)| 00:00:01 | 152K|00:00:00.03 | 1385 | | | | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMR_XYJBBM | 152K| 1 | 162 | | 0 (0)| | 0 |00:00:00.02 | 0 | | | | |* 6 | INDEX RANGE SCAN | IDX_EMR_XYJBBM_JBXH | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((("GY_JBBM"."ICD9" LIKE '%龋病%' AND "GY_JBBM"."ICD9" IS NOT NULL AND "GY_JBBM"."ICD9" IS NOT NULL) OR ("GY_JBBM"."JBMC" LIKE '%龋病%' AND "GY_JBBM"."JBMC" IS NOT NULL AND "GY_JBBM"."JBMC" IS NOT NULL) OR ("GY_JBBM"."PYDM" LIKE '%龋病%' AND "GY_JBBM"."PYDM" IS NOT NULL AND "GY_JBBM"."PYDM" IS NOT NULL) OR "EMR_XYJBBM"."BMMC" LIKE '%龋病%' OR "EMR_XYJBBM"."PYDM" LIKE '%龋病%')) 4 - filter("GY_JBBM"."DMLB"=10) 5 - filter("EMR_XYJBBM"."ZXBZ"=0) 6 - access("GY_JBBM"."JBXH"="EMR_XYJBBM"."JBXH") --//这时我才注意,里面的一堆or.实际上开发要表达的是(我认为),应该之间的是and不是or. --//我尝试在EMR_XYJBBM表的字段上写入加号.(EMR_XYJBBM.BMMC(+) LIKE '%龋病%'),报如下错误： ORA-01719: outer join operator (+) not allowed in operand of OR or IN --//修改如下： SELECT /*+ gather_plan_statistics */ gy_jbbm.jbxh ,gy_jbbm.jbmc ,gy_jbbm.icd9 ,gy_jbbm.pydm FROM gy_jbbm, emr_xyjbbm WHERE GY_JBBM.jbxh = emr_xyjbbm.jbxh(+) AND emr_xyjbbm.zxbz(+) = 0 AND (GY_JBBM.DMLB = 10) AND ( GY_JBBM.ICD9 LIKE '%龋病%' OR GY_JBBM.JBMC LIKE '%龋病%' OR GY_JBBM.PYDM LIKE '%龋病%') AND ( EMR_XYJBBM.BMMC(+) LIKE '%龋病%' ~~~ OR EMR_XYJBBM.PYDM(+) LIKE '%龋病%') ORDER BY GY_JBBM.ICD9, GY_JBBM.JBMC, GY_JBBM.PYDM; --//执行计划如下: Plan hash value: 1931007572 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 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 | | | | 1319 (100)| | 4 |00:00:00.07 | 1385 | | | | | 1 | SORT ORDER BY | | 1 | 20844 | 4233K| 4520K| 1319 (1)| 00:00:01 | 4 |00:00:00.07 | 1385 | 2048 | 2048 | 2048 (0)| | 2 | NESTED LOOPS OUTER | | 1 | 20844 | 4233K| | 375 (1)| 00:00:01 | 4 |00:00:00.03 | 1385 | | | | |* 3 | INDEX FAST FULL SCAN | I_GY_JBBM_ALL | 1 | 20844 | 936K| | 375 (1)| 00:00:01 | 4 |00:00:00.03 | 1385 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMR_XYJBBM | 4 | 1 | 162 | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | |* 5 | INDEX RANGE SCAN | IDX_EMR_XYJBBM_JBXH | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(((("GY_JBBM"."JBMC" LIKE '%龋病%' AND "GY_JBBM"."JBMC" IS NOT NULL AND "GY_JBBM"."JBMC" IS NOT NULL) OR ("GY_JBBM"."PYDM" LIKE '%龋病%' AND "GY_JBBM"."PYDM" IS NOT NULL AND "GY_JBBM"."PYDM" IS NOT NULL) OR ("GY_JBBM"."ICD9" LIKE '%龋病%' AND "GY_JBBM"."ICD9" IS NOT NULL AND "GY_JBBM"."ICD9" IS NOT NULL)) AND "GY_JBBM"."DMLB"=10)) 4 - filter(("EMR_XYJBBM"."ZXBZ"=0 AND ("EMR_XYJBBM"."BMMC" LIKE '%龋病%' OR "EMR_XYJBBM"."PYDM" LIKE '%龋病%'))) 5 - access("GY_JBBM"."JBXH"="EMR_XYJBBM"."JBXH") --//我想这才是开发想要的执行方式。id=4的循环次数现在是starts=4. --//或者讲开发上面使用ansi的语法,"正确"的写法如下: SELECT /*+ gather_plan_statistics */ gy_jbbm.jbxh ,gy_jbbm.jbmc ,gy_jbbm.icd9 ,gy_jbbm.pydm FROM gy_jbbm LEFT OUTER JOIN emr_xyjbbm ON GY_JBBM.jbxh = emr_xyjbbm.jbxh AND ( EMR_XYJBBM.BMMC LIKE '%龋病%' OR EMR_XYJBBM.PYDM LIKE '%龋病%') AND emr_xyjbbm.zxbz = 0 WHERE GY_JBBM.DMLB = 10 AND ( GY_JBBM.ICD9 LIKE '%龋病%' OR GY_JBBM.JBMC LIKE '%龋病%' OR GY_JBBM.PYDM LIKE '%龋病%') ORDER BY GY_JBBM.ICD9, GY_JBBM.JBMC, GY_JBBM.PYDM; --//执行计划如下: Plan hash value: 1931007572 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 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 | | | | 1319 (100)| | 4 |00:00:00.07 | 1385 | | | | | 1 | SORT ORDER BY | | 1 | 20844 | 4233K| 4520K| 1319 (1)| 00:00:01 | 4 |00:00:00.07 | 1385 | 2048 | 2048 | 2048 (0)| | 2 | NESTED LOOPS OUTER | | 1 | 20844 | 4233K| | 375 (1)| 00:00:01 | 4 |00:00:00.03 | 1385 | | | | |* 3 | INDEX FAST FULL SCAN | I_GY_JBBM_ALL | 1 | 20844 | 936K| | 375 (1)| 00:00:01 | 4 |00:00:00.03 | 1385 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMR_XYJBBM | 4 | 1 | 162 | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | |* 5 | INDEX RANGE SCAN | IDX_EMR_XYJBBM_JBXH | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(((("GY_JBBM"."JBMC" LIKE '%龋病%' AND "GY_JBBM"."JBMC" IS NOT NULL AND "GY_JBBM"."JBMC" IS NOT NULL) OR ("GY_JBBM"."PYDM" LIKE '%龋病%' AND "GY_JBBM"."PYDM" IS NOT NULL AND "GY_JBBM"."PYDM" IS NOT NULL) OR ("GY_JBBM"."ICD9" LIKE '%龋病%' AND "GY_JBBM"."ICD9" IS NOT NULL AND "GY_JBBM"."ICD9" IS NOT NULL)) AND "GY_JBBM"."DMLB"=10)) 4 - filter(("EMR_XYJBBM"."ZXBZ"=0 AND ("EMR_XYJBBM"."BMMC" LIKE '%龋病%' OR "EMR_XYJBBM"."PYDM" LIKE '%龋病%'))) 5 - access("GY_JBBM"."JBXH"="EMR_XYJBBM"."JBXH") 3.总结： --//我个人比较喜欢+的语法，实际上我个人认为上面的写法很容易写错，开发团队应该统一使用的语法。 --//而且使用加号的语法理论上满足大部分应用的需求，而且不容易出错。

编辑推荐：

返回列表

[20220507]优化的困惑13.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推