RE: What sql is using undo tablespace

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 May 2014 15:52:13 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DF3CEC_at_exmbx05.thus.corp>

It's the requirement and mechanism for finding old commit SCNs that's slightly exotic, not the representation or meaning of the SCN

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Justin Mungal [justin_at_n0de.ws]
Sent: 13 May 2014 16:47
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: What sql is using undo tablespace

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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Adam Musch [ahmusch_at_gmail.com<mailto:ahmusch_at_gmail.com>] Sent: 13 May 2014 14:56
To: dba.tyagisumit_at_gmail.com<mailto:dba.tyagisumit_at_gmail.com> Cc: exriscer_at_gmail.com<mailto:exriscer_at_gmail.com>; JDunn_at_sefas.com<mailto:JDunn_at_sefas.com>; oracle-l_at_freelists.org<mailto: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<mailto: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<http://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<mailto:ahmusch_at_gmail.com>


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

Received on Tue May 13 2014 - 17:52:13 CEST