Skip to Main Content
  • Questions
  • How to find out how much rollback a session has to do

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arup.

Asked: January 04, 2003 - 11:25 am UTC

Last updated: July 20, 2016 - 9:44 am UTC

Version: 9.2

Viewed 100K+ times! This question is

You Asked

Hello Tom,

As always, thanks a lot for accepting this question. When a session rolls back for some error (typically, when the rollback segment or undo space cannot grow), what is the best way to determine if it is rolling back and how much is left to be rolled back. I use the query SELECT USED_UBLK FROM V$TRANSACTION to see how much undo blocks the transaction has allocated to it's changes. If the figure drops, then this is an indication that the transaction is rolling back. Is this the preferred approach, or is there a better one?

Also, how do I know how many undo blocks the transaction had when it started the rollback process, enabling the DBA to estimate how much longer the rollback will continue.

Thanks in advance.

Arup

and Tom said...

USED_UBLK is what you would use - yes.

You need not know how many blocks were used at the beginning of the rollback. You simply need to know:

a) how many are used NOW - used_now
b) in say 60 seconds how many are used - used_later

Now, just take (used_later / ( used_now - used_later )) and that is approximately how many minutes you have left (as a swag -- if of course won't be 100% accurate, but it is a good guess)

So, if you have 500 used_ublks to start,
60 seconds later you have 400

you can guess that 400/(500-400) = 4minutes is the amount remaining....




Rating

  (51 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 04, 2003 - 12:37 pm UTC

Tom,

What is the correct way to join v$transaction to v$session?

Tom Kyte
January 04, 2003 - 12:42 pm UTC

v$transaction.addr = v$session.taddr

Negative used_ublk

A reader, January 04, 2003 - 12:53 pm UTC

Hi Tom,

Sometimes I see negative used_ublk value in the v$transasction.
SQL text in v$sqlarea for this v$transaction entry is just query.
What does it mean ?

Thanks

Tom Kyte
January 04, 2003 - 2:07 pm UTC

it means during a rollback they took away more than they put in generally. It is "harmless". consider it zero.

What's the FLAG

Arup Nanda, January 05, 2003 - 1:06 am UTC

Right on dot, as always. What's the purpose of the FLAG column in v$transaction? I see that while rolling back, it's 7811 and an ongoing transaction has 7683. What other values are possible and what do they indicate?

Tom Kyte
January 05, 2003 - 9:44 am UTC

They are undocumented bit flags actually -- it is not the numbers 7811 and 7683 so much as the HEX:

ops$tkyte@ORA920> select to_char( 7811, '0000000X' ), to_char( 7683, '0000000X' ) from dual;

TO_CHAR(7 TO_CHAR(7
--------- ---------
 00001E83  00001E03
       ^         ^

0 indicates "normal user transaction"
8 indicates "rollback,most likely - means no more changes and you cannot commit"
 

A reader, January 05, 2003 - 12:17 pm UTC

Hi Tom,

There is this column in v$transaction and the definition is

SES_ADDR RAW(4) User session object address


I got this value when from v$transaction.

test@oracle.com> select ses_addr from v$transaction;

SES_ADDR
--------
7B20A4E8

test@oracle.com> select UTL_RAW.CAST_TO_VARCHAR2('7B20A4E8') from dual;

UTL_RAW.CAST_TO_VARCHAR2('7B20A4E8')
------------------------------------------------------
{ ¤è


Tom why is it displaying in that format.

Thanks

Tom Kyte
January 05, 2003 - 12:49 pm UTC

cast_to_varchar2 expects a raw.
you sent a string -- which happened to be hex -- so it converted to a 4byte raw

cast to varchar2 just changes the "type" field in the variable -- so now you have a 4 byte varchar2

you could achieve a similar effect by:

select chr( to_number('7b','xx' ) ) ||
chr( to_number('20','xx' ) ) || ....

from dual


you just took 4 bytes of RAW data and said "pretend it is a string, print it"

garbage in
garbage out

A reader, January 05, 2003 - 12:58 pm UTC

Tom,

Thanks for the feedback, but if i wanna know the name of object how can i get that.

Tom Kyte
January 05, 2003 - 1:38 pm UTC

the name of "what" object?


v$transaction is about a transaction -- a transction typically involves MANY database objects -- so it cannot be a database object.

If you mean the "session object", that is the row in v$session.


select v$session.*
from v$session, v$transaction
where v$session.saddr = v$transaction.ses_addr;


gets the "session" object.

amazing

Gabriel, April 09, 2003 - 2:23 pm UTC

I looked all over the internet for a method to get the sql text of transactions that are rolling back but this the only page where I found the correct information (as usual). So just to check Tom:
select sql_text
from v$sqlarea
where address = (select sql_address from v$session where sid in (select SID
from v$transaction,dba_rollback_segs,v$session
where SADDR=SES_ADDR and
XIDUSN=SEGMENT_ID and
flag=7811));
would get me that text?

Thanks

Tom Kyte
April 09, 2003 - 2:41 pm UTC

7811 is not the only value that gives you 0x8 in the second byte ..

you need to look at that byte and see if it is 8

Check that individual bit.....

Mark J. Bobak, April 09, 2003 - 5:51 pm UTC

It's a bitmap, so, look at the two numbers in binary:
7811 = 0001 1110 1000 0011 = active transaction, no rollback
7683 = 0001 1110 0000 0011 = rollback in progress

Note that the numbers only differ by one bit. Looking
at the numbers in decimal, it's not obvious, but in binary
it sure is.

So, Starting with the right, counting left, and starting
with the first bit on the right is bit 0, we can see that
bit 7 is the bit that identifies a transaction that's being
rolled back. So, to check if bit 7 is set, you can write:
"where bitand(flag,power(2,7))<>0"

If you ever learn what the meaning of the other bits is,
you can check those too. For example, if bit 28 is set,
that's a transaction with the SERIALIZABLE isolation level.

Hope that helps,

-Mark

good insight but doesn't solve the problem

Gabriel, April 17, 2003 - 4:39 pm UTC

Hello,
I ran the modified sql statement as suggested above, but I received no results:

select sql_text from v$sqlarea where address in (select sql_address from v$session where sid in (select SID from v$transaction,dba_rollback_segs,v$session where SADDR=SES_ADDR and XIDUSN=SEGMENT_ID and
bitand(flag,power(2,7))<>0));

returned no rows when ran repeatedly.

select value rlbk from v$sysstat where name='user rollbacks';
select value cmt from v$sysstat where name='user commits';

continued to show increasing values in both values.

Do you have any other suggestions as to how to find the transactions that roll back? I tried using the Trace Analyzer, TRACNLYZR.sql, but that is not available for 8.0.6. Do you know other packages that I can use on this 8.0.6?

Thank you very much for your effort and for your timely response,

Tom Kyte
April 17, 2003 - 5:57 pm UTC

did you know that you had transactions rolling back?

if so, you must know the session (cause you are doing it as a test).

if so, look at the flag and see what it is for that session, show it to us and lets see what it says....



Hmm...works for me....

Mark J. Bobak, April 18, 2003 - 4:25 am UTC

Gabriel,

I just tested it, and it worked fine for me, but I used
a different SQL than you. I'll include it below. Also,
note that if it's a user rollback, then the 'current'
statement will be 'rollback', NOT the original DML, which
is probably not what you were looking for. I tried
unsucessfully to use V$SESSION.PREV_SQL_ADDR and
V$SESSION.PREV_HASH_VALUE to get the original DML. Not
sure why that didn't work. Tom, any insights there? Does
PREV_SQL_ADDR and PREV_HASH_VALUE not work the way I'm
expecting?

Here is my SQL, which works, but only displays 'rollback':
select sql_text
from v$sqlarea vsa,
v$session vs,
v$transaction vt
where vsa.address = vs.sql_address
and vsa.hash_value = vs.sql_hash_value
and vs.taddr = vt.addr
and bitand(vt.flag,power(2,7))>0;

Hope that helps!

-Mark

Gabriel, April 22, 2003 - 4:11 pm UTC

This sql is created by an application that opens more than one session at a time, therefore studying any one session won't help me much, I have to look at all of them.
Hello I placed a job in my database when the transactions were created. This is the text of the job that executes every minute:

insert into alternate_all_text
select sql_text,USERNAME,SID,SERIAL#,OSUSER,TERMINAL, flag,'all'
from v$sqlarea vsa,
v$session vs,
v$transaction vt
where vsa.address = vs.sql_address
and vsa.hash_value = vs.sql_hash_value
and vs.taddr = vt.addr;

insert into alternate_all_text
select sql_text,USERNAME,SID,SERIAL#,OSUSER,TERMINAL, flag,'rolling_back'
from v$sqlarea vsa,
v$session vs,
v$transaction vt
where vsa.address = vs.sql_address
and vsa.hash_value = vs.sql_hash_value
and vs.taddr = vt.addr
and bitand(vt.flag,power(2,7))>0;

insert into all_text
select USERNAME,SID,SERIAL#,OSUSER,TERMINAL,SEGMENT_NAME, flag
from v$transaction,dba_rollback_segs,v$session
where SADDR=SES_ADDR and
XIDUSN=SEGMENT_ID;

insert into trace_table(sqldate,text)
select sysdate,sql_text
from v$sqlarea
where address in (select sql_address
from v$session where sid in (select SID
from v$transaction,dba_rollback_segs,v$session
where SADDR=SES_ADDR and
XIDUSN=SEGMENT_ID and
bitand(flag,power(2,7))<>0));
insert into trace_table(text, sqldate, user_rollback) select 'user_rollback_value',sysdate,value from v$sysstat where name='user rollbacks';
insert into trace_table(text, sqldate, user_commit) select 'user_commit_value',sysdate,value from v$sysstat where name='user commits';
commit;

Here is some data from the tables that I insert data into:

select distinct flag distinct_flag_all_text from all_text;
select count (*) count_all_alternate_all_text from alternate_all_text where data_indicator='all';
select count (*) count_rback_alternate_all_text from alternate_all_text where data_indicator='rolling_back';
select min(user_rollback) min_user_rollback from trace_table;
select max(user_rollback) max_user_rollback from trace_table;
select min (user_commit) min_user_commit from trace_table;
select max (user_commit) max_user_commit from trace_table;

DISTINCT_FLAG_ALL_TEXT
----------------------
7683
COUNT_ALL_ALTERNATE_ALL_TEXT
----------------------------
79
COUNT_RBACK_ALTERNATE_ALL_TEXT
------------------------------
0
MIN_USER_ROLLBACK
-----------------
261
MAX_USER_ROLLBACK
-----------------
3663
MIN_USER_COMMIT
---------------
1022
MAX_USER_COMMIT
---------------
3656

As you can see there are user rollbacks, and commits but the only flag captured is 7683. Therefore, again, no rolling back transactions were captured by any query. I received a suggestion to use the decreasing value of used_ublk from v$transaction, but I don't see how in the context of this application.

Thank you very much,

Tom Kyte
April 22, 2003 - 9:39 pm UTC

????

Would you like to have more information?

Gabriel, April 23, 2003 - 8:41 am UTC

Hello Tom,
Would you like to have more information? If yes, what else would you need to know? And what do you mean by '????'

Thank you,

Tom Kyte
April 23, 2003 - 7:03 pm UTC

I'm basically saying with the ????? that i have no idea what you might be after.

is there a question buried in that big list of facts?

Would you like to have more information?

Gabriel, April 23, 2003 - 10:16 am UTC

Hello Tom,
Would you like to have more information? If yes, what else would you need to know? And what do you mean by '????'

Thank you,

User followup

Gabriel, April 30, 2003 - 10:05 am UTC

Hello Tom

And thank you for your quick answers.
I am after the sql text of transactions that are performing rollback. The application developpers use a tool to generate and manage the sql text develloped, therefore they don't see the sql text. It is my task as a DBA to find the text of the transactions that are rolling back.

With the facts that I sent I am trying to ilustrate the fact that although rollback is occuring as illustrated by the increasing value in user rollback, the transactions are not captured when using:

insert into alternate_all_text
select sql_text,USERNAME,SID,SERIAL#,OSUSER,TERMINAL, flag,'rolling_back'
from v$sqlarea vsa,
v$session vs,
v$transaction vt
where vsa.address = vs.sql_address
and vsa.hash_value = vs.sql_hash_value
and vs.taddr = vt.addr
and bitand(vt.flag,power(2,7))>0;

As you can see the only flag captured for all transactions is 7683. Either this method doesn't work or my implementation is wrong.

I intend to run another test.
I placed the following query on a job that executes every 20 secs:

insert into rlbk_used_ublk
SELECT s.sid, s.sql_address, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec, sysdate
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr;

commit;

insert into rlbk_text select sql_text, address
from v$sqlarea where address in (select sql_address from rlbk_used_ublk);

commit;

I intend to look at:

s.sid, s.sql_address, s.serial#, s.username

where the value of used_ublk is decreasing and then look for the address of all sql executed during the test and get the text this way. This seems to be a very labour intensive way of solving my problem of finding the text of sql that is rolling back.

Additional information:

The application opens several sessions and connects using multiple users to complete the work.

My question is:

Can you think of any other way of capturing the text of sql that is rolling back?



Tom Kyte
April 30, 2003 - 10:12 am UTC

a transaction is made up of many statements

there is no single "statement" that is rolling back. You cannot get the sql they used in that "transaction". you see nothing because of your joins, there isn't anything to join to necessarily.


I love those tools that "protect" those poor developers from the dreaded SQL thing -- i understand it is really far too complex....

awesome that it opens several sessions -- that is very transactional of it. so scalable as well (not).

I feel sorry for you -- really I do. This needs to be fixed in the application -- the application needs to add a bit of instrumentation (ala dbms_application_info.set_client_info perhaps) to allow you to see what ACTION they are performing.

Why is it your task as DBA? It might be your task to alert the developers to a performance issue (rolling back) but it would be the development staffs job to fix their BUG wouldn't it.


Thank you very much

Gabriel, April 30, 2003 - 1:37 pm UTC

Thank you very much,

Very good insight.

how much rollback do I need?

A reader, May 01, 2003 - 4:23 am UTC

Hi

Is there a way to know how much rollback do I need for a transaction?
If we must run it in order to know it, is it possible to extrapolate? For example if I have a massive update of 1 million rows, is there a way such as

run the update
check how many rows have been updated then from there get the used_ublk value so if we updated 100000 rows and used 1000 blocks we can say 1 million rows more or less needs 10000 blocks and kill the transaction? Or even better create an empty copy of original table, insert 100000 rows there and run the update totally and extrapolate from there...?

Or is this totally out of question :?

Tom Kyte
May 01, 2003 - 12:05 pm UTC

if I had a massive update of a million rows - I would give serious consideration to this:

ASSUMING original table is partitioned:


create table new_table
as
select <modified_rows> in parallel with nologging


create indexes in parallel/nologging

alter table old_table exchange the partition with this new table
drop new table;


ASSUMING original table is not partitioned


create table new_table
as
select <modified_rows> in parallel with nologging

create indexes in parallel/nologging

grant on new table
drop old table
rename new table to old table




vs trying to update and maintain the indexes (which might tend to be the largest, and most unpredicable generator of UNDO and REDO)...


Extrapolation is a "maybe" idea. maybe it'll work, maybe it won't. depends on how bad the indexes get hit. If the table has no indexes (or the columns being modified are not indexed), extrapolation should be workable. Indexes are the unknown variant here.

How about a null taddr?

Doug, June 12, 2003 - 2:17 pm UTC

I'm trying to drop a schema that is "currently connected" even though all the sessions connected to it are marked for "KILL". I figured I'd look for things rolling back but the taddr is null in the v$sesion columns. Why would that be?

Tom Kyte
June 12, 2003 - 3:51 pm UTC

we are waiting for the client application to do something in the database so we can report back to it "i'm sorry, you are dead, you were killed", rather then just report back "ora-3113 eof on communication channel" which most people would interpret as "bug"

BITAND(VT.FLAG,POWER(2,7)) = 0

Doug, June 28, 2003 - 4:48 pm UTC

I have a flag
4201987 which shows a statement which is a huge select for update statement.
The session, marked for kill, is holding about 13 different locks, many of them TOs on temp tables.
If this transaction is not rolling back, what is it doing? (it's been this way for an hour).. An online index creation on the same table appears to blocked on an enqueue waiting for it.

Tom Kyte
June 28, 2003 - 8:36 pm UTC

well, it is probably too late now, but did v$session_event have anything interesting? was the back end chugging away or just sitting there?

Too late - but how about next time -

Doug, June 29, 2003 - 5:58 pm UTC

Well - as you mentioned - this situation has resolved itself. I took the nasty route of killing the dedicated server process to speed things along. I know you hate that. But.. let me prepare for next time. Basically what I noticed was that in v$transaction, the USED_UBLK was gradually going up from 20 - 90 over the course of an hour. The java app was doing absolutely nothing, and there were only 3 connections to the entire database. Mine, the one that was killed, and the one trying to build the index. I didn't think to look at session event. What would you suspect? Thanks.. Dc.

What about a session killed?

Michel CADOT, July 21, 2003 - 8:22 am UTC

Hi Tom,

When a session is killed, taddr in v$session is "set" to null and the row in the v$transaction is deleted.
So, how to follow the rollback make for this session?
In addition, who executes this rollback? The server process (dedicated or shared) or a background process?

Thanks
Michel

Tom Kyte
July 21, 2003 - 8:50 am UTC

that has not been my experience.

Complement to my previous message

Michel CADOT, July 22, 2003 - 2:53 am UTC

Hi Tom,

I try it on my laptop (Windows NT4, Oracle8iR3).

Session 1: TEST
----------

20:03:13 mike=MIKE/ILLUVATAR TEST>update guichets set c_code_postal='00000';

26295 rows updated.

Elapsed: 00:01:39.52
20:04:52 mike=MIKE/ILLUVATAR TEST>
20:04:53 mike=MIKE/ILLUVATAR TEST>rollback;

Rollback complete.

Elapsed: 00:01:00.47
20:05:54 mike=MIKE/ILLUVATAR TEST>
20:06:11 mike=MIKE/ILLUVATAR TEST>update guichets set c_code_postal='00000';

26295 rows updated.

Elapsed: 00:01:18.53
20:07:34 mike=MIKE/ILLUVATAR TEST>
20:07:59 mike=MIKE/ILLUVATAR TEST>select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed


Elapsed: 00:00:00.71
20:08:05 mike=MIKE/ILLUVATAR TEST>


Session 2: SYSTEM
----------

20:07:43 =MIKE/ILLUVATAR SYSTEM>select s.sid, s.username,
20:07:43 2 t.addr tx, t.start_time, t.status, t.start_scnb,
20:07:43 3 decode(t.noundo, 'YES', 'No RB', nvl(r.name, 'No RB')) rollback,
20:07:43 4 round(rs.rssize/1024) rssize,
20:07:43 5 decode(substr(to_char(t.flag,'fm0000000X'),7,1),'8','YES',NULL) rollbacking
20:07:43 6 from v$rollstat rs, v$rollname r, v$session s, v$transaction t
20:07:43 7 where r.usn (+) = t.xidusn
20:07:43 8 and rs.usn (+) = t.xidusn
20:07:43 9 and s.saddr = t.ses_addr
20:07:44 10 order by 7, 6, 1
20:07:44 11 /
Sid Utilisateur Tx Addr Tx Start Time Tx Status Tx SCN Rollback Taille (Ko) RB?
------- --------------- -------- -------------------- ---------------- ----------- ----------- ------------ ---
11 TEST 02BF2448 07/21/03 20:06:12 ACTIVE 6449420 RS_1 2,516

1 row selected.

Elapsed: 00:00:00.00
20:07:44 =MIKE/ILLUVATAR SYSTEM>select sid, serial#, username, taddr from v$session where sid=11;
Sid SERIAL# Utilisateur TADDR
------- ---------- --------------- --------
11 142 TEST 02BF2448

1 row selected.

Elapsed: 00:00:00.10
20:07:44 =MIKE/ILLUVATAR SYSTEM>select * from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC Tx Status
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
Tx Start Time Tx SCN START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR
-------------------- ----------- ---------- ---------- ------------ ------------ ------------ ------------ --------
FLAG SPA REC NOU PTX PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W
---------- --- --- --- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE
---------- ---------- ---------- ---------- ---------- ----------
02BF2448 1 33 29 3 6046 81 1 ACTIVE
07/21/03 20:06:12 6449420 0 0 3 5 64 1 02A8B7B4
7683 NO NO NO NO 0 0 0 0 0 0 6447653 0
612 26295 81029 534 969 14

1 row selected.

Elapsed: 00:00:00.30
20:07:45 =MIKE/ILLUVATAR SYSTEM>alter system kill session '11,142';

System altered.

Elapsed: 00:00:00.80
20:07:45 =MIKE/ILLUVATAR SYSTEM>select s.sid, s.username,
20:07:45 2 t.addr tx, t.start_time, t.status, t.start_scnb,
20:07:46 3 decode(t.noundo, 'YES', 'No RB', nvl(r.name, 'No RB')) rollback,
20:07:47 4 round(rs.rssize/1024) rssize,
20:07:47 5 decode(substr(to_char(t.flag,'fm0000000X'),7,1),'8','YES',NULL) rollbacking
20:07:48 6 from v$rollstat rs, v$rollname r, v$session s, v$transaction t
20:07:49 7 where r.usn (+) = t.xidusn
20:07:50 8 and rs.usn (+) = t.xidusn
20:07:50 9 and s.saddr = t.ses_addr
20:07:51 10 order by 7, 6, 1
20:07:52 11 /

no rows selected

Elapsed: 00:00:00.81
20:07:52 =MIKE/ILLUVATAR SYSTEM>select sid, serial#, username, taddr from v$session where sid=11;
Sid SERIAL# Utilisateur TADDR
------- ---------- --------------- --------
11 142 TEST

1 row selected.

Elapsed: 00:00:00.71
20:07:53 =MIKE/ILLUVATAR SYSTEM>select * from v$transaction;

no rows selected

Elapsed: 00:00:00.41
20:07:55 =MIKE/ILLUVATAR SYSTEM>

Then during about a minute the process oracle.exe takes 75% of CPU and my disk works.
As you can see, I killed the TEST session after the second "update" and just after "alter system kill session", taddr in v$session is null and the row in v$transaction no more exists.

Thanks for your help
Michel

Remaining time estimation

Roberto Zanfrini, October 28, 2003 - 10:12 am UTC

<predelta.sql>
DEFINE p_sid=   &1
DEFINE curtime= TO_NUMBER(NULL)
DEFINE curblks= TO_NUMBER(NULL)
DEFINE usdblks= GREATEST(used_ublk,0)

COLUMN curtime     NEW_VALUE curtime  NOPRINT
COLUMN dlttime                        NOPRINT
COLUMN curblks     NEW_VALUE curblks  NOPRINT
COLUMN dltblks                        NOPRINT
COLUMN "Blks/Sec"  FORMAT 999,990.0

<delta.sql>
SELECT
  ((Sysdate-TRUNC(Sysdate))*86400) curtime,
  ((Sysdate-TRUNC(Sysdate))*86400)-&curtime dlttime,
  &usdblks curblks,
  &usdblks "Blks",
  ((&curblks-&usdblks)) dltblks,
  (&curblks-&usdblks)/(((Sysdate-TRUNC(Sysdate))*86400)-&curtime) "Blks/Sec",
  TO_CHAR(
   TRUNC(Sysdate)+
   (DECODE(&curblks-&usdblks, 0, TO_NUMBER(NULL),
    &usdblks/((&curblks-&usdblks)/
     (((Sysdate-TRUNC(Sysdate))*86400)-&curtime)))/86400),
   'mi:ss') " Time"
 FROM v_transaction t, v$session s
 WHERE s.sid=&p_sid AND s.taddr=t.addr;

SQL> @predelta
SQL> @delta

      Blks   Blks/Sec  Time
---------- ---------- -----
        75

SQL> @delta

      Blks   Blks/Sec  Time
---------- ---------- -----
        70        0.4 02:48

SQL> @delta

      Blks   Blks/Sec  Time
---------- ---------- -----
        60        1.0 01:00

etc... 

What affects the performance of rollback.

Vladimir, March 15, 2005 - 7:29 am UTC

Hi Tom
Thank you for providing information on how to determine how much time left to finish a transaction rollback.
Is there any way to increase speed of rolling back changes?
Let's say I figured that it takes 500 blocks off used_ublk per minute so it means that 500000 will be rolled back in 1000 minutes. Can I increase the speed?
Does FAST_START_PARALLEL_ROLLBACK parameter impact the rollback speed?
Thank you for your time and effort.

Tom Kyte
March 15, 2005 - 8:26 am UTC

In 8i and above PMON lets transaction recovery of "killed" sessions be done by SMON which may use fast start parallel rollback if necessary. This frees up locks held by the killed process immediately. (so if you kill it, the blocks will be rolled back upon demand -- as people try to access them)

otherwise, there is an undocumented (as if 8i) parameter cleanup rollback entries that controls how many blocks at a time pmon processes for a rollback of a statement.

What are these undocumented parameters?

A reader, March 15, 2005 - 7:59 pm UTC

It is 8.1.7 server.
The problem is that the performance of the system degraded drastically while SMON rolls transaction back. Yes, there are no locks. But all processes take 8-10 longer than usual.

So I think I am interested in the undocumented parameters that may help to finish rollback process as soon as possible

Thank you

Tom Kyte
March 15, 2005 - 9:37 pm UTC

it is most likely the SPEED of the rollback that is making other things slower (eg: making it faster would make it *worse*)

I do not discuss undocumented parameters - you are free however to take it up with support.


I would be much more seriously concerned that this is a RECURRING PROBLEM, rather than an isolated event however. Why do you do a ton of work (make things slow) to undo this ton of work (make things slow) more than once in a blue moon?

Got it

Vladimir, March 15, 2005 - 11:10 pm UTC

Thank god it is a one time event.
I read a lot on your insert instead of update/delete approach. Very useful.
Thank you


update million on partitioned table

phil, April 13, 2005 - 10:17 pm UTC

hi tom,

above you say

"if I had a massive update of a million rows - I would give serious consideration
to this:

ASSUMING original table is partitioned:


create table new_table
as
select <modified_rows> in parallel with nologging


create indexes in parallel/nologging

alter table old_table exchange the partition with this new table
drop new table;"

how does this work?

If you create new table ( create table as select ) based on old table you get a table with no partitions just a pk and rows. I do not uderstand what the alter table exchange is going to do.

I have tried to find an example of updating millions of rows in a partitioned table but I cannot find one. Are the steps I need to take as above ? Where does the creation of local indexes come in ?

What I am asking for is an example of the above.

regards

Tom Kyte
April 14, 2005 - 7:29 am UTC

create table as select supports all organizations, you can partition it, IOT it, cluster it, whatever it.


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t select to_date( '10-mar-2003', 'dd-mon-yyyy' )+mod(rownum,10), rownum,rownum
  2   from all_users;
 
25 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table new_t ( dt, x, y )
  2  PARTITION BY RANGE (dt)
  3  (
  4    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
  5    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
  6    PARTITION junk VALUES LESS THAN (MAXVALUE)
  7  )
  8  as select * from t;
 
Table created.


but in context of the answer there were two cases (and the partitioned one could have been more clear on my part)

a) table is partitioned, fine -- select rows out of partition that you want to keep, put them into a table, index it, swap the smaller new table with the big partition.

b) table is not partitioned..... 

update million on partitioned table <part 2>

Phil, April 14, 2005 - 1:07 am UTC

After re-read - I am probably in the wrong thread with this, so I will move my question to more relevant one

regards
phil

Used Blocks in RBS when Insert /*+ APPEND */ into table select

Vin, April 14, 2005 - 9:45 am UTC

Givel below are the sql's I use to get the above information.

--No of Undo blocks generated in the transaction
select used_ublk from v$transaction;

--Map that to a RBS
Select b.segment_name as "RB Name",
sum(c.used_ublk) as Total_Blocks
From dba_rollback_segs b, v$transaction c
Where b.segment_id = c.xidusn
Group by segment_name;

--Objects Existing in Rollback Segments
column os_user_name format a10
column oracle_username format a10
column owner format a10
column object_name format a25
column object_type format a10
column segment_name format a10
column used_urec format 9999999
Select substr(a.os_user_name,1,8) "OS User",
substr(a.oracle_username,1,8) "DB User",
substr(b.owner,1,8) "Schema",
substr(b.object_name,1,25) "Object Name",
substr(b.object_type,1,10) "Type",
substr(c.segment_name,1,5) "RBS",
substr(d.used_urec,1,12) "# of Records"
From v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
Where a.object_id = b.object_id
And a.xidusn = c.segment_id
And a.xidusn = d.xidusn
And a.xidslot = d.xidslot
And d.addr = e.taddr;

The last SQL gives me the number of records which could be mapped to the blocks in the RBS.
Question:For a table A with 100000 rows, when a Insert /*+ APPEND */ into A Select * From B sql is executed, the number of rows showed up towords the end of execution of the Insert is 4 times the number of records for tables with LOB's and 3 times the number of records without LOB's.
Could you please explain why this is so.
Appreciate your valuable time and effort in helping all with your refined knowledge.


Tom Kyte
April 14, 2005 - 9:59 am UTC

lobs have an index on them and indexes are ALWAYS generating redo while being maintained.

with a table with a single lob, there are three segments:

a) the table
b) the lob index
c) the lob segment

the lob index must generate undo, like any index will.

The right way to see the how much rollback a session has to do

Ovidiu T, August 12, 2005 - 9:06 pm UTC

Hi Tom,

I was using your recommended query to get the number of rollback segments a session has to do
select sid, used_ublk from v$transaction, v$session
where v$transaction.addr = v$session.taddr
and sid = ...;

and I noticed that one of our sessions was returning while the transaction was going on something like:
1
1
22345
1
1
22678
...

It seems that if I do the join as:
select sid, used_ublk from v$transaction, v$session
where v$transaction.ses_addr = v$session.saddr
and sid = ….;
I get more than 1 transactions and the sum of used_ublk looks as expected.

So my question is two fold:
1. is the second query the right way to see all used_ublk under a session?
2. what are these multiple transactions listed under my session? I don't have any autonomous transactions in the session, just simple pl/sql (packages).
I though for a moment that I was seeing the one current statement executed (with a save point inserted before each one by Oracle if I'm not mistaken) inside the session, but then I saw three transactions under the same session as in:

select sid, used_ublk, ses_addr, saddr, v$transaction.addr, taddr from v$transaction, v$session
where v$transaction.ses_addr = v$session.saddr
and sid in (17);

17 1 00000003BA569E38 00000003BA569E38 00000003BD6F61E8 00000003BD6F61E8
17 28696 00000003BA569E38 00000003BA569E38 00000003BD6F7B78 00000003BD6F61E8
17 2 00000003BA569E38 00000003BA569E38 00000003BD6F8840 00000003BD6F61E8

Many thanks for the answers and the site!

Tom Kyte
August 13, 2005 - 9:55 am UTC

are you saying taddr was duplicated in v$transaction?

The right way to see the how much rollback a session has to do

Ovidiu T, August 14, 2005 - 3:07 pm UTC

Yes, taddr was duplicated in v$transaction (three values at the moment I did the select that I posted above).

Tom Kyte
August 14, 2005 - 8:53 pm UTC

that doesn't sound right, taddr should point to the transaction object for a session with a transaction, three of them pointing to the "same thing" doesn't make sense.

I'll have to play with that and parallel execution though.

I don't see duplicate taddr.

Sai, August 15, 2005 - 3:56 am UTC

select sid, command, taddr from v$session where sid in
(select sid from v$px_session where qcsid=224)
/

SID COMMAND TADDR
----- ---------- ----------------
166 2 0000040604192608
224 2 000004060417C7B8
231 2 0000040604180318
240 2 000004060417A418
345 2 00000406041996D8

Each parallel DML session has a different TADDR.


The right way to see the how much rollback a session has to do

Ovidiu T, August 15, 2005 - 3:22 pm UTC

Oops,
Sorry Tom, I was *not* right on my answer above.
You asked if taddr was duplicated in v$transaction.
But taddr was listed from v$session and it is unique in v$session.
What is actually duplicate in v$transaction is ses_addr.

For example now:
select ses_addr, count(*) from v$transaction
group by ses_addr;
returns:
00000003BA577C08 1
00000003BA589938 1
00000003BA598C28 2
00000003BA5B6778 1
00000003BA5BA6D8 1
00000003BA5BB168 1
00000003BA606588 1

In other words if I start from the session (v$session) and go to the transaction through taddr I only get 1 session - 1 transaction.
If I start from transactions (v$transaction) and go to the session through ses_addr then I can get many transactions pointing to the same session. Hence my original follow up here.

Tom Kyte
August 15, 2005 - 10:36 pm UTC

join them by v$session.taddr -> v$transaction.addr (as stated above)

Re: The right way to see the how much rollback a session has to do

Sai, August 15, 2005 - 8:33 pm UTC

It does look like a some weird phenomena. It would be interesting to see what does the following query return in your environment:

select s.sid, s.serial#, s.command, t.addr, t.ses_addr, t.xidusn, t.space, t.recursive
from
v$session s, v$transaction t
where
t.ses_addr = s.saddr(+)
order by t.ses_addr
/

Re: The right way to see the how much rollback a session has to do

Ovidiu T, August 16, 2005 - 3:16 pm UTC

That's how I used to do the join: by v$session.taddr -> v$transaction.addr, but I am getting this odd set of results running this query for the *same* session repeateadly:
1
1
22345
1
1
22678
...

I don't understand the results.

Thanks Sai - I will run the query later today (and post the result) when I'll have the process that produces this kind of results running.

Tom Kyte
August 17, 2005 - 12:13 pm UTC

I don't understand what i'm looking at -- no column name or anything.

Re: The right way to see the how much rollback a session has to do

Ovidiu T, August 17, 2005 - 10:42 pm UTC

This is the select that I ran for the same session while doing a long transaction:
select sid, used_ublk from v$transaction, v$session
where v$transaction.addr = v$session.taddr
and sid = 77;
These were the results:
77 1
after 1 sec ran again:
77 1
after 1 sec ran again:
77 22345
after 1 sec ran again:
77 1
after 1 sec ran again:
77 1
after 1 sec ran again:
77 22678



Tom Kyte
August 18, 2005 - 3:47 pm UTC

what is in the long transaction? any recursive sql (space management, sequences, autonomous transactions.......)??

USED_UREC description

Shivaswamy, October 14, 2005 - 10:49 am UTC

I wanted to understand what does sys.v$transaction.USED_UREC mean. I see description from documentation (Oracle9i Database Reference Release 2 (9.2)Part Number A96536-02) for this column as "Number of undo records used ". I think undo records is the number of records that are bieng inserted/updated/deleted - is it not?

And here is one test case:
09:46:33 >drop table test;
Table dropped.
09:46:37 >create table test as select * from all_objects where 1=2;
Table created.
09:46:37 >select
09:46:45 2 sid "Your SID",
09:46:45 3 serial# "Your Serial#"
09:46:45 4 from
09:46:45 5 sys.v_$session
09:46:45 6 where
09:46:45 7 sid = (select sid from sys.v_$mystat where rownum = 1);

Your SID Your Serial#
---------- ------------
19 34065

1 row selected.
09:46:46 >insert into test (select * from all_objects where rownum < 11);
10 rows created.

09:47:07 >select sid, t.start_time, r.name,
09:47:21 2 t.used_urec "Rec"
09:47:21 3 from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
09:47:21 4 where t.xidusn = r.usn
09:47:21 5 and t.ses_addr = s.saddr
09:47:21 6 and s.sid=19;

SID START_TIME NAME Rec
---- ----------------- ---------- ------------
19 10/14/05 08:46:24 _SYSSMU50$ 1

Can you please explain?

At times I have seen when I was thinking I am inserting 1 million rows (eventhough it is a table with no LOB segment), I was finding about 2 million records in the v$transaction.used_urec.

Thanks in advance.



Tom Kyte
October 14, 2005 - 5:11 pm UTC

... I think 
undo records is the number of records that are bieng inserted/updated/deleted - 
is it not? ...

I think you just demonstrated "not" - it is not - you inserted 10 records.
You have used_urec=1.

It is undo records - a data structure used in rolling back.  It is not directly associated with number of records modified - nor the number of statements processed.  This demo here will show why bulk operations are more efficient though - as the statements will increment the number of records by one, or more - so if you insert 1,000 records a row at a time, you'll have 1,000 used_urec.  If you insert 1,000 records in a single statement, you might only have a few.


ops$tkyte@ORA9IR2> create table t ( x int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA9IR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA9IR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA9IR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA9IR2> select used_urec from v$transaction;

 USED_UREC
----------
         4

ops$tkyte@ORA9IR2> insert into t select rownum from all_users;

35 rows created.

ops$tkyte@ORA9IR2> select used_urec from v$transaction;

 USED_UREC
----------
         5

ops$tkyte@ORA9IR2> insert into t select rownum from all_objects;

30717 rows created.

ops$tkyte@ORA9IR2> select used_urec from v$transaction;

 USED_UREC
----------
       172

 

A reader, October 14, 2005 - 6:06 pm UTC

Tom,

May be sometimes what we think right we believe too much. Thanks for reminding.

Now, since used_urec is not the number of rows inserted/updated/deleted, is there a way to find out ho w many records are there on undo segment - may be to see how much the job has progressed or to see how much of undo recovery is left out for transaction that is killed and rolling back?

Thanks,
Shivaswamy


Tom Kyte
October 14, 2005 - 6:16 pm UTC

no, not really - not "records" as in rows in a table.

but monitoring used_ublk during a rollback can be useful in monitoring the process.

How to estimate time to roll back when OS process killed

Srinivas, February 16, 2006 - 7:50 pm UTC

I killed a session that was doing a huge delete and was able to monitor the rollback from v$transaction. Next I killed the shadow process on the db server for the previously killed session. v$transaction is now empty.

Is there a way to monitor the progress of the roll back in this case?

Tom Kyte
February 17, 2006 - 1:29 pm UTC

you lost that ability by removing the transaction entry.

Shailesh, February 17, 2006 - 11:41 am UTC

HiTom,
   You mentioned "So, if you have 500 used_ublks to start,
60 seconds later you have 400". However in my case value in column used_ublk goes on increasing in my case. Could you please explain. I executed this query 
select t.used_ublk,s.sid,s.username from v$transaction t, v$session s where s.taddr = t.addr and s.username = 'BSCS';

 USED_UBLK        SID USERNAME
---------- ---------- ------------------------------
         3       2821 BSCS
       645       2698 BSCS

NCRMPRD:SQL>/

 USED_UBLK        SID USERNAME
---------- ---------- ------------------------------
         6       2821 BSCS
       651       2698 BSCS

NCRMPRD:SQL>/

 USED_UBLK        SID USERNAME
---------- ---------- ------------------------------
         2       2821 BSCS
       655       2698 BSCS

How do we estimate how much amount of undo blocks are used?

 

Tom Kyte
February 17, 2006 - 2:57 pm UTC

what is your case - is that session actually killed

Shailesh, March 10, 2006 - 6:45 pm UTC

We were refreshing snapshot in our schema (BSCS) from remote database.

Tom Kyte
March 10, 2006 - 8:38 pm UTC

so, the refresh is still running?

A reader, March 11, 2006 - 8:52 pm UTC

Will monitor snapshot refresh and update this thread.



Nag, May 15, 2008 - 12:29 pm UTC

Hi Tom,

I have a similar situation.

The session which was running for 35 hours and blocking few other sessions has been killed by the application first and then from the database side using alter system kill session which was marked for killed. After waiting for 5 hrs and found that it was not doing any rollback, the OS process has been killed using kill -9 <pid>. The PID has gone, but still the database session has been in there and locking other sessions. Is there any way to clear this session from the database without bouncing the database?

Thanks in advance.

Nag

kill -9 and ALTER SYSTEM KILL SESSION

Swapnil kambli, January 11, 2012 - 9:07 am UTC

Hi Tom,

In 8i We are facing this issue.The database takes too much time before it shuts down for flashcopy.we had implemented alter system kill session script just before the shutdown,it worked for some time but we still face same issue.In alert log we get waiting for "WAITING FOR ACTIVE CALLS TO FINISH".

Could you please comment on difference between oracle's rollback behavior in case of OS level KILL- 9 <SESSION_OS_ID> and ALTER SYSTEM KILL SESSION?


Tom Kyte
January 11, 2012 - 9:51 am UTC

do not use a kill -9.

do use a shutdown immediate. that will cause all sessions to rollback, and then exit and then shutdown. You want a clean shutdown.


why are you shutting down for a 'flashcopy', why not just backup your database hot?????

or put the database into hot backup mode, split mirrors, take it out of hot backup mode and then backup the split off mirror part and re-silver them back in if you don't want to do an online backup.

making a flashcopy for cold backups is not very efficient. It requires an outage and it shouldn't.

Swapnil kambli, January 11, 2012 - 10:27 am UTC

Thanks for your answer.

My client is using that same procedure for "ages" now!I doubt they are going to change it but I will try to convince.They do the flashcopy then start their batch loading.So delay in shutting down databases in turn delays the batch... that's the issue.

Can you please explain how oracle proceed when it find any OS level killed session which was performing DML?
Tom Kyte
January 11, 2012 - 3:11 pm UTC

do not kill things, it is not the way to do it.

pmon would find it, smon would clean it up. smon would have to do the rollback instead of the user process.

just shutdown immediate.

or if you want a really 'fast' shutdown - shutdown abort, start up with restricted session, shutdown normal.

Rollback growing....growing..

Jayadevan, July 31, 2012 - 2:41 am UTC

Hi Tom,
We have a database that is about 18 GB in size. It has been running without any issues for a few months. I keep track of space usage and the undo file is usually under a couple of GB.
Yesterday, we ran into a space issue on that machine and I saw that the undo file had grown to 9 GB. This is our staging environment. I switched undo and the new file was a few MB in size. I dropped the old undo table space. This morning, the undo had again grown to 9 GB. How can I find out what was causing this sudden growth of undo? Which SQL, fired from which application etc? I tried v$sql, v$session etc using the queries posted in this thread. There were no records in V$TRANSACTION and hence I did not get any data.
Tom Kyte
July 31, 2012 - 12:32 pm UTC

you could use logminer to see if something updated massive amounts of data - however, it doesn't have to be that at all.

All it would take is one tiny transaction to remain open for a long period of time.

undo is managed in a circular buffer - we have an undo segment made up of extents. when we get to the "end" of the segment - we try to wrap back around to the first extent if we can. IF there is an active transaction in that first extent - we cannot wrap around and what we'll do is extend that segment - add another extent. We'll keep trying to wrap around to the first extent - but won't be able to unless that transaction commits.

so, my theory is, someone did a small transaction - and left their terminal for a long period of time. that wedged the undo segment so it could never wrap around - we had to extend. the undo tablespace would grow and grow and grow - and then they finally came back from lunch or whatever and committed.


now, if you think it was a single large transaction - you could enable the resource manager and set up an undo quota. anything that exceeds that would fail - you'd find your culprit.

or you could set up an on logoff trigger and record the amount of undo generated by the session (including any other details you wanted) for looking at later.

Wow

Jayadevan, August 01, 2012 - 3:35 am UTC

Hi Tom,
The problem repeated today , and the transaction (had already generated 1+ GB of undo) was active when I noticed the problem. It was indeed a simple update (the SQL updates one record in one table). This is done in the beginning of a batch job that takes a long time to finish.
Thank you

time out parameter

Jayadevan, August 01, 2012 - 5:33 am UTC

Hi Tom,
A couple of questions to follow up.
1) Does this mean that if a developer starts an SQL Plus session, does a single row update on a db which is write-heavy, forgets about it and goes away on vacation - it can
cause Oracle to crash or throw unable to extend undo error?
2) Is there a way to set a tramsaction timeout value in the db?
Thanks
Tom Kyte
August 01, 2012 - 7:00 am UTC

1) we won't crash, but we might run out of undo space, yes.

2) sort of, you can set an idle timeout - but not just for something "in a transaction" - it would be an idle timeout for any session.

Idle timeout

Jayadevan, August 01, 2012 - 11:36 pm UTC

Hi Tom,
Thanks for clarifying that. If I set an idle timeout parameter, will it apply to such 'forgotten' or long-running transactions also? Since those are 'active', won't they continue?
Tom Kyte
August 02, 2012 - 9:07 am UTC

they won't be active, they'll be idle.

if they were active, the person wouldn't/couldn't have left the keyboard, they'd be running a command.

shutdown immediate does not wait for rollback to complete

Kunal, August 21, 2013 - 9:13 pm UTC

I ran a big tnx and the used__ublk showed more than 800000 and when i issues shutdown immediate, the db went down after 4 mins and when i started the db and checked if the rollback is done, then i saw that its still running.
i used this query to check:

select inst_id,state, undoblocksdone, undoblockstotal, undoblocksdone / undoblockstotal * 100 from gv$fast_start_transactions

Ideally the shutdown immediate should wait for the whole rollback to complete. Please let me know if i am wrong anywhere.
Tom Kyte
August 28, 2013 - 6:00 pm UTC

why should it?

shutdown immediate makes sure that files do not need recovery (redo) - that everything is consistent. it checkpoints after all active statements complete then shuts down.

we do block level rollback if necessary to provide immediate access to objects when starting up.

why would you want to purposely incur downtime when you don't have to?

Jump on used_ublk

Joao Prates, April 28, 2014 - 12:54 pm UTC

Hi Tom,

The background info/facts:

We had a merge statement that was taking far too long to complete, several days actually.

Looking at the used_ublk value of just 2 blocks changed we decided to kill the session and try to figure out what the heck was going on.

With only 2 blocks used the rollback should be quick, we assumed. It was weird as hell that something running for days had only changed 2 blocks... but we trusted the used_ublk info and killed the session.

Much to our surprise, only a few seconds after we issued the kill, the used_ublk jumped from 2 to several hundred thousands... over 400000 blocks... and of course the rollback took several hours to complete.

The question:

What could have possibly happened, what can you speculate was happening, that could have this behavior?

Is it normal for the used_ublk to report an incorrect number of blocks used? Was it real the 2 blocks info but something we are missing made it jump to over 400k blocks?

Thanks in advance for your assistance.

TABLE SHRINK undo usage not showing up in V$TRANSACTION

Syltrem, August 14, 2014 - 8:35 pm UTC

Hi Tom, I've been reading you for years and always learning !

If I do the following :

ALTER TABLE MYTABLE SHRINK SPACE;

This will generate huge amounts of undo and yet, it is not trackable in V$TRANSACTION because this operation commits frequently (I was told so by Oracle support).

The following query tells me how much undo the entire session has been doing in total, and I can see this increasing as the shrink operation runs (10g) :

select value from v$sesstat s,v$statname n where name = 'undo change vector size' and s.statistic#=n.statistic#  and s.sid=70; 

My problem is that if I find myself in a situation where the undo tablespace fills up, looking at V$TRANSACTION to try and find out who is using lots of undo will not help me here (because the "shrink" session will only show it is using 2-3 undo blocks). And looking at V$SESSSTAT will not help me either, as the value reported is for the entire sesion and does not represent the undo blocks currently allocated.

So at this point I don't know which session is filling up the undo tablespace.

Actual example of a shrink operation running :

SQL> select USED_UBLK, USED_UREC from v$transaction where SES_ADDR=(select SADDR from v$session where sid=70 and serial#=1441); 

USED_UBLK USED_UREC 
---------- ---------- 
1 1 
2 75 

2 ligne(s) sélectionnée(s). 

SQL> select value from v$sesstat s,v$statname n where name = 'undo change vector size' 
2 and s.statistic#=n.statistic# 
3 and s.sid=70; 

VALUE 
---------- 
3694168 

1 ligne sélectionnée. 

The latter value does not necessarily represent the current undo usage at it started counting when the session was connected and many other transactions may have completed before starting the shrink operation.

Is there any way to find out it is session with SID=70 that is using lots of undo, in this situation ? And tell how much undo blocks it is holding ?

Thank you !

PL/SQL consolidating time left to rollback

Alexandre Bertolino Verri, July 19, 2016 - 1:14 pm UTC

Using the info got here I made this pl/sql if anyone want to know sids time left to rollback:

declare
v_blk_1 number;
v_blk_2 number;

cursor c_all is
SELECT vses.sid,vses.username,vses.status,vses.schemaname,vses.osuser,vses.machine,vses.module,vses.action,vtran.used_ublk
FROM V$TRANSACTION vtran,
V$SESSION vses
WHERE vtran.addr = vses.taddr
and vses.status = 'KILLED';

cursor c_sid(sid_ number) is
SELECT vses.sid,vses.username,vses.status,vses.schemaname,vses.osuser,vses.machine,vses.module,vses.action,vtran.used_ublk
FROM V$TRANSACTION vtran,
V$SESSION vses
WHERE vtran.addr = vses.taddr
and vses.status = 'KILLED'
and vses.sid = sid_;
begin

for i in c_all loop
v_blk_1 := i.used_ublk;
DBMS_LOCK.SLEEP(60);

for j in c_sid(i.sid) loop
v_blk_2 := j.used_ublk;
dbms_output.put_line('SID: '|| j.sid || ' - USERNAME: ' || j.username || ' - STATUS: ' || j.status || ' - SCHEMA: ' || j.schemaname || ' - OSUSER: ' ||
' - MACHINE: ' || j.machine || ' - MODULE: ' || j.module || ' - ACTION: ' || ' - BLOCKS TO ROLLBACK: ' || j.used_ublk);
dbms_output.put_line('Estimate Mins Left: '|| abs(trunc((v_blk_1 / (v_blk_2 - v_blk_1)))));
dbms_output.put_line('-------------------------------------------------------------------');
end loop;

end loop;
end;
Chris Saxon
July 20, 2016 - 9:44 am UTC

The formula:

v_blk_1 / (v_blk_2 - v_blk_1)

Looks the wrong way round to me. You want to divide the current ublocks by (starting ublocks - current):
v_blk_2 / (v_blk_1 - v_blk_2)

Also, if you have N killed sessions you'll only get one update every N minutes for each.