Re: What sql is using undo tablespace

From: Justin Mungal <justin_at_n0de.ws>
Date: Tue, 13 May 2014 10:47:57 -0500
Message-ID: <CAO9=aUyvHiYtSvGnkh6+qNvuFs3iDZTbde5MzpZTQGH5yCwCrg_at_mail.gmail.com>

You can use the scn_to_timestamp() function to make that SCN slightly less exotic.

On Tue, May 13, 2014 at 10:42 AM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
>
> v$sessstat (joined to v$statname) gives you three statistics at the
> session level (not statement, of course).
> data blocks consistent reads - undo records applied
> this will usually be similar to
> consistent changes
>
> Who's rolling back
> rollback changes - undo records applied
>
> A slightly exotic one about finding (upper bound) commit SCNs for
> transactions that committed a "long" time in the past.
> transaction tables consistent reads - undo records applied
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Adam Musch [ahmusch_at_gmail.com]
> *Sent:* 13 May 2014 14:56
> *To:* dba.tyagisumit_at_gmail.com
> *Cc:* exriscer_at_gmail.com; JDunn_at_sefas.com; oracle-l_at_freelists.org
> *Subject:* Re: What sql is using undo tablespace
>
> 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 - 17:47:57 CEST