SQL如何改变那些CBO无能为力的执行计划

64次阅读
没有评论

共计 4060 个字符,预计需要花费 11 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 SQL 如何改变那些 CBO 无能为力的执行计划,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!

用户写的  sql , Oracle  会进行等价改写,即使是  RBO  优化模式, Oracle  也会给你做一些转换,这些转化都是基于一种固定的算法, oracle  称这种转换是“启发式”的。比如我们写  inner join  时  ,并且只访问单表数据  , Oracle  会自动降为半连接,然后用  semi join  的方式给你做  join 。 transformation  是  Oracle  必做的一个步骤,至少在  8.05  版本之后  transformation  都一直存在。

网上有很多优化法则,有的说  exists  比  in  效率高,有的说  in  比  exists  执行的快,那就要看  SQL  是如何写的, CBO 是如何转换的,是否能转换?当然这种转换不是基于成本的而是“基于启发的转化”。

当  Oracle  没办法做  transformation  的时候,可能就是  sql  产生问题的时候,此时就要我们去找原因了,下面通过一些案例,说明这种优化器无能为力的情况(为了保护客户的隐私,表名和部分列已经重命名)。

|  用  merge  代替  update

UPDATE  关联更新跑了将近  40 分钟  , SQL  语句如下:

UPDATE PRO_S_ACCT A SET ACCT_SKID = (SELECT ACCT_SKID FROM ACCT_S_BK B WHERE A.ACCT_ID = B.ACCT_ID);

执行计划如下: 

查看量表数据量,  其中  PRO_S_ACCT  有  1044227  行数据, acct_s_bk  有  553554  行数据。

UPDATE  后面跟子查询类似嵌套循环。 pro_s_acct  为嵌套循环的驱动表  , acct_s_bk  为被驱动表  ,那么表  acct_s_bk 就会被扫描  100  多万次,就会产生大量的逻辑读,被驱动表走全表扫描,我们可以在其上面建立索引  ,但是此时索引会被扫描  100  多万次。

下面我们建立索引看其执行计划如下:

create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID);

下面我们通过用  merge into  等价改写   看其执行计划:

merge into PRO_S_ACCT A 
using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
when matched
then update
set a.ACCT_SKID = B.ACCT_SKID;

MERGE INTO  可以自由控制走嵌套循环或者走  hash  连接,并且当驱动表和被驱动表的使用数据超过  1G  时我们   可以开启相应大小的并行  DML  更新  。 

merge /*+PARALLEL(8 )*/ into PRO_S_ACCT A
using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
when matched
then update
set a.ACCT_SKID = B.ACCT_SKID;

实际执行   中, 2s  完成。

下面通过  sql  改写,来让  sql  的执行计划被我们所控制。

UPDATE INXX I 
SET (I.INT_FRM_DT,I.INT_TO_DT,I.ACCT_DESC) = (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC
 FROM DBPP
 WHERE DBPP.SYS_ID= INV 
 AND DBPP.ACCT_TYPE = I.ACCT_TYPE
 AND DBPP.INT_CAT = I.INT_CAT)
WHERE I.EXTDATE = TO_DATE(2018-04-03 , YYYY-MM-DD)
AND EXISTS (SELECT DBPP.SYS_ID
 FROM DBPP
 WHERE DBPP.SYS_ID= INV 
 AND DBPP.ACCT_TYPE = I.ACCT_TYPE
 AND DBPP.INT_CAT = I.INT_CAT
 AND DBPP.ACCT_DESC =  S

merge /*+parallel(10) use_hash(I,X) swap_join_inputs(X)*/ into INXX I 
using (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC,DBPP.ACCT_TYPE,DBPP.INT_CAT FROM DBPP WHERE DBPP.SYS_ID= INV  AND DBPP.ACCT_DESC =  S) x
on (x.ACCT_TYPE = I.ACCT_TYPE AND x.INT_CAT = I.INT_CAT) 
when matched 
then update set I.INT_FRM_DT=x.CR_SOP_DATE,I.INT_TO_DT=x.EOP_DATE,I.ACCT_DESC=x.ACCT_DESC 
WHERE I.EXTDATE = TO_DATE(2018-04-03 , YYYY-MM-DD

另一类似案例:

update WWW a 
set a.cny_bal=a.ll_bal*nvl((select b.hl from MMM b where b.startdate =a.extedate and b.enddate   a.extdate and b.zb= CNY  and
a.curr=b.yb),0)
where a.extdate=to_date( 2018-04-01 , yyyy-mm-dd 
由于 www 表是按天分区,分区字段是 extdate,那么可以起改写成如下:merge /*+parallel(8)*/ into www a 
using (select b.hl from MMM b where b.zb= CNY  and b.enddate date 2018-04-01  and b.startdate =date 2018-04-01) c 
on (a.curr=c.yb) 
when matched 
then update 
set a.cny_bal=a.ll_bal*NVL(c.hl,0) 
where a.extdate=to_date(2018-04-01 , yyyy-mm-dd

|  有关外链接的其他改写  

SELECT 
CASE WHEN 
 NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD) AND A.CUSTNO=B.KHH) 
 AND A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) THEN A.CUSTNO END BQXZ,
CASE THEN 
 NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD) AND A.CUSTNO=B.KHH) 
 AND A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) THEN A.CUSTNO END YE,
 2  AS QD,
SUBSTR(B.OPENBANKNO,1,4) JGM
FROM NB_CCCCCCCCC A 
inner join DZZH_XXXXXXXXXXXXXXXXXX B 
ON A.CUSTNO = B.CUSTNO 
WHERE CUPCHECKSTT IN ( 1 , 2 
685012 rows selected

由于环境是跑批业务,建立索引需要全面考虑,为了不改变当前环境我们尽量不建立索引,执行计划如下: 

实际执行时间  37  分钟完成。

SELECT 
CASE WHEN c.khh is null then A.CUSTNO END BQXZ,
CASE WHEN c.khh is null then A.CUSTNO END ye,
 2  AS QD,
SUBSTR(B.OPENBANKNO,1,4) JGM
from NB_CCCCCCCCC A 
inner join DZZH_XXXXXXXXXXXXXXXXXX B 
ON A.CUSTNO = B.CUSTNO 
left join 
(SELECT KHH FROM NB_XXXXXXXX B WHERE RQ =ADD_MONTHS(TO_DATE( 2018-04-27 , YYYY-MM-DD),-12) AND RQ =TO_DATE(2018-04-27 , YYYY-MM-DD)) c
on A.CUSTNO=c.KHH and A.OPENCUPDATE+365=TO_DATE(2018-04-27 , YYYY-MM-DD) 
where CUPCHECKSTT IN ( 1 , 2 
685012 rows selected

执行计划如下,并且  NB_XXXXXXXX  表只扫描一次,逻辑读由  84 M + 18M  降为  126 ,执行时间也降为秒级(当然下面的数据因多次执行已经在  buffer  中)。 

看完了这篇文章,相信你对“SQL 如何改变那些 CBO 无能为力的执行计划”有了一定的了解,如果想了解更多相关知识,欢迎关注丸趣 TV 行业资讯频道,感谢各位的阅读!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计4060字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)