[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.总结：
--//我个人比较喜欢+的语法，实际上我个人认为上面的写法很容易写错，开发团队应该统一使用的语法。
--//而且使用加号的语法理论上满足大部分应用的需求，而且不容易出错。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2893172/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 20261113跟踪通过SQL Translation Framewordd的sql语句library cache lock/pin的情况
- [20261109]关于sql语句对象的idn.txt
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
