[20130904]等待事件wait for a undo record模擬.txt

[20130904]等待事件wait for a undo record模擬.txt

模擬等待事件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 level<=4e4;
insert 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')

--正好對上!