大型事務(wù)的回滾 大型事務(wù)的回滾產(chǎn)生非常大的代價(jià)，不僅鎖定需要的資源，并且消耗的CPU和IO，尤其是IO將極為密集。尤其在KILL大型事務(wù)之前檢查事務(wù)究竟有多大可能是必要的，同時(shí)我們也需要知道回滾已經(jīng)進(jìn)行了多少程度。一、模擬：1.刪除500多萬(wàn)的數(shù)據(jù)。SQL> conn hr/hr;Connected.SQL> create table test as select * from dba_objects;Table created.SQL> insert into test select * from test;87055 rows created.SQL> insert into test select * from test;174110 rows created.SQL> insert into test select * from test;348220 rows created.SQL> insert into test select * from test;696440 rows created.SQL> insert into test select * from test;1392880 rows created.SQL> select count(*) from test; COUNT(*)---------- 2785760SQL> insert into test select * from test;2785760 rows created.SQL> commit;Commit complete.SQL> select count(*) from test; COUNT(*)---------- 5571520SQL> analyze table test compute statistics;Table analyzed.SQL> SQL> select sid from v$mystat where rownum=1; SID---------- 30SQL>--模擬刪除，不commitSQL> delete test; 5571520 rows deleted.--另開(kāi)窗口，查詢(xún)kill掉會(huì)話SQL> select sid,serial#,sql_id,event,blocking_session from v$session where sid=30; SID SERIAL# SQL_ID EVENT BLOCKING_SESSION---------- ---------- ------------- ---------------------------------------------------------------- ---------------- 30 165 7qqwcq9td6akt log buffer space 11SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';SQL_TEXT---------------------------------------------------------------------- delete testSQL> alter system kill session '30,165' immediate;System altered.--回到原來(lái)窗口驗(yàn)證：SQL> select count(*) from test; select count(*) from test *ERROR at line 1:ORA-03135: connection lost contactProcess ID: 28346Session ID: 30 Serial number: 165二、定位：查看回滾進(jìn)度：可以通過(guò)以下兩個(gè)視圖查看回滾的進(jìn)度，通過(guò)單位時(shí)間內(nèi)恢復(fù)的undo block來(lái)估算恢復(fù)時(shí)間：1. 通過(guò)x$ktuxealter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ,sysdate from x$ktuxe where KTUXECFL='DEAD' and KTUXESIZ >0;SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';Session altered.SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ,sysdate from x$ktuxe where KTUXECFL='DEAD' and KTUXESIZ >0;ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ SYSDATE---------------- ---------- ---------- ---------- ---------- -------------------00007F170E8AAC20 5 11 7784 84438 2017-12-09 14:19:22SQL>可以通過(guò)KTUXESLT ,KTUXESQN這兩個(gè)字段，然后用以下腳本回滾得出大概需要的時(shí)間：set serveroutput ondeclare l_start number; l_end number;begin select ktuxesiz into l_start from x$ktuxe where KTUXEUSN = 5 and KTUXESLT = 11; dbms_lock.sleep(60); select ktuxesiz into l_end from x$ktuxe where KTUXEUSN = 5 and KTUXESLT = 11; dbms_output.put_line('time est Day:' || round(l_end / (l_start - l_end) / 60 / 24, 2));end;/time est Day:.01PL/SQL procedure successfully completed.SQL> SQL> 2. 通過(guò)v$fast_start_trancsations 狀態(tài)為recovering表示恢復(fù)中;select USN,SLT,SEQ,STATE,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,CPUTIME,XID,sysdate from V$FAST_START_TRANSACTIONS;SQL> select USN,SLT,SEQ,STATE,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,CPUTIME,XID,sysdate from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME XID SYSDATE---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------------- ------------------- 5 11 7784 RECOVERING 123491 179829 120 05000B00681E0000 2017-12-09 14:20:25 通過(guò)如下視圖觀察回滾是串行還是并行回滾的，如下圖應(yīng)是并行恢復(fù)的，V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.通過(guò)xid字段與v$fast_start_trancsations關(guān)聯(lián)。select * from v$fast_start_servers where xid in (select XID from V$FAST_START_TRANSACTIONS);SQL> select * from v$fast_start_servers where xid in (select XID from V$FAST_START_TRANSACTIONS);STATE UNDOBLOCKSDONE PID XID----------- -------------- ---------- ----------------RECOVERING 133950 20 05000B00681E00003.查看回滾對(duì)象通過(guò)dump undo block方式查看回滾的對(duì)象：首先通過(guò)v$fast_start_trancsations的usn字段查詢(xún)到使用的回滾段。SQL> select * from v$rollname where usn=5; USN NAME---------- ------------------------------ 5 _SYSSMU5_898567397$ dump這個(gè)undo block，因?yàn)閐ump的文件很大，在查詢(xún)出對(duì)象的object_id后，kill掉這個(gè)dump會(huì)話。alter system dump undo block "" XID ;SQL> alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784; 。。。。。。。。。[oracle@wang trace]$ ls -lrttotal 635992-rw-r----- 1 oracle oinstall 81 Apr 27 2017 DBdb_ora_9045.trm-rw-r----- 1 oracle oinstall 59 Apr 27 2017 DBdb_mman_9065.trm-rw-r----- 1 oracle oinstall 60 Apr 27 2017 DBdb_ora_9084.trm-rw-r----- 1 oracle oinstall 111 Apr 27 2017 DBdb_ora_9099.trm.......................................-rw-r----- 1 oracle oinstall 12306 Dec 9 14:23 DBdb_ora_28113.trm-rw-r----- 1 oracle oinstall 536798705 Dec 9 14:23 DBdb_ora_28113.trc[oracle@wang trace]$[oracle@wang trace]$[oracle@wang trace]$[oracle@wang trace]$ grep objn DBdb_ora_28113.trc | head -5* Rec #0x11 slt: 0x0b objn: 90373(0x00016105) objd: 90373 tblspc: 4(0x00000004)* Rec #0x10 slt: 0x0b objn: 90373(0x00016105) objd: 90373 tblspc: 4(0x00000004)* Rec #0xf slt: 0x0b objn: 90373(0x00016105) objd: 90373 tblspc: 4(0x00000004)* Rec #0xe slt: 0x0b objn: 90373(0x00016105) objd: 90373 tblspc: 4(0x00000004)* Rec #0xd slt: 0x0b objn: 90373(0x00016105) objd: 90373 tblspc: 4(0x00000004)[oracle@wang trace]$[oracle@wang trace]$可以查詢(xún)到objn為121192,對(duì)應(yīng)的就是dba_objects的object_id，即mosongtao.rollback_test，正是前邊測(cè)試的對(duì)象。再查詢(xún)v$session_longops配合username,last_update_time,target，可以大概定位到執(zhí)行sql_id。
注意：在查詢(xún)到object_id后手工停掉dump undo block 動(dòng)作
SQL> alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784;
^C^C^C^C^C^C
alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> SQL> SQL> SQL>
4.查詢(xún)sqlSQL> select SID,TARGET,SQL_ID,START_TIME,LAST_UPDATE_TIME from v$session_longops where target like '%TEST%' order by LAST_UPDATE_TIME desc; SID TARGET SQL_ID START_TIME LAST_UPDATE_---------- ---------------------------------------------------------------- ------------- ------------ ------------ 30 HR.TEST 7qqwcq9td6akt 09-DEC-17 09-DEC-17SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';SQL_TEXT------------------------------------------------------------------------- delete testSQL>
名稱(chēng)欄目：long長(zhǎng)事務(wù)回滾的模擬與定位
文章地址：
http://weahome.cn/article/gijihs.html
重慶分公司
重慶分公司
