分类: Oracle

2018-05-07 15:26:33


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

网上有很多优化法则,有的说existsin效率高,有的说inexists执行的快,那就要看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_ACCT1044227行数据,acct_s_bk553554行数据。



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、数据库备份恢复及迁移经验。

 
阅读(302) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册

/bbsy/124l.html /gpw/806j.html /ks/512O.html /bosoo/021m.html /gupiao/355w.html /ymw/122j.html /1088/057Z.html /bbsy/775T.html /ggs/708o.html /55901/433Z.html /gpw/283y.html /123/406v.html /gupiao/601Y.html /888/287Z.html /wb/258D.html /daxue/114w.html /bg88/784J.html /88410/210E.html /bbs/655U.html /bosoo/016i.html /boss/380d.html /boss/125x.html /ggs/160E.html /ggs/075w.html /bbs/842E.html /88410/846v.html /ks/518f.html /888/773m.html /ks/403F.html /bosoo/703F.html /888/146B.html /bbs/238h.html /bbs/240t.html /pmw/118J.html /ggs/542A.html /8888/811m.html /3088/618i.html /boss/082j.html /gpw/522t.html /bbsy/404p.html /bbs/878u.html /bbsy/408L.html /bosoo/171g.html /gupiao/104J.html /bosoo/001p.html /bg88/506z.html /gpw/588o.html