模拟等待事件wait for a undo record。
1.测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t as select rownum id ,lpad('a',400,'a') name from dual connect by levelinsert into t select * from t;
....
commit ;
最后记录大小640000，占用304M.
2.修改记录:
--回话1：
SCOTT@test> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
12223
update t set name=lpad('b',400,'b') ;
--等待结束后，打开另外的shell，kill掉spid=12223.另外我的测试如果执行正常rollback，不会出现wait for a undo record的等待事件。
kill -9 12223
select * from V$FAST_START_TRANSACTIONS;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--- ---- ------ ----------- -------------- --------------- ----- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
7 27 12113 RECOVERING 15655 43019 34 25 0 0 0 07001B00512F0000 0000000000000000 1
select * from V$FAST_START_TRANSACTIONS;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--- ---- ------ ----------- -------------- --------------- ----- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
7 27 12113 RECOVERING 25106 43019 34 50 0 0 0 07001B00512F0000 0000000000000000 8
--CPUTIME逐步在增加。
SCOTT@test> select event,sid,serial#,program from v$session where wait_class'Idle';
EVENT SID SERIAL# PROGRAM
---------------------------------------- ---------- ---------- --------------------------
log file switch (checkpoint incomplete) 15 5 oracle@xxxxx (P002)
log file switch (checkpoint incomplete) 16 11 oracle@xxxxx (P006)
wait for stopper event to be increased 66 1 oracle@xxxxx (SMON)
log file switch (checkpoint incomplete) 72 335 oracle@xxxxx (P007)
log file switch (checkpoint incomplete) 73 67 oracle@xxxxx (P003)
db file async I/O submit 127 1 oracle@xxxxx (DBW0)
log file switch (checkpoint incomplete) 135 139 oracle@xxxxx (P004)
wait for a undo record 136 83 oracle@xxxxx (P000)
SQL*Net message to client 199 697 sqlplus@xxxxx (TNS V1-V3)
log file switch (checkpoint incomplete) 200 641 oracle@xxxxx (P001)
buffer busy waits 202 37 oracle@xxxxx (P005)
11 rows selected.
SCOTT@test> select event,sid,serial#,program from v$session where wait_class'Idle';
EVENT SID SERIAL# PROGRAM
---------------------------------------- ---------- ---------- --------------------------
wait for stopper event to be increased 66 1 oracle@xxxxx (SMON)
db file async I/O submit 127 1 oracle@xxxxx (DBW0)
wait for a undo record 136 83 oracle@xxxxx (P000)
log file parallel write 189 1 oracle@xxxxx (LGWR)
SQL*Net message to client 199 697 sqlplus@xxxxx (TNS V1-V3)
--可以发现出现wait for a undo record等待事件。
SQL> select * from V$FAST_START_TRANSACTIONS;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--- ---- ------ --------- -------------- --------------- ----- ---------- ---------- ---------- ---------- ---------------- ---- ----------
7 27 12113 RECOVERED 43019 43019 149 07001B00512F0000 8
--从xid反推看看。
select distinct sql_id from V$ACTIVE_SESSION_HISTORY where xid=hextoraw('07001B00512F0000');
SQL_ID
-------------
b9y957hayvgkm
select sql_id,sql_text from v$sql where sql_id='b9y957hayvgkm' ;
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
b9y957hayvgkm update t set name=lpad('b',400,'b')
--正好对上!