1、优化前的SQL,修改需要5小时
CREATE OR REPLACE PROCEDURE UPDATE_UserInfo_AnyField AS
-- LOCAL VARIABLES HERE
K VARCHAR2(5);
BEGIN
-- TEST STATEMENTS HERE
FOR I IN 1 .. 100 LOOP
K := TO_CHAR(I-1, '00');
UPDATE /*+ nologging */ T_BSS_USERINFO T
SET T.Validity = NULL
WHERE T.PARTCOL_USERNO = TO_NUMBER(K);
COMMIT;
END LOOP;
END UPDATE_UserInfo_AnyField;
/
2、优化后的存储过程如下:
CREATE OR REPLACE PROCEDURE Update_Batch_Try AS
TYPE User_RowId IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
u_tab User_RowId;
v_BeginTime VARCHAR2(10);
v_EndTime VARCHAR2(10);
v_TotalTime NUMBER;
v_sum INTEGER; --计数器
v_errRecord NUMBER; --倒入失败的记录数
err_code NUMBER;
err_msg VARCHAR2(200);
v_Pos BINARY_INTEGER;
v_StrSql0 VARCHAR2(512);
v_StrSql1 VARCHAR2(256);
TYPE CURSOR_TYPE IS REF CURSOR;
UserInfo_Cursor CURSOR_TYPE;
BEGIN
v_errRecord := 0;
v_sum := 0;
SELECT TO_CHAR(SYSTIMESTAMP, 'sssss')
INTO v_BeginTime
FROM DUAL;
v_StrSql0 := 'CREATE TABLE T_USERINFO_ROWID NOLOGGING AS
SELECT A.RID FROM (
SELECT T.ROWID AS RID
FROM T_BSS_USERINFO T
WHERE (T.LEVEL_ID = ''1'' OR T.LEVEL_ID = ''2'')
ORDER BY T.ROWID
) A';
BEGIN
EXECUTE IMMEDIATE v_StrSql0;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
v_StrSql1 := 'SELECT T.RID FROM T_USERINFO_ROWID T';
OPEN UserInfo_Cursor FOR v_StrSql1;
LOOP
FETCH UserInfo_Cursor BULK COLLECT INTO u_tab LIMIT 2000; --每次取2000条记录
IF u_tab.COUNT > 0 THEN
BEGIN
FORALL i IN 1..u_tab.COUNT SAVE EXCEPTIONS --利用临时表批量导入
UPDATE /*+ nologging */ T_BSS_USERINFO T
SET T.Validity = NULL
WHERE T.ROWID=u_tab(i);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
COMMIT;
v_errRecord := v_errRecord + SQL%BULK_EXCEPTIONS.COUNT;
FOR x IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
v_Pos := SQL%BULK_EXCEPTIONS(x).ERROR_INDEX;
err_code := -SQL%BULK_EXCEPTIONS(x).ERROR_CODE;
err_msg := SQLERRM(err_code);
DBMS_OUTPUT.PUT_LINE('batch update failure:['||to_char(u_tab(v_Pos))||']:['|| TO_CHAR(err_code) ||']:[' || err_msg || ']');
END LOOP;
END;
v_sum := v_sum + u_tab.COUNT;
u_tab.DELETE;
END IF;
EXIT WHEN UserInfo_Cursor%NOTFOUND;
END LOOP;
CLOSE UserInfo_Cursor;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE T_USERINFO_ROWID';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
SELECT TO_CHAR(SYSTIMESTAMP, 'sssss')
INTO v_EndTime
FROM DUAL;
v_TotalTime := TO_NUMBER(v_EndTime) - TO_NUMBER(v_BeginTime);
DBMS_OUTPUT.PUT_LINE('total batch update user info datas===>['||TO_CHAR(v_sum)||']pieces'||' failure records===>['||TO_CHAR(v_errRecord)||']pieces' || ' total time:[' || TO_CHAR(v_TotalTime)||']seconds!!!');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
IF UserInfo_Cursor%ISOPEN THEN
CLOSE UserInfo_Cursor;
END IF;
IF u_tab.COUNT > 0 THEN
u_tab.DELETE;
END IF;
err_code := SQLCODE;
err_msg := SUBSTR(SQLERRM,1,200);
DBMS_OUTPUT.PUT_LINE('batch update failure:['||to_char(err_code)||']'||err_msg);
END;
/
结论、解决方案及效果
使用上述优化方法可以发现通过对存储过程的优化,可以使Oracle数据库在更新大数据量的效率提高30倍,最根本的优化原理是使用空间来换取时间的做法,充分利用了Oracle的ROWID,批量更新等方法来提高更新大数据量的执行效率。
经验总结、预防措施和规范建议
1, 处理速度加快的原因,是因为使用ORDER BY ROWID,按块顺序处理数据,很大程度上减少了物理读,同时使用临时表存储更新记录的ROWID,占有了一定的表空间,所以在使用该优化存储过程之前,需要一定的空闲的表空间。
2, 为了快速生成更新的ROWID临时数据,使用了CTAS(Create Table As Select)的操作方式,如果可以使用NOLOGGING选项,可以进一步提高CTAS操作的执行效率。
3, 为了使用临时表名,该存储过程使用的动态游标(OPEN UserInfo_Cursor FOR v_StrSql1;)。
4, 使用了Oracle数据库的数组,将数据量以FETCH UserInfo_Cursor BULK COLLECT INTO u_tab LIMIT 2000;批量形式读入到数据库的内存,同时使用Bulk Binds(FORALL) 的特性,批量处理,大大降低了主机的资源压力(以2000为单位,批量处理)。