Shulou(Shulou.com)06/01 Report--

Rollback of large transactions rollback of large transactions is very costly, not only locking the required resources, but also consuming CPU and IO, especially IO, will be extremely intensive. In particular, it is necessary to check how likely a transaction is before a large KILL transaction, and we also need to know how far the rollback has gone.

1. Simulation:

1. Delete more than 5 million data.

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

-

thirty

SQL >

-- simulated deletion, not commit

SQL > delete test

5571520 rows deleted.

-- Open another window to query the kill session

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 '30165' immediate

System altered.

-- go back to the original window to verify:

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

Second, positioning:

Check the rollback progress:

You can view the progress of the rollback through the following two views, and estimate the recovery time based on the undo block recovered per unit time:

1. Through 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 >

You can use the KTUXESLT and KTUXESQN fields, and then roll back with the following script to get the approximate time required:

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 >

two。 Using the v$fast_start_trancsations status of recovering indicates that the recovery is in progress.

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

Observe whether the rollback is serial or parallel through the following view, and the following figure should be recovered in parallel

V$FAST_START_SERVERS provides information about all the recovery slaves performing parallel transaction recovery.

Associate with v$fast_start_trancsations through the xid field.

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. View rollback object

View the rolled-back objects through dump undo block:

First, the rollback segment used is queried through the usn field of v$fast_start_trancsations.

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

USN NAME

5 _ SYSSMU5_898567397 $

Dump this undo block, because the dump file is very large, after querying the object_id of the object, kill drops the dump session.

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 111Apr 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] $

You can query that the objn is 121192, corresponding to the object_id of dba_objects, that is, mosongtao.rollback_test, which is the object of the previous test. If you query v$session_longops with username,last_update_time,target, you can roughly locate the execution of sql_id.

Note: stop the dump undo block action manually after the object_id is queried

SQL > alter system dump undo block "_ SYSSMU5_898567397 $" xid 5 11 7784

^ 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 >

4. Query 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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.