事务回滚时间估算
1、
当Oracle处于open 状态,当Oracle回滚事务的时候,可以从used_urec,used_ublk数值可以初步估计Oracle回滚事务
的速度。
SQL> select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;
from v$session a, v$transaction b
where a.saddr=b.ses_addr;
2、
当Oracle非正常关闭(如shutdown abort)时,处于业务繁忙期,再次open时,v$transaction重置,smon进程事务回滚,查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE,UNDOBLOCKSTOTAL估算smon恢复进度,这里涉及到参数FAST_START_PARALLEL_ROLLBACK的设置,设置方法可以查看Oracle文档。需要注意的是Oracle在回滚大事务并行回滚参数设置存在bug,这时候你可以查询视图v$fast_start_servers中字段STATE ,如果只有一进城处于RECOVERING,其他进程处于IDLE,则可考虑将FAST_START_PARALLEL_ROLLBACK设置为false,关闭并行恢复。如果所有进程都处于RECOVERING状态,则可以考虑加大恢复进程,将其设置为high。
set linesize 120
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;
"Estimated time to complete"
from v$fast_start_transactions;
SELECT s.sid, s.username, s.osuser, s.machine, s.program,
t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.log_io, t.phy_io, t.cr_get, t.cr_change,
r.name, q.sql_text
FROM v$session s,v$transaction t, v$RollName r, v$sqlarea q
WHERE s.saddr=t.ses_addr
and t.xidusn = r.usn
and s.sql_address = q.address(+)
and s.sql_hash_value = q.hash_value(+)
And r.name = 'RBS_BATCH1';
And r.name = 'RBS_BATCH1';
检查是否还有其他事务占用了该回滚段
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24996904/viewspace-767677/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24996904/viewspace-767677/