long長事務(wù)回滾的模擬與定位

發(fā)布時間：2020-08-18 20:40:15 來源：ITPUB博客閱讀：191 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫

大型事務(wù)的回滾大型事務(wù)的回滾產(chǎn)生非常大的代價，不僅鎖定需要的資源，并且消耗的CPU和IO，尤其是IO將極為密集。尤其在KILL大型事務(wù)之前檢查事務(wù)究竟有多大可能是必要的，同時我們也需要知道回滾已經(jīng)進(jìn)行了多少程度。
一、模擬：

1.刪除500多萬的數(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(*)
----------
   2785760

SQL> insert into test select * from test;

2785760 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

COUNT(*)
----------
   5571520

SQL> analyze table test compute statistics;

Table analyzed.

SQL>
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        30

SQL>

--模擬刪除，不commit
SQL> delete test;

5571520 rows deleted.

--另開窗口，查詢kill掉會話

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                                                               11

SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';

SQL_TEXT
----------------------------------------------------------------------
delete test

SQL> alter system kill session '30,165' immediate;

System altered.

--回到原來窗口驗(yàn)證：
SQL> select count(*) from test;
select count(*) from test
        *
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 28346
Session ID: 30 Serial number: 165

二、定位：
查看回滾進(jìn)度：
可以通過以下兩個視圖查看回滾的進(jìn)度，通過單位時間內(nèi)恢復(fù)的undo block來估算恢復(fù)時間：
1.   通過x$ktuxe
alter 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:22

SQL>

可以通過KTUXESLT ,KTUXESQN這兩個字段，然后用以下腳本回滾得出大概需要的時間：
set serveroutput on
declare
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:.01

PL/SQL procedure successfully completed.

SQL> SQL>

2.   通過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

通過如下視圖觀察回滾是串行還是并行回滾的，如下圖應(yīng)是并行恢復(fù)的，
V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.

通過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 05000B00681E0000

3.查看回滾對象
通過dump undo block方式查看回滾的對象：

首先通過v$fast_start_trancsations的usn字段查詢到使用的回滾段。

SQL> select * from v$rollname where usn=5;

       USN NAME
---------- ------------------------------
         5 _SYSSMU5_898567397$

dump這個undo block，因?yàn)閐ump的文件很大，在查詢出對象的object_id后，kill掉這個dump會話。
alter system dump undo block "" XID ;

SQL> alter system dump undo block "_SYSSMU5_898567397$" xid 5 11 7784;

。。。。。。。。。

[oracle@wang trace]$ ls -lrt
total 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]$

可以查詢到objn為121192,對應(yīng)的就是dba_objects的object_id，即mosongtao.rollback_test，正是前邊測試的對象。再查詢v$session_longops配合username,last_update_time,target，可以大概定位到執(zhí)行sql_id。

注意：在查詢到object_id后手工停掉dump undo block 動作

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.查詢sql
SQL> 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-17

SQL> select sql_text from v$sql where sql_id='7qqwcq9td6akt';

SQL_TEXT
-------------------------------------------------------------------------
delete test

SQL>

向AI問一下細(xì)節(jié)

long長事務(wù)回滾的模擬與定位

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽