如何處理業務系統中併發比較高的表資料清理工作

1,SQL> set sqlprompt session_130>
session_130>insert into t_free select * from t_free;
2,session_8>truncate table t_free;
truncate table t_free
               *
第 1 行出現錯誤:
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效

說明:如果表上有dml操作時,此時truncate不能操作此表

看來只能用批次刪除,但涉及到問題:
 1,空間是否馬上釋放
 2,高水位線
 3,對於表上dml的阻塞
 
 
執行如下plsql時,
declare
 cursor cur_1  is select rowid from t_free tt where tt.a<=5000000;
 v_rowid varchar2(18);
 v_cnt pls_integer;
begin
  v_cnt:=0;
  open cur_1;
  loop
    fetch cur_1 into v_rowid;
    v_cnt:=cnt+1;
   
    exit when cur_1%notfound;
  delete from t_free where rowid=v_rowid;
  if v_cnt=1000 then
    commit;
    v_cnt:=0;
  end if;
 
  end loop;
end;

PL/SQL 過程已成功完成。

已用時間:  00: 32: 08.95

小結:用此法發生大量的等待事件為db file sequential read,效能極低

 

 

 

--2次最佳化後的儲存過程如下:
create or replace procedure proc_batch_delete(in_start pls_integer,in_end pls_integer)
as
begin
  delete from t_free tt where tt.a between in_start and in_end;
  commit; 
end;

改寫的sql依舊等待事件多是:db file sequential read
12:04:11 session_130>exec proc_batch_delete(1,100000);
PL/SQL 過程已成功完成。

已用時間:  00: 02: 09.56

依次產生各種等待事件
依次展開發分析

如果同時在多個會話執行
exec proc_batch_delete(x,y);

兩個會話交替互換出現
read by other session和
db file scattered read

 

--再次最佳化上述的儲存過程
--3次最佳化後的儲存過程如下:
create or replace procedure proc_batch_delete(in_start pls_integer,in_end pls_integer)
as
V_LOGNUM NUMBER; -- 資料庫中擁有的日誌檔案數
V_NEEDARC NUMBER; -- 需要歸檔的日誌檔案數
BEGIN
loop
 delete from t_free tt where (tt.a between in_start and in_end) AND rownum < 500;
 IF SQL%ROWCOUNT = 0 THEN
   EXIT;
 END IF;
 COMMIT;
end loop;
END;
/