记一次卓有成效的SQL优化

1.1      环境

Oracle 9i 9.2.0.4 64 bit

Linux as 4.7

1.2      优化对象：一个过程

pkg_lr_dt01.p_update_tb021

业务非常复杂，代码很长，这里就不贴了。

这个过程中的SQL大致的意思是通过视图v_jt_tb021来更新表tb021中的数据，v_jgb_tb021中有7个表关联，表中的记录数：
select count(*) from  tb_pub103;--145938
select count(*) from  tb003;--302457
select count(*) from  tb008;--304167
select count(*) from  tb011;--335287
select count(*) from  tb_pub205;--49460
select count(*) from  tb002;--7140
select count(*) from  tb010;--806

通过v_jt_tb021中的各个字段与已生成的表tb021现有记录进行各个字段的一一比较，看是否需要更新tb021中的记录，或插入，或更新，或删除，比较的SQL有3个。这3个SQL大致的包含以下几个重要的操作

有8个排序操作，其中6个"LAG OVER"，一个"MIN OVER",还有一个"ROW_NUMBER OVER"，通过v$sessstat和v$statname跟踪发现每次执行需要排序的工作量“sorts (rows) 3355663“。

1.3      优化过程

1.3.1    原始执行计划

SELECT STATEMENT, GOAL = CHOOSE           Cost=49092 Cardinality=1 IO cost=49092

 HASH JOIN           Cost=49092 Cardinality=1 IO cost=49092

  TABLE ACCESS FULL  Object wner=JDB  Object name=TB021 Cost=923   Cardinality=159136   IO cost=923

  VIEW Object wner=JDB     Cost=39969 Cardinality=330413   

   WINDOW SORT PUSHED RANK         Cost=39969 Cardinality=330413   IO cost=39969

    WINDOW SORT         Cost=39969 Cardinality=330413   IO cost=39969

     HASH JOIN          Cost=7202  Cardinality=330413   IO cost=7202

      TABLE ACCESS FULL Object wner=JDB  Object name=TB010 Cost=3 Cardinality=797   IO cost=3

      HASH JOIN OUTER          Cost=7175  Cardinality=293087   IO cost=7175

       HASH JOIN OUTER         Cost=5739  Cardinality=235732   IO cost=5739

        HASH JOIN           Cost=4760  Cardinality=218201   IO cost=4760

         TABLE ACCESS FULL  Object wner=JDB  Object name=TB_PUB103    Cost=710   Cardinality=72923   IO cost=710

         HASH JOIN          Cost=3198  Cardinality=210086   IO cost=3198

          TABLE ACCESS FULL Object wner=JDB  Object name=TB003 Cost=1684  Cardinality=149810  IO cost=1684

          HASH JOIN         Cost=882   Cardinality=169211   IO cost=882

           TABLE ACCESS FULL   Object wner=JDB  Object name=PUB205   Cost=183   Cardinality=38050   IO cost=183

           TABLE ACCESS FULL   Object wner=JDB  Object name=TB011 Cost=504   Cardinality=164180  IO cost=504

        TABLE ACCESS FULL   Object wner=JDB  Object name=TB002 Cost=19    Cardinality=6690    IO cost=19

       TABLE ACCESS FULL    Object wner=JDB  Object name=TB008 Cost=231   Cardinality=149596  IO cost=231

通过执行计划发现几个表之间的关联采用的时hash_join的连接方式，单个SQL的执行时间也不是很长，在10秒内能执行完，各个表都用了全表扫描，原因很简单，因为过滤条件很少，要有的话过滤的数据不多，所以用全表扫描的方式效率相对较高，考虑到是每5分钟执行一次，频率有点高，所以先把表缓存到缓存中，执行如下操作：“alter table table_name storage(buffer_pool keep);”

1.3.2    缓存表之后

经过上述缓存之后，发现仍然有大量的物理读，每次执行都在16W多，对磁盘压力还是很大，通过10053跟踪看到有大量的'direct path write' 和'direct path read'等待，具体如下： 

WAIT #1: nam='direct path write' ela= 9 p1=501 p2=3237513 p3=31

WAIT #1: nam='direct path write' ela= 3 p1=501 p2=3237575 p3=31

WAIT #1: nam='direct path write' ela= 4 p1=501 p2=3400713 p3=31

WAIT #1: nam='direct path write' ela= 3 p1=501 p2=3400775 p3=31

......

WAIT #1: nam='direct path read' ela= 2 p1=501 p2=3454887 p3=1

WAIT #1: nam='direct path read' ela= 2 p1=501 p2=3454888 p3=1

WAIT #1: nam='direct path read' ela= 1 p1=501 p2=3198665 p3=1

WAIT #1: nam='direct path read' ela= 1 p1=501 p2=3198666 p3=1

果然验证了当初的想法，排序引起了大量的物理读和逻辑读，而对表本身的查询并没有产生物理读。

1.3.3    对session设置workarea_size_policy=MANUAL

回顾一下基础，在oracle 9i后，workarea_size_policy默认为"auto",在workarea_size_policy=AUTO的情况下，所有会话的UGA共用pga_aggregate_target定义的内存，不必单独设定sort_area_size,单个session最大能申请到5%的pga，比如我们的pga是2.4G，那么意味着一个session最大能能用到120M的内存，但这里有“sorts (rows) 3355663“，内存不够用，所以只能先输出到temp上去了，等下用的时候还得把temp上的结果“read”回来，所以产生了大量的物理读写。

因此对session设置私有的sort_area_size，即在procedure中设置：
procedure开始的地方：
   EXECUTE IMMEDIATE 'ALTER session SET workarea_size_policy=manual';
     EXECUTE IMMEDIATE 'ALTER session SET sort_area_size=419430400';
procedure结束的地方：
    EXECUTE IMMEDIATE 'ALTER session SET sort_area_size=0';
       EXECUTE IMMEDIATE 'ALTER session SET workarea_size_policy=auto';
PS:结束的地方也可以不设置，因为会话结束后会自动释放，但是手动释放相对严谨。

 

再次执行过程跟踪发现物理读写都为0（v$sessstat和v$statname关联起来可以看），通过iostat监控发现tps大幅降低，

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 171.19 805.30 1486.09 2432 4488
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 171.19 805.30 1486.09 2432 4488

avg-cpu: %user %nice %sys %iowait %idle
          20.75 0.00 4.17 0.42 74.67

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 236.79 588.63 2145.82 1760 6416
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 236.79 588.63 2145.82 1760 6416

avg-cpu: %user %nice %sys %iowait %idle
          16.83 0.00 3.50 0.33 79.34

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 102.66 252.49 1142.86 760 3440
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 102.66 252.49 1142.86 760 3440
原来空的时候都要400~800质检，忙的时候要过1500，现在总算有点暗淡了。

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

转载于:http://blog.itpub.net/751051/viewspace-731725/