Home » RDBMS Server » Server Administration » USED_UREC in v$transaction
USED_UREC in v$transaction [message #219463] Wed, 14 February 2007 09:11 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
Below is a situation:

CREATE TABLE A(B NUMBER, C VARCHAR2(100))

INSERT INTO A VALUES(1, 'H')

SQL:
SELECT USED_UREC
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')

Returns 1
INSERT INTO A VALUES(2, 'C')

SQL:
SELECT USED_UREC
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')

Returns 2


Now I delete all the rows:

DELETE FROM A

SQL:
SELECT USED_UREC
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')

I expected the output as 3, but it returns 4.

Can anyone explain this behaviour?

Thanks in advance
Anand
Re: USED_UREC in v$transaction [message #219466 is a reply to message #219463] Wed, 14 February 2007 09:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I expected the output as 3, but it returns 4.
Could you explain why were you expecting an output of 3?
v$transaction.used_urec is the number of UNDO records used.

[Updated on: Wed, 14 February 2007 09:30]

Report message to a moderator

Re: USED_UREC in v$transaction [message #219498 is a reply to message #219466] Wed, 14 February 2007 11:38 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
I thought the Delete operation would be treated as a single transaction eventhough it has 2 records to delete.

Because if I do an insert like:

INSERT INTO A(B)
SELECT OBJECT_ID FROM ALL_OBJECTS;

In this case USED_UREC shows the value as 1 eventhough thousands of records are inserted.

Re: USED_UREC in v$transaction [message #219782 is a reply to message #219466] Fri, 16 February 2007 01:07 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi Mahesh,
Can you help in clearing this doubt?

Thanks
Anand
Re: USED_UREC in v$transaction [message #219818 is a reply to message #219782] Fri, 16 February 2007 03:51 Go to previous messageGo to next message
oraclearora
Messages: 6
Registered: February 2007
Location: Delhi
Junior Member
USED_UREC is no of UNDO records affected by the transaction so far.In this case it is 4 = 1 + 1 + 2

1+1 is for inserts and 2 for delete.

Check UNDO_UBLK also, that is no of undo blocks held by the transaction
.
SQL> INSERT INTO A VALUES(1, 'H')
2 /

1 row created.

SQL> SELECT USED_UREC,USED_UBLK
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')
/
2 3 4 5 6
USED_UREC USED_UBLK
---------- ----------
1 1

SQL> INSERT INTO A VALUES(2, 'C')
2 /

1 row created.

SQL> SELECT USED_UREC,USED_UBLK
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')
/
2 3 4 5 6
USED_UREC USED_UBLK
---------- ----------
2 1

SQL> DELETE FROM A;

2 rows deleted.

SQL> SELECT USED_UREC,USED_UBLK
FROM V$SESSION S,
V$TRANSACTION T
WHERE S.TADDR=T.ADDR
AND S.AUDSID=sys_context('userenv', 'sessionid')
/
2 3 4 5 6
USED_UREC USED_UBLK
---------- ----------
4 1

Re: USED_UREC in v$transaction [message #219909 is a reply to message #219818] Fri, 16 February 2007 11:44 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
I noticed the following:

SQL> create table a(id number);
SQL> insert into a(id)
  2  select rownum from all_objects where rownum<51

50 rows created.

SQL>  select a.used_ublk, a.used_urec
 from v$transaction a,
 v$session b
 where a.addr=b.taddr
 and b.audsid=sys_context('userenv', 'sessionid');

 USED_UBLK  USED_UREC
---------- ----------
         1          1
SQL>


Now I delete all the rows from table a.

SQL> delete a;

50 rows deleted.

SQL>  select a.used_ublk, a.used_urec
 from v$transaction a,
 v$session b
 where a.addr=b.taddr
 and b.audsid=sys_context('userenv', 'sessionid');

 USED_UBLK  USED_UREC
---------- ----------
         2         51

SQL>


Can you explain the situation considering the above example?

Basically I wanted to know why USED_UREC showed 1 when 50 records had been inserted whereas it shows 51 when we executed the deleted statement.

Thanks
qA

[Updated on: Fri, 16 February 2007 11:46]

Report message to a moderator

Re: USED_UREC in v$transaction [message #219949 is a reply to message #219909] Fri, 16 February 2007 22:41 Go to previous messageGo to next message
oraclearora
Messages: 6
Registered: February 2007
Location: Delhi
Junior Member
Good question ..

for this you need to understand what goes to Undo segment at the time of insert and delete.

In case of a single insert statement, only the rowid(s) of the affected row goes to undo segment as there is no "previous image" of the data for INSERTS (that also to assist rollback operation, so oracle knows which rows to roll-back).

For delete, since there is "previous image" of data, oracle moves all the affected rows to undo segment. So the count of undo records is = no of rows.

But in case of INSERTS, all the rowids of inserted rows(50), can go to undo segment in 1 block and as 1 record, Oracle will show the affected rows as 1. Therefore in earlier example every insert statement was executed differently, so the no of Undo records were incremented as the statements got executed.

Let me know if i should be elaborating it further.

Thanks,
Sachin
Re: USED_UREC in v$transaction [message #219953 is a reply to message #219949] Fri, 16 February 2007 23:00 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks Sachin,
That was really a satisfactory explanation. I think you are right, because i did this after truncating table a:

SQL> select a.used_ublk, a.used_urec
  2  from v$transaction a,
  3  v$session b
  4  where a.addr=b.taddr
  5  and b.audsid=sys_context('userenv', 'sessionid');

no rows selected

SQL> insert into a(id)
  2  select rownum from all_objects where rownum<35000;

30168 rows created.

SQL> select a.used_ublk, a.used_urec
  2  from v$transaction a,
  3  v$session b
  4  where a.addr=b.taddr
  5  and b.audsid=sys_context('userenv', 'sessionid');

 USED_UBLK  USED_UREC
---------- ----------
        10        162

SQL>



1)Does that mean the rowids of all the inserted rows are accomodated in 10 undo blocks?

2)What does 162 under USED_UREC mean (I know USED_UREC mean undo records)?

It would be great if you can elaborate on these two questions?

Thanks
qA
Re: USED_UREC in v$transaction [message #219975 is a reply to message #219953] Sat, 17 February 2007 01:28 Go to previous messageGo to next message
oraclearora
Messages: 6
Registered: February 2007
Location: Delhi
Junior Member
1)Does that mean the rowids of all the inserted rows are accomodated in 10 undo blocks?

Yes - you are correct

2)What does 162 under USED_UREC mean (I know USED_UREC mean undo records)?
162 records were needed to satisfy 30168 actual records.
So effectively
30168/162 ~ 187 actual row(ids) could fit into one undo block.
I hope its more clear now.
Re: USED_UREC in v$transaction [message #219980 is a reply to message #219975] Sat, 17 February 2007 04:11 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks Sachin.
I got now what is behind the scenes.


Regards,
Anand
Previous Topic: accout is locked
Next Topic: flash drop in 10g
Goto Forum:
  


Current Time: Wed Dec 07 16:20:06 CST 2016

Total time taken to generate the page: 0.15323 seconds