2018年10月份的一天，历史报表系统的开发人员让我帮忙优化一个每天执行报错ORA-01555的存储过程，由于最近给他们系统处理过其他的几个存储过程，优化后的效率都得到了大幅度提升，只是本人比较懒，没有保存下来优化前后的脚本以及优化思路，听领导说本月底要进行技术分享，于是就想仔细总结下这个存储过程的优化过程。
第一步：先来欣赏一下2017年7月份之前的原始SQL的写法：
UPDATE R_AAAA A
SET (JBBR_CD, JS_BAL, YEBZ_FLG)
= (SELECT M.BRANCH_NO,
NVL(M.CURR_VAL, 0),
CASE WHEN NVL(M.CURR_VAL, 0) < NVL(A.BAK4, 0) THEN
0
ELSE
1
END
FROM JGUAN.BBBB M
WHERE SUBSTR(A.JS_NO, 1, 16) = M.ACCT_NO
AND M.EXTDATE = TO_DATE(kjrq, 'YYYYMMDD'))
WHERE A.DT_COMMIT = kjrq;
先来查询下R_AAAA,JGUAN.BBBB两个表的数据量：
select count(1) from R_AAAA ; --18848326行
select count(1) from JGUAN.BBBB; --34920442行，
开发人员说该表的数据量比较固定，每天都是3500万行左右
再来看一下原始SQL的真实执行计划：
Plan hash value: 269157309
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 6408 | 269K| 157K (13)| 00:31:35 |
| 1 | UPDATE | R_AAAA | | | | |
|* 2 | TABLE ACCESS FULL | R_AAAA | 6408 | 269K| 42527 (1)| 00:08:31 |
|* 3 | TABLE ACCESS BY INDEX ROWID| BBBB_LON | 1 | 33 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_BBBB_LON | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."DT_COMMIT"='20181010')
3 - filter("M"."ACCT_NO"=SUBSTR(:B1,1,16))
4 - access("M"."EXTDATE"=TO_DATE(' 2018-10-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
了解表关联方式的同学应该知道，UPDATE后面跟子查询类似嵌套循环，它的算法与标量子查询，Filter一模一样。也就是说表BBBB_LON相当于嵌套循环的被驱动表，而走了索引范围扫描INDEX RANGE SCAN，相当于该表被扫描了18848326次，这是比较坑的，基本上2个小时是无法出结果的，另外一个比较坑的是，索引范围扫描后的TABLE ACCESS BY INDEX ROWID回表，回表是单块读，这里要消耗多少物理读和逻辑读啊，想想还是比较恐怖的，综合这两个大坑，此条SQL的原始写法，必然导致它每天报错ORA-01555。
在2018年7月份的某天我们开发DBA组的同事用MERGE INTO对此SQL进行了改写，先来讲下merge into的使用场景，原理和优点：
场景：多表关联后，对主表进行update或insert操作时使用。
原理：从using搜出来的结果逐条与on条件匹配，然后决定是update还是insert。档using后面的SQL没有查询到数据时，merge into 语句是不会执行update和insert操作的。
格式：懒得写了，想了解的，自行百度吧。
第二步：来看一看最近的报错信息，我直接截图了：
由上图Query Duration=4437 sec可知，该条语句执行了一个多小时，没有执行完毕就报错了。我找到了它的执行计划，如下：
SQL_ID 1c6x55j4a1u92, child number 0
-------------------------------------
MERGE INTO R_AAAA A USING (SELECT M.BRANCH_NO,
NVL(M.CURR_VAL, 0) CURR_VAL, M.ACCT_NO FROM JGUAN.BBBB M WHERE
M.EXTDATE = TO_DATE(:B1 , 'YYYYMMDD')) M ON (SUBSTR(A.JS_NO, 1, 16) =
M.ACCT_NO) WHEN MATCHED THEN UPDATE SET JBBR_CD = M.BRANCH_NO, JS_BAL =
M.CURR_VAL, YEBZ_FLG = CASE WHEN NVL(M.CURR_VAL, 0) < NVL(A.BAK4, 0)
THEN 0 ELSE 1 END
Plan hash value: 938640974
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 42715 (100)| |
| 1 | MERGE | R_AAAA | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1 | 146 | 42715 (1)| 00:08:33 |
| 4 | TABLE ACCESS BY INDEX ROWID| BBBB | 1 | 34 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_BBBB_EXTDATE | 1 | | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | R_AAAA | 9542K| 1019M| 42658 (1)| 00:08:32 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."ACCT_NO"=SUBSTR("A"."JS_NO",1,16))
5 - access("M"."EXTDATE"=TO_DATE(:B1,'YYYYMMDD'))
有执行计划分析可知，两表关联方式为HASH连接，驱动表虽然通过INDEX RANGE SCAN索引范围扫描来访问表，但是该表的数据量可是3500万行，回表一不小心就要回表3500万次，回表在访问UNDO表空间时，找不到原来的数据，自然就会报错ORA-01555了。
**话说回来，MERGE INTO的好处还是不少的，只是此处不该有回表操作，所以考虑避免回表，让访问表BBBB时可以走全表扫描。**关于这个想法可以执行一下，看看效率再说，具体执行计划如下：(此种改写方法执行时间只消耗了400多秒，不到7分钟)
MERGE/*+ use_hash(a,b) leading(a) */ INTO R_AAAA A
USING (SELECT /*+ full(M) qb_name(b) */ M.BRANCH_NO,
NVL(M.CURR_VAL, 0) CURR_VAL,
M.ACCT_NO
FROM JGUAN.BBBB M
WHERE M.EXTDATE = TO_DATE('20181023', 'YYYYMMDD')) mm
ON (SUBSTR(A.JS_NO, 1, 16) = mm.ACCT_NO)
WHEN MATCHED THEN
UPDATE
SET JBBR_CD = mm.BRANCH_NO,
JS_BAL = mm.CURR_VAL,
YEBZ_FLG = CASE
WHEN NVL(mm.CURR_VAL, 0) < NVL(A.BAK4, 0) THEN
0
ELSE
1
END;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 64 | 1023K (1)| 03:24:39 |
| 1 | MERGE | R_AAAA | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1 | 64 | 1023K (1)| 03:24:39 |
| 4 | TABLE ACCESS FULL| R_AAAA | 9542K| 272M | 42658 (1)| 00:08:32 |
|* 5 | TABLE ACCESS FULL| BBBB | 1 | 34 | 961K (1)| 03:12:20 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MM"."ACCT_NO"=SUBSTR("A"."JS_NO",1,16))
5 - filter("M"."EXTDATE"=TO_DATE(' 2018-10-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
MERGE INTO可以自由控制表关联方式走嵌套循环或是HASH连接，而且MERGE INTO可以开启并行DML，并行查询，但数据量大些，这样的更新也是个大事务，对UNDO的占用较大较长，只有到最后COMMIT的时候才会释放UNDO空间；
另外进程突然断开连接，MERGE INTO更新连接就会导致UNDO很难释放。但是我们可以考虑PLSQL语言进行批量更新，采用PLSQL更新不需要担心进程突然断开连接，对UNDO占用也小，相对而言更新过程比较安全、平稳。
第三步：就是PLSQL批量更新的写法，把这段代码放在原有的存储过程中执行，据开发人员反馈说第二天跑批时只花了23分钟跑完了整个存储过程，整个存储过程里不仅有删除，insert，查询，还有这个update，这个批量更新耗时不到10分钟。改写前整个存储过程每天都因报错停止一次，重跑还要一个多小时才能完成，改写完成后，第二天跑批没有再报错，也无需再重跑，因此这个效率的提升还是很可喜的。
declare
--加上HINT: /*+ full(m) use_hash(a,m) leading(a)*/ 这个查询执行时间是3分30秒， 不加执行了7分钟都没出结果
CURSOR cur_update is
select/*+ use_hash(A,M) */ A.rowid row_id, M.branch_no,nvl(M.CURR_VAL,0) curr_val, M.acct_no
from R_AAAA A, JGUAN.BBBB M
where SUBSTR(A.js_no,1,16) = M.acct_no
and M.extdate = to_date(:ywdate, 'YYYYMMDD') order by A.rowid;
TYPE rec_invm_type IS RECORD(
row_id varchar2(50),
branch_no JGUAN.BBBB.branch_no%TYPE,
curr_val JGUAN.BBBB.curr_val%TYPE,
acct_no JGUAN.BBBB.acct_no%TYPE
) ;
TYPE ty_invm IS TABLE OF rec_invm_type;
tab_invm ty_invm;
begin
OPEN cur_update;
LOOP
FETCH cur_update BULK COLLECT INTO tab_invm LIMIT 20000;
EXIT WHEN tab_invm.COUNT = 0;
FORALL i in tab_invm.FIRST..tab_invm.LAST
update R_AAAA R
set JBBR_CD = tab_invm(i).branch_no,
JS_BAL = tab_invm(i).curr_val,
YEBZ_FLG = case when nvl(tab_invm(i).curr_val, 0) < nvl(R.BAK4, 0) then
0
else
1
END
where R.rowid = tab_invm(i).row_id;
commit;
END LOOP;
CLOSE cur_update;
end;
以上存储过程中，在定义CURSOR时，两表关联的执行计划如下；
--加上full(m) 执行时间是3:30秒， 不加执行了5分钟不出结果
select /*+ full(m) use_hash(a,m) leading(a)*/
a.rowid row_id, m.branch_no, nvl(m.curr_val, 0) curr_val, m.acct_no
from R_AAAA a, JGUAN.BBBB m
where substr(a.js_no, 1, 16) = m.acct_no
and m.extdate = to_date('20181023', 'yyyymmdd')
order by a.rowid;
Plan hash value: 1480194564
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | | 1023K (1)| 03:24:39 |
| 1 | SORT ORDER BY | | 1 | 64 | | 1023K (1)| 03:24:39 |
|* 2 | HASH JOIN | | 1 | 64 | 382M| 1023K (1)| 03:24:39 |
| 3 | TABLE ACCESS FULL| R_AAAA | 9542K| 272M| | 42585 (1)| 00:08:32 |
|* 4 | TABLE ACCESS FULL| BBBB | 1 | 34 | | 961K (1)| 03:12:20 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("M"."ACCT_NO"=SUBSTR("A"."JS_NO",1,16))
4 - filter("M"."EXTDATE"=TO_DATE(' 2018-10-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
这个查询的瓶颈也是回表，增加HINT后，避免回表，同时走HASH连接，大大的提升了执行效率。
到此，对于UPDATE的改写，是选择MERGE INTO(仍旧是大事务)还是PLSQL批量更新(事务粒度化)来实现，读者可以根据自己的数据量，以及UNDO表空间的大小来决定吧。
1688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
