Oracle Tips and Tricks — David Fitzjarrell

October 16, 2017

Roll With It

Filed under: General — dfitzjarrell @ 11:07

"I know one thing for certain; it is much harder to tell whether you are lost than whether you were lost,
for, on many occasions, where you are going is exactly where you are. On the other hand, if you often find
that where you've been is not at all where you should have gone, and, since it's much more difficult to
find your way back from someplace you've never left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

Waiting is, well, inconvenient at times because it interferes with getting work done yet there are times when there is nothing to do, really, except wait. Of course this usually happens during an important update to a critical application table, when the transaction fails on error or, horror of horrors, the network ‘gives way’ and the session is disconnected, forcing Oracle to rollback all of that work you just did. Unfortunately that rollback seems to take forever to complete. The reason for that is that large, long-running transactions can generate large volumes of UNDO. And UNDO is how Oracle restores the data to its starting point should the transaction fail or be cancelled (which, by golly, just happened). This, of course, generates long rollback times that can seem interminable when you are waiting for the rollback to complete before other work can begin. But, monitoring the rollback progress isn’t difficult since V$TRANSACTION, in conjunction with V$SESSION, can report on the UNDO blocks that have yet to be applied. Let’s see how that can be done.

V$TRANSACTION records various pieces of information about active and cancelled transactions, with one of those pieces being the blocks of UNDO that are used (either allocated or applied, depending on whether the transaction is running or being rolled back). That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:


set echo on pagesize 100 linesize 132

select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
                                          else 'Not Rolling Back'
       end   as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'));

select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
from v$transaction t, v$session s
where t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr;

The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done. The client_info column may or may not be populated (it isn’t in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored. Looking at an example should help; a long-running transaction is in progress in session #1; looking at the UNDO blocks being allocated (from a second session) we see the USED_UBLK column increasing in value and that the transaction is not rolling back:


BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              430

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              739

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >
...
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6475

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6755

BING @ quanghoo >

At the time the transaction is cancelled the used UNDO block total was:


--
-- Transaction still active and generating UNDO
--
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             7914

BING @ quanghoo >

From the second session we can now monitor the progress of the rollback, using the same queries as before. This time the first query reports a rollback is in progress:


--
-- Transaction cancelled
--
-- Successive executions of these queries return a decreasing used undo block count
-- thus tracking the rollback progress
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             5110

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             3491

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             1363

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688               70

BING @ quanghoo >

--
-- Transaction rollback complete
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >

The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.

Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyone’s life (at least for those who are waiting on a rollback to finish) a bit easier.

Which doesn’t make you lost, just … impatient.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: