记一次卓有成效的SQL优化

最新推荐文章于 2025-07-22 20:52:14 发布

csdc1233

最新推荐文章于 2025-07-22 20:52:14 发布

阅读量292

点赞数 1

文章标签：数据库

本文探讨了如何优化一个复杂Oracle过程的性能，通过调整执行计划和设置工作区大小策略，显著减少了物理读取操作。优化后，过程执行效率提升，磁盘I/O压力减轻。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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，现在总算有点暗淡了。