定期清理 cmpp_submit 表中已處理或超時重試的歷史數據,歸檔至備份表 cmpp_submit_backup,每 1000 條提交一次事務,異常時回滾並記錄日志。
CREATE OR REPLACE PROCEDURE cmpp_submit_remove_procedure
IS
d_end_time DATE; -- 處理時間切點
i_id NUMBER; -- 游標變量:記錄 ID
i_count NUMBER := 0; -- 批次計數器
-- 定義參數化游標:處理 5 分鐘前的非活躍/超時記錄
CURSOR c_cmpp_submit (p_end_time DATE) IS
SELECT id
FROM cmpp_submit
WHERE (ih_process <> 'insert_cmpp_submit'
AND ih_process <> 'wait_for_response')
OR (ih_process = 'wait_for_response' AND ih_retry <= 0)
AND ih_timestamp < p_end_time
AND ROWNUM < 100001; -- 單次最多處理 10 萬條
BEGIN
-- 1. 確定處理時間點:截取到分鐘 (處理 5 分鐘前數據)
-- d_end_time := TRUNC(SYSDATE - 1/288, 'MI'); -- 1/288 天 = 5 分鐘
d_end_time := TRUNC(SYSDATE, 'MI'); -- 或處理當前分鐘前數據
-- 2. 打開游標
OPEN c_cmpp_submit(d_end_time);
-- 3. 循環處理
LOOP
FETCH c_cmpp_submit INTO i_id;
EXIT WHEN c_cmpp_submit%NOTFOUND;
i_count := i_count + 1;
-- 歸檔:複製到備份表
INSERT INTO cmpp_submit_backup SELECT * FROM cmpp_submit WHERE id = i_id;
-- 清理:刪除原表記錄
DELETE FROM cmpp_submit WHERE id = i_id;
-- 4. 每 1000 條批量提交 (減少 Undo 積壓、釋放鎖、控制事務大小)
IF i_count = 1000 THEN
COMMIT;
i_count := 0; -- 重置計數器
END IF;
END LOOP;
-- 5. 提交剩餘未滿 1000 的記錄
COMMIT;
-- 6. 關閉游標
CLOSE c_cmpp_submit;
RETURN;
EXCEPTION
WHEN OTHERS THEN
-- 異常處理:回滾、關閉游標、記錄日志 (此處簡化為空處理)
ROLLBACK;
IF c_cmpp_submit%ISOPEN THEN
CLOSE c_cmpp_submit;
END IF;
-- 實際項目中應記錄錯誤碼、錯誤信息、調用棧至日志表
-- INSERT INTO proc_error_log VALUES (SYSDATE, 'cmpp_submit_remove', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE; -- 重拋異常讓調用方感知
END cmpp_submit_remove_procedure;
/
SHOW ERRORS
CURSOR c_cmpp_submit (p_end_time DATE) IS
SELECT id FROM cmpp_submit WHERE ... AND ih_timestamp < p_end_time;
-- 打開時傳參:OPEN c_cmpp_submit(d_end_time);
-- 優勢:解析一次,多次執行;避免硬解析;防止 SQL 注入
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- 回滾未提交變更
IF c_cmpp_submit%ISOPEN THEN -- 安全關閉游標
CLOSE c_cmpp_submit;
END IF;
-- 記錄錯誤上下文 (生產必備)
INSERT INTO proc_error_log (proc_name, error_code, error_msg, call_stack, created_at)
VALUES ('cmpp_submit_remove', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, SYSDATE);
RAISE; -- 重拋,讓調度器/監控捕獲告警
END;
-- 替換循環 FETCH,批量獲取 ID 數組
DECLARE
TYPE id_array_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_ids id_array_t;
BEGIN
OPEN c_cmpp_submit(d_end_time);
LOOP
FETCH c_cmpp_submit BULK COLLECT INTO l_ids LIMIT 1000;
EXIT WHEN l_ids.COUNT = 0;
-- 批量歸檔
FORALL i IN 1..l_ids.COUNT
INSERT INTO cmpp_submit_backup
SELECT * FROM cmpp_submit WHERE id = l_ids(i);
-- 批量刪除
FORALL i IN 1..l_ids.COUNT
DELETE FROM cmpp_submit WHERE id = l_ids(i);
COMMIT;
END LOOP;
CLOSE c_cmpp_submit;
END;
性能提升:上下文切換從 N 次降為 N/1000 次,大表處理速度提升 10-50 倍。
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_CMPP_SUBMIT_CLEANUP',
job_type => 'STORED_PROCEDURE',
job_action => 'cmpp_submit_remove_procedure',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', -- 每 5 分鐘
enabled => TRUE,
comments => '清理 CMPP 提交表歷史數據,歸檔備份'
);
END;
/
-- 查看執行歷史
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'JOB_CMPP_SUBMIT_CLEANUP';
DELIMITER //
CREATE PROCEDURE sp_cmpp_submit_cleanup()
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_id BIGINT;
DECLARE v_count INT DEFAULT 0;
DECLARE v_end_time DATETIME DEFAULT DATE_SUB(NOW(), INTERVAL 5 MINUTE);
DECLARE cur CURSOR FOR
SELECT id FROM cmpp_submit
WHERE (ih_process NOT IN ('insert_cmpp_submit','wait_for_response')
OR (ih_process = 'wait_for_response' AND ih_retry <= 0))
AND ih_timestamp < v_end_time
LIMIT 100000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
START TRANSACTION;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id;
IF v_done THEN LEAVE read_loop; END IF;
INSERT INTO cmpp_submit_backup SELECT * FROM cmpp_submit WHERE id = v_id;
DELETE FROM cmpp_submit WHERE id = v_id;
SET v_count = v_count + 1;
IF v_count >= 1000 THEN
COMMIT;
START TRANSACTION;
SET v_count = 0;
END IF;
END LOOP;
COMMIT;
CLOSE cur;
END //
DELIMITER ;
V$SESSION_LONGOPS 追蹤長運行過程;DBA_SCHEDULER_JOB_RUN_DETAILS 監控調度成功率cmpp_submit_backup 按月分區,便於歷史數據歸檔/刪除cmpp_submit(ih_process, ih_retry, ih_timestamp) 組合索引加速游標查詢