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博客

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

最新文章