ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 记一次卓有成效的SQL优化

记一次卓有成效的SQL优化

原创 Linux操作系统 作者:microsoft_fly 时间:2012-06-02 14:24:39 0 删除 编辑

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中的记录,或插入,或更新,或删除,比较的SQL3个。这3SQL大致的包含以下几个重要的操作

8个排序操作,其中6"LAG OVER",一个"MIN OVER",还有一个"ROW_NUMBER OVER",通过v$sessstatv$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,比如我们的pga2.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:
结束的地方也可以不设置,因为会话结束后会自动释放,但是手动释放相对严谨。

 

再次执行过程跟踪发现物理读写都为0v$sessstatv$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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-06

  • 博文量
    41
  • 访问量
    99330