OraFAQ Forum: Server Administration » USED

Home » RDBMS Server » Server Administration » USED_UREC in v$transaction

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

USED_UREC in v$transaction [message #219463]

Wed, 14 February 2007 09:11

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

Report message to a moderator

Re: USED_UREC in v$transaction [message #219466 is a reply to message #219463]

Wed, 14 February 2007 09:30

Mahesh Rajendran
Messages: 10708
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

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.

Report message to a moderator

Re: USED_UREC in v$transaction [message #219782 is a reply to message #219466]

Fri, 16 February 2007 01:07

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Hi Mahesh,
Can you help in clearing this doubt?

Thanks
Anand

Report message to a moderator

Re: USED_UREC in v$transaction [message #219818 is a reply to message #219782]

Fri, 16 February 2007 03:51

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

Report message to a moderator

Re: USED_UREC in v$transaction [message #219909 is a reply to message #219818]

Fri, 16 February 2007 11:44

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

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

Report message to a moderator

Re: USED_UREC in v$transaction [message #219953 is a reply to message #219949]

Fri, 16 February 2007 23:00

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

Report message to a moderator

Re: USED_UREC in v$transaction [message #219975 is a reply to message #219953]

Sat, 17 February 2007 01:28

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.

Report message to a moderator

Re: USED_UREC in v$transaction [message #219980 is a reply to message #219975]

Sat, 17 February 2007 04:11

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Thanks Sachin.
I got now what is behind the scenes.

Regards,
Anand

Report message to a moderator

Previous Topic:	accout is locked
Next Topic:	flash drop in 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 15:39:19 CST 2024