ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130904]等待事件wait for a undo record模拟.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-09-04 16:27:27 0 删除 编辑
[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')

--正好对上!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-772157/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2594
  • 访问量
    6370417