Oracle10g New Feature -- 7. Rollback Monitoring-CSDN博客

在oracle10g中，如果某个回滚进程所用时间超过6秒，就能在v$session_longops中被捕捉到。这个看似微不足道的功能其实还挺有用的呢，至少对我来说是这样。

以前做上海移动的项目时，一张表动仄上千万，开发和测试人员经常在一句SQL执行了半个小时或者更久后发现错误又急忙停止，等着回滚结束后再次执行。可惜回滚结束这个时间太难估计了，当DBA的就经常被他们追着问。有了上面这个功能后至少可以给他们一个大概的估计时间了，并且是有根据的哦。要是估计错了那也是oracle自己估计错了：）

至于另外一个parallel instance recovery功能，目前似乎并没有体会到多少用处。能提高多少恢复速度呢？真正碰到问题时会想到这个功能吗？

[@more@]

1. Rollback Monitoring

1. DML Rollback

In Oracle 10g, If a rollback process takes more than six seconds, the record appears in v$SESSION_LONGOPS.

SQL>select sid,opname,target,sofar,totalwork,time_remaining from v$session_longops where sofar!=totalwork

SID OPNAME TARGET SOFAR TOTALWORK TIME_REMAINING

---------- -------------------------------------- ------------------------ -------------------- ------------------------

133 Transaction Rollback 887 8415 59

SQL> select sql_text from v$sql where sql_id in (select sql_id from v$session_longops where sid=133);

SQL_TEXT

--------------------------------------------------------------------------------

rollback

insert into emp select * from emp

rollback

2. Parallel Instance Recovery

Suppose that during a large insert, the instance shuts down abnormally. When the instance comes up, the failed transaction is rolled back. If the value of the initialization parameter for parallel recovery is enabled, the rollback occurs in parallel instead of serially, as it occurs in regular transaction rollback.

Session a is running insert DML

SQL>insert into emp select * from emp;

Session b shuts down the instance in abort mode

SQL> connect sys/oracle as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

Session a shows

SQL> insert into emp select * from emp;

insert into emp select * from emp

ERROR at line 1:

ORA-03113: end-of-file on communication channel