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表空间的大小来决定吧。