如何查看一个session使用了多少undo

Session Undo

Filed under:  Infrastructure, Oracle, Troubleshooting, undo — Jonathan Lewis @ 8:40 pm BST Sep 22,2010 

One of the simple scripts I use from time to time monitors the amount of undo generated by a long running session by taking a session id (SID) as an input and joining v$session to v$transaction:

column start_scn format 999,999,999,999   select          tr.start_scn, tr.log_io, tr.phy_io, tr.used_ublk, tr.used_urec, recursive from          v$session       se,          V$transaction   tr where          se.sid = &1 and     tr.ses_addr = se.saddr ;           START_SCN     LOG_IO     PHY_IO  USED_UBLK  USED_UREC REC ---------------- ---------- ---------- ---------- ---------- ---   136,509,466,121   10730956     358074          1          1 NO   136,515,115,543         12          0          1          3 YES

You’ll note that this is a session that has been doing quite a lot of work (log_io = db block gets + db block changes, and phy_io = physical reads). I was watching it because I wanted to check that it was only generating undo in a series of very short recursive transactions. (It’s running a coalesce on a very large, very smashed, index).

Interestingly the work done by the recursive transactions for the coalesce was accounted for against the parent transaction, even though (fortunately) the undo block and record usage was not. Theused_urec and used_ublk columns can best be described as showing you “currently held” space, rather than “cumulative space used”.

Addendum: with reference to Gary’s comment below – here’s a lovely little script – originally written for 9i, but still working) from Steve Adams for estimating how long it will take for rolling back to complete. Note that it references sys.x_$ktuxe; Steve has a convention of creating “x_” views on the x$ objects when there is no v$ view supplied by Oracle, so x_$ktuxe is just a sys-owned view defined as ‘select * from x$ktuxe’.