Oracle10g New Feature -- 7. Rollback Monitoring

在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

 

Session b restart the instance

SQL> startup

ORACLE instance started.

Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145750508 bytes

Database Buffers           25165824 bytes

Redo Buffers                 262144 bytes

Database mounted.

Database opened.

 

During instance start up, check the rcvservers in view v$fast_start_transactions

SQL> select rcvservers from v$fast_start_transactions;

RCVSERVERS

----------

         1

 

Change the initial parameter fast_start_parallel_rollback

SQL> alter system set fast_start_parallel_rollback=high;

System altered.

Check the rcvservers again

SQL> select rcvservers from v$fast_start_transactions;

RCVSERVERS

----------

         4