Re: What sql is using undo tablespace

From: Adam Musch <ahmusch_at_gmail.com>
Date: Tue, 13 May 2014 08:56:03 -0500
Message-ID: <CAH4Zrrt-4zMVZXWtCJmF4a27TVbxY7Ci-2aJRe+k9a7OMkCPhQ_at_mail.gmail.com>

That shows who generated the undo, which is often useful; what it doesn't show is who's referencing the undo to generate read consistent blocks for other statements, and I'm not sure that information's tracked anywhere that's queryable.

On Tue, May 13, 2014 at 3:46 AM, sumit Tyagi <dba.tyagisumit_at_gmail.com>wrote:

> undo is just generated by "a query", we do not track undo at a query level
> undo is generated by transactions
>
> v$transaction has a column used_ublk that tells you how much undo a given
> transaction has
> generated....
>
> to figure out how much undo (blocks or MB) a user session is using as of a
> "point in time" sure you can find :
>
> as soon as they commit, v$transaction empties for them.
>
> so to find information use v$sesstat joined to v$statname, look for name
> like '%undo%', you'll find useful information there - it is cumulative for
> a session.
>
> select s.sid,
> s.username,
> sum(ss.value) / 1024 / 1024 as undo_size_mb
> from v$sesstat ss
> join v$session s on s.sid = ss.sid
> join v$statname stat on stat.statistic# = ss.statistic#
> where stat.name = 'undo change vector size'
> and s.type <> 'BACKGROUND'
> and s.username IS NOT NULL
> group by s.sid, s.username;
>

-- 
Adam Musch
ahmusch_at_gmail.com

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

Received on Tue May 13 2014 - 15:56:03 CEST