ITPub博客

首页 > 数据库 > Oracle > Oracle10g New Feature -- 7. Rollback Monitoring

Oracle10g New Feature -- 7. Rollback Monitoring

原创 Oracle 作者:zhyuh 时间:2004-09-21 14:35:19 0 删除 编辑

在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

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-778763/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008719