Re: rollback segments with NO TRANSACTION

Comments in line.

HTH,
Dave

"rpr" <renu_p_r_at_yahoo.com> wrote in message news:71802af1.0311061654.487a7c40_at_posting.google.com...
> Hello,
> I am looking at rollback segment usage and a lot of them show as
> being used but I do not see many transactions (V$TRANSACTION). Is this
> normal?

This can be normal if you have a few long running transactions locking a lot of rows.

> There is lot of waits in v$waitstat for 'undo header' and
> 'undo block'. How do I avoid that? :

These are cumulative values. So the values by themselves don't mean much. You should compare these two values against the count of consistent read gets and the number of header gets recorded in v$rollstat, as detailed in this article by Steve Adams:

http://www.ixora.com.au/q+a/0104/25152343.htm

>
> I am using query and see a lot of rollback segments with 'NO
> TRANSACTION':
That's because your join clause is incorrect.

v$lock.sid is the session identifier. v$process.pid is the process identifier. You cannot join these two columns. They're entirely different id's.

Your join clause should be:

l.sid = s.sid

>
> SELECT r.name , p.pid , p.spid , NVL (p.username, 'NO TRANSACTION'),
> s.sid, s.username, p.terminal
> FROM v$lock l, v$process p, v$rollname r, v$session s
> WHERE l.sid = p.pid(+)
> AND TRUNC (l.id1(+)/65536) = r.usn
> AND l.type(+) = 'TX'
> AND l.lmode(+) = 6
> AND p.addr = s.paddr (+)
> ORDER BY r.name
>
> Thank You
Received on Sun Nov 09 2003 - 01:11:46 CST