oracle大数据量更新sql优化建议

八芯
506 阅读1分钟

近期组内有个性能优化需求，针对大数据表联表查询数据后更新进行性能优化。优化方案是新增联合查询的表，并对联合查询的表进行分区，每次按分区进行查询数据更新到主表。结果优化完以后以前半小时能处理完的跑批，现在5、6个小时都处理不完了。好家伙挖完坑了，找笔者来填,没办法为了赚钱只能含泪接下。经过一番折腾，也是解决了问题。分享一下具体场景以及解决方案。

这是当前的sql语句(使用mybatis)，可以看到是从B表的分区表中查一些信息然后关联biz_id更新到A表。

update a_table a
set a.status = '020',
  a.update_time = sysdate,
  a.start_date = (select nvl(b.start_date, a.start_date) 
                         from b_table partition(${partName}) b 
					     where b.serial_id = #{serialId}, and b.file_type = #{fileType} and b.biz_id = a.biz_id),

  a.due_date = (select nvl(b.due_date, a.due_date) 
                       from b_table partition(${partName}) b 
					    where b.serial_id = #{serialId}, and b.file_type = #{fileType} and b.biz_id = a.biz_id)
where exists (select * from b_table partition(${partName}) b
                 where b.serial_id = #{serialId}, and b.file_type = #{fileType} and b.biz_id = a.biz_id)

可以看到最终是根据exist来处理更新的，但是每个更新语句里还要单开一个查询语句，不清楚oracle怎么做解析优化的，如果没有额外优化的，这个每个查询还可能涉及到挂起上和下文切换，肯定不会快。

B表总共符合条件的有15w数据，A表中20w,按照这个sql执行，5个小时还没跑完。这才15w，我真是两眼一黑。

之后，同事根据上面的sql优化了一版

UPDATE /*+ parallel(t,8) */
  a_table a
SET (
  a.status,
  a.update_time,
  a.start_date,
  a.due_date)
  = (
  SELECT 
    '020', 
    sysdate,
    nvl(b.start_date, a.start_date) start_date,
    nvl(b.due_date, a.due_date) due_date
  FROM 
    b_table PARTITION(${partName}) b
  WHERE
    b.serial_id = #{serialId}
    AND b.file_type = #{fileType}
    AND b.invoice_id = a.invoice_id
  )
WHERE 
  EXISTS(
    SELECT biz_id
	FROM b_table PARTITION(${partName}) b
	WHERE 
	  b.serial_id = #{serialId}
	  AND b.file_type = #{fileType}
          AND b.biz_id = a.biz_id
  )

加了并行执行以及查询不再一个字段查一次了。好家伙，这版优化完执行5个小时的都执行不完的逻辑，现在2小时之内可以搞定了。可惜连之前30分钟的边边都没摸到，高低没法给他打到及格分。

没办法，高手都是最后时刻出场的。分析sql以及动手执行可以发现，查询语句本身不慢，性能瓶颈还是在更新语句上。当前更新语句是根据biz_id做关联查询，那肯定也是根据biz_id做更新的。放心，索引啥的早加了，在15w数据这档对执行效率影响基本没有啥影响。oracle是根据每个数据块加到data buffer cache里的，如果根据biz_id去更新，每次都在一个块里的概率比较低，假设这次大数量更新运气不大好，data buffer cache里就是找不到需要的数据，每次都要从磁盘上读块然后再更新，那效率自然就低了。所以有什么办法能尽量从缓存里读块呢。使用oracle的伪列rowid。将查询出来的结果用rowid排序，这样子更新时就会在连续块区域更新了。

230157344063991.png

这是优化过后的第一版sql

declare cursor cur_t2 is
   SELECT 
     a.ROWID row_id,
	 nvl(tt.INSURE_START_DATE, t.insure_start_date) start_date,
	 nvl(tt.policy_expire_date, t.insure_due_date) due_date	 
   FROM 
      b_table PARTITION(${partName}) b, a_table a
   WHERE 
     b.serial_id = #{serialId}
     AND b.file_type = #{fileType}
     AND b.invoice_id = a.invoice_id
	 order by row_id
begin
  for row_t2 in cur_t2 loop
    update
	  a_table a
	set
	  a.status = '020',
	  a.update_time = sysdate,
	  a.start_date = row_t2.start_date,
	  t.due_date = row_t2.due_date
	where rowid = row_t2.row_id;
  end loop;
end;

主要就是通过 order by row_id，把row_id排序后，这样子更新时命中缓存里的块概率就高多了。 执行一下，15w数据15s全部跑完，非常完美。 继续优化，使用MERGE INTO + ROWID进行更新，15w数据7s就跑完了。 感觉超额完成任务了。直接喝茶、上厕所、刷淘宝摸鱼三件套安排上。

avatar
文章
阅读
粉丝