首先，我是做BI的，在工作中偶尔会出现一些特殊情况，比如突然在某天ETL日增量调度的时候一个简单的插入操作卡住不动几小时都过不去，平时可能30分钟左右。 可能是资源征用导致DataStage资源分配不均等各种原因，这个时候通常会有运维的同事协助中断此作业的运行，进行重新插入。 中断后就要开始漫长的等待，等全部rollback后进行rerun这个JOB。 而这时候业务用户就不爽了，为什么都9点了我还看不了报表数据？通常情况我们会先各种赔礼道歉然后给一个大概的时间点安抚用户， 等待时间基本就是回滚时间 + 重新插数时间，插数时间可以从调度日志中获取历史平均值，而回滚时间就难以获取，这里给大家普及一个查看事物回滚时间的知识点 这里我们不详细讲解DB异常关闭的SMON事务回滚，大概说下： 基本分3种方式： 1. 查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算smon恢复进度 2. 跟踪日志 3. 查询内部试图x$ktuxe [k]ernel layer [t]ransaction layer [u]ndo transaction [e]ntry  可参考http://www.ssc.stn.sh.cn/html/zsk/ITyw/2012-04/5819.html 这里主要说下一个insert、delete、update等语句的rollback时间估算 建个测试表 <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code371" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> create table table_chenxu as select * from dba_objects </li> </ol> </div> </div> 插入大量数据 <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code663" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> begin </li> <li> for i in 1..100 </li> <li> loop </li> <li> insert into table_chenxu </li> <li> select * from dba_objects; </li> <li> commit; </li> <li> end loop; </li> <li> end; </li> </ol> </div> </div> 首先第一步我们要拿到正在回滚的SQL的SQLID和SESSIONID，这个简单，假如我们对table_chenxu进行delete，回滚过程中这个table_chenxu必定是被锁的，所以我们可以根据gv$locked_object来查看当前对象是否被锁，且根据sid查看锁表的SQL 我们先update下这个表 update table_chenxu t set t.owner = 'azzo'; 然后查询被锁的相关信息： 注：多个系统视图关联查询的时候为了防止试图合并可以加上use_hash或者no_merge <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code678" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> select /*+ use_hash(o,s,t,q)*/ </li> <li>  t.object_name, o.session_id, s.sql_id, q.sql_text, s.* </li> <li>   from gv$locked_object o, dba_objects t, gv$session s, gv$sql q </li> <li>  where t.object_id = o.object_id </li> <li>    and o.session_id = s.sid </li> <li>    and s.sql_id = q.sql_id </li> <li>    and t.object_name = 'TABLE_CHENXU' </li> </ol> </div> </div> 结果如下： <img src="/attachment/201509/7/26224278_1441625282A0vI.jpg" width="700" height="180.13029315961" alt="" /> 我是PC机，update用了628秒，现在rollback（plsql developer死了。。于是重新开一个）： 使用如下SQL查看ktuxesiz字段，过一段时间（如10s）再执行一次，再看一下，两个结果相减除以10就是每秒回滚的速度 <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code550" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> select </li> <li>       s.username, </li> <li>       t.xidusn, </li> <li>       t.xidslot, </li> <li>       t.xidsqn, </li> <li>       x.ktuxesiz </li> <li>     from </li> <li>       sys.x$ktuxe x, </li> <li>       sys.v_$transaction t, </li> <li>       sys.v_$session s </li> <li>     where </li> <li>       x.inst_id = userenv('Instance') and </li> <li>       x.ktuxesta = 'ACTIVE' and </li> <li>       x.ktuxesiz > 1 and </li> <li>       t.xidusn = x.ktuxeusn and </li> <li>       t.xidslot = x.ktuxeslt and </li> <li>       t.xidsqn = x.ktuxesqn and </li> <li>       s.saddr = t.ses_addr; </li> </ol> </div> </div> 或者使用自动化脚本（取自网络） <div> <div class="codeheads"> 点击(此处)折叠或打开 </div> <div id="code909" class="codeText"> <ol style="margin:0 1px 0 0px;padding-left:40px;" start="1" class="dp-css"> <li> declare </li> <li>   cursor tx is </li> <li>     select </li> <li>       s.username, </li> <li>       t.xidusn, </li> <li>       t.xidslot, </li> <li>       t.xidsqn, </li> <li>       x.ktuxesiz </li> <li>     from </li> <li>       sys.x$ktuxe x, </li> <li>       sys.v_$transaction t, </li> <li>       sys.v_$session s </li> <li>     where </li> <li>       x.inst_id = userenv('Instance') and </li> <li>       x.ktuxesta = 'ACTIVE' and </li> <li>       x.ktuxesiz > 1 and </li> <li>       t.xidusn = x.ktuxeusn and </li> <li>       t.xidslot = x.ktuxeslt and </li> <li>       t.xidsqn = x.ktuxesqn and </li> <li>       s.saddr = t.ses_addr; </li> <li>   user_name varchar2(30); </li> <li>   xid_usn number; </li> <li>   xid_slot number; </li> <li>   xid_sqn number; </li> <li>   used_ublk1 number; </li> <li>   used_ublk2 number; </li> <li> begin </li> <li>   open tx; </li> <li>   loop </li> <li>     fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1; </li> <li>     exit when tx%notfound; </li> <li>     if tx%rowcount = 1 </li> <li>     then </li> <li>       sys.dbms_lock.sleep(10); </li> <li>     end if; </li> <li>     select </li> <li>       sum(ktuxesiz) </li> <li>     into </li> <li>       used_ublk2 </li> <li>     from </li> <li>       sys.x$ktuxe </li> <li>     where </li> <li>       inst_id = userenv('Instance') and </li> <li>       ktuxeusn = xid_usn and </li> <li>       ktuxeslt = xid_slot and </li> <li>       ktuxesqn = xid_sqn and </li> <li>       ktuxesta = 'ACTIVE'; </li> <li>     if used_ublk2 < used_ublk1 </li> <li>     then </li> <li>       sys.dbms_output.put_line( </li> <li>         user_name || </li> <li>         '''s transaction ' || </li> <li>         xid_usn || '.' || </li> <li>         xid_slot || '.' || </li> <li>         xid_sqn || </li> <li>         ' will finish rolling back at approximately ' || </li> <li>         to_char( </li> <li>           sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24, </li> <li>           'HH24:MI:SS DD-MON-YYYY' </li> <li>         ) </li> <li>       ); </li> <li>     end if; </li> <li>   end loop; </li> <li>   if user_name is null </li> <li>   then </li> <li>     sys.dbms_output.put_line('No transactions appear to be rolling back.'); </li> <li>   end if; </li> <li> end; </li> </ol> </div> </div> 结果如下： SCOTT's transaction 8.19.1171 will finish rolling back at approximately 19:47:57 07-9月 -2015 <div style="white-space:nowrap;"> </div> 是不是很方便~  不过自动化脚本需要有dba权限哦，sys.dbms_lock.sleep(10)这个包，普通用户是没权限以调用的，如果没有dba权限就掐表及时吧<img src="http://blog.itpub.net/kindeditor/plugins/emoticons/images/13.gif" border="0" alt="" /> 直到写完这篇文章，我的plsql还在未响应中。。。

ITPub博客

【Oracle】查看事务回滚的时间

最新文章