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语句如下：

点击(此处)折叠或打开

1. 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多万次。

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

点击(此处)折叠或打开

1. create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID);

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

实际执行中， 2s完成。

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

点击(此处)折叠或打开

1. UPDATE INXX I
   
2. SET (I.INT_FRM_DT,I.INT_TO_DT,I.ACCT_DESC) = (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC
   
3.                                     FROM DBPP
   
4.                                    WHERE DBPP.SYS_ID='INV'
   
5.                                      AND DBPP.ACCT_TYPE = I.ACCT_TYPE
   
6.                                      AND DBPP.INT_CAT = I.INT_CAT)
   
7. WHERE I.EXTDATE = TO_DATE('2018-04-03','YYYY-MM-DD')
   
8. AND EXISTS (SELECT DBPP.SYS_ID
   
9.       FROM DBPP
   
10.      WHERE DBPP.SYS_ID='INV'
    
11.        AND DBPP.ACCT_TYPE = I.ACCT_TYPE
    
12.        AND DBPP.INT_CAT = I.INT_CAT
    
13.        AND DBPP.ACCT_DESC = 'S');

点击(此处)折叠或打开

1. merge /*+parallel(10) use_hash(I,X) swap_join_inputs(X)*/ into INXX I
   
2. 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
   
3. on (x.ACCT_TYPE = I.ACCT_TYPE AND x.INT_CAT = I.INT_CAT)
   
4. when matched
   
5. then update set I.INT_FRM_DT=x.CR_SOP_DATE,I.INT_TO_DT=x.EOP_DATE,I.ACCT_DESC=x.ACCT_DESC
   
6. WHERE I.EXTDATE = TO_DATE('2018-04-03','YYYY-MM-DD');

另一类似案例：

点击(此处)折叠或打开

1. update WWW a
   
2. 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
   
3. a.curr=b.yb),0)
   
4. where a.extdate=to_date('2018-04-01','yyyy-mm-dd');
   
5. 由于www表是按天分区，分区字段是extdate，那么可以起改写成如下：
   
6. merge /*+parallel(8)*/ into www a
   
7. 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
   
8. on (a.curr=c.yb)
   
9. when matched
   
10. then update
    
11. set a.cny_bal=a.ll_bal*NVL(c.hl,0)
    
12. where a.extdate=to_date('2018-04-01','yyyy-mm-dd');

| 有关外链接的其他改写

点击(此处)折叠或打开

1. SELECT
   
2. CASE WHEN
   
3.   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)
   
4.                AND A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD') THEN A.CUSTNO END BQXZ,
   
5. CASE THEN
   
6.   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)
   
7.                AND A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD') THEN A.CUSTNO END YE,
   
8. '2' AS QD,
   
9. SUBSTR(B.OPENBANKNO,1,4) JGM
   
10. FROM NB_CCCCCCCCC A
    
11. inner join DZZH_XXXXXXXXXXXXXXXXXX B
    
12. ON A.CUSTNO = B.CUSTNO
    
13. WHERE CUPCHECKSTT IN ('1','2');
    
14. 685012 rows selected

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


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

点击(此处)折叠或打开

1. SELECT
   
2. CASE WHEN c.khh is null then A.CUSTNO END BQXZ,
   
3. CASE WHEN c.khh is null then A.CUSTNO END ye,
   
4. '2' AS QD,
   
5. SUBSTR(B.OPENBANKNO,1,4) JGM
   
6. from NB_CCCCCCCCC A
   
7. inner join DZZH_XXXXXXXXXXXXXXXXXX B
   
8. ON A.CUSTNO = B.CUSTNO
   
9. left join
   
10. (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
    
11. on A.CUSTNO=c.KHH and A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD')
    
12. where CUPCHECKSTT IN ('1','2');
    
13. 685012 rows selected

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




|  作者简介

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28218939/viewspace-2154052/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28218939/viewspace-2154052/