Re: Interpreting USED_UREC from V$TRANSACTION

To answer the tracking question:

Unless 11.2.0.3 has changed from previous versions of Oracle, the number of redo entries will be one per column dropped per row affected - at about (210 + column size) bytes per redo entry, and checking "rollback changes - undo records applied" should tell you where you are and how far you have to go.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Herring Dave - dherri" <Dave.Herring_at_acxiom.com> To: <oracle-l_at_freelists.org> Sent: Friday, July 06, 2012 6:20 PM Subject: Interpreting USED_UREC from V$TRANSACTION

As the value for USED_UREC decreased near 0 I figured that'd be it, but then it seemed to go indefinitely below 0, then some point in the past 24 hrs. the value was reset about 800 million. Like I said, repeated checks so the value to be decreasing and also a DECODE(BITAND(t.flag,128), 0, NULL, 'Rollback') returns "Rollback".

So how can the value go negative and then jump back to 800 million? I'm pretty sure it didn't "wrap" and transactions can't span undo segments. This also makes it VERY difficult to figure out how long this will take.

The best I've come up with is comparing "redo entries" stat from AWR before the rollback with "rollback changes - undo records" stat in AWR during this rollback process. Should they eventually be equal, assuming little to no other activity on the DB?

--
http://www.freelists.org/webpage/oracle-l