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;
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;
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 过程已成功完成。
12:04:11 session_130>exec proc_batch_delete(1,100000);
PL/SQL 过程已成功完成。
已用时间: 00: 02: 09.56
依次产生各种等待事件
依次展开发分析
依次展开发分析
如果同时在多个会话运行
exec proc_batch_delete(x,y);
exec proc_batch_delete(x,y);
两个会话交替互换出现
read by other session和
db file scattered read
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;
/
--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/