如何处理业务系统中并发比较高的表数据清理工作

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;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-751833/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-751833/