SQL优化案例-改变那些CBO无能为力的执行计划（一）

心已赠人 2022-05-25 07:29 173阅读 0赞

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

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

当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);

执行计划如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

查看量表数据量，其中PRO\_S\_ACCT有1044227行数据，acct\_s\_bk有553554行数据。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

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);

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

下面我们通过用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;

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

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;

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

实际执行中，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');

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

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');

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

**另一类似案例：**

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

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

实际执行时间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表只扫描一次，逻辑读由84M\+18M降为126，执行时间也降为秒级（当然下面的数据因多次执行已经在buffer中）。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 9][]

--------------------

**|  作者简介**

### 姚崇·沃趣科技高级数据库技术专家 ###

### 熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。 ###

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220525/aec85ae4770f4cf2a400564e8194a8a0.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220525/c2d356fefabd4126adf6d370d494a767.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2]: /images/20220525/0c2e1094314e46e999cb9ccd8c5280ba.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3]: /images/20220525/9ca3e3d18b4a49ecb02ff0e1635615bd.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4]: /images/20220525/b96264958c974e07bef4538eee52b6b2.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5]: /images/20220525/6d30d7acd72d40bbb9cf5ebcd7c2e015.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6]: /images/20220525/93d8755007eb4f2c9a89b54e3984ecaf.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7]: /images/20220525/33a958a984354c059760b9b90ea686ad.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8]: /images/20220525/bd86757534b7485992c98e7b33d6fb6e.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 9]: /images/20220525/05ddcbca8b2949a48b2afaf875d911dd.png