Re: v$rollstat rssize vs v$transaction used_ublk

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 31 Jul 2014 14:44:05 -0700
Message-ID: <CAA2DszzvD3yrNCzPQLo+DQUj7+FHO7iM9XoeAPQSiKmBPTsmFA_at_mail.gmail.com>

Hi Sundar
  Yes, used_ublk is the correct column to query to identify session's undo usage.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Thu, Jul 31, 2014 at 5:45 AM, Sundar Mahadevan < sundarmahadevan82_at_gmail.com> wrote:

> Hi All,
> I am trying to find out which session is using the undo tablespace. I
> notice 72GB of active undo being used. But I am not able to come to any
> conclusion as to which one should be used to map undo usage to a session?
> Is it rssize from v$rollstat in bytes or is it used_ublk from v$transaction
> in number of blocks. From the numbers below, I am inclined to think that
> used_ublk from v$transaction is closer to 72GB active undo size. Please
> clarify. Thanks.
>
> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from
> dba_undo_extents group by tablespace_name, status;
>
> TABLESPACE_NAME STATUS GB
> ------------------------------ --------- --------------------
> UNDOTBS1 UNEXPIRED 24.17645263671875
> UNDOTBS1 EXPIRED .00128173828125
> UNDOTBS1 ACTIVE 72.9659423828125
>
> 3 rows selected.
>
> SQL Statement which produced this data:
> select 'ROLLBIG' rollbig,a.sid, a.username,a.osuser, a.machine, b.xidusn,
> b.used_urec, b.used_ublk, b.used_ublk * 8192/1024/1024 UNDO_USED_IN_MB ,
> b.used_ublk *8192/1024/1024/1024 UNDO_USED_IN_GB , d.name,c.extents,c.rssize/1024/1024
> RSsize_in_MB, c.rssize/1024/1024/1024 RSsize_in_GB
> from v$session a, v$transaction b,v$rollstat c,v$rollname d
> where a.saddr=b.ses_addr
> and b.xidusn=c.usn
> and b.xidusn=d.usn
>
> ROLLBIG|SID|USERNAME|OSUSER|MACHINE|XIDUSN|USED_UREC|USED_UBLK|
> *UNDO_USED_IN_MB|UNDO_USED_IN_GB*|NAME|EXTENTS|*RSSIZE_IN_MB|RSSIZE_IN_GB*
> ROLLBIG|861|ANALYTICAL|ABCD|11QL1W1-DER|11|51045872|9571102|
> *74774.234375|73.0217132568359*|_SYSSMU11_941321016$|1447|
> *1053.0859375|1.02840423583984*
>
> SQL Statement which produced this data:
> select * from v$transaction
>
>
> ADDR|XIDUSN|XIDSLOT|XIDSQN|UBAFIL|UBABLK|UBASQN|UBAREC|STATUS|START_TIME|START_SCNB|START_SCNW|START_UEXT|START_UBAFIL|START_UBABLK|START_UBASQN|START_UBAREC|SES_ADDR|FLAG|SPACE|RECURSIVE|NOUNDO|PTX|NAME|PRV_XIDUSN|PRV_XIDSLT|PRV_XIDSQN|PTX_XIDUSN|PTX_XIDSLT|PTX_XIDSQN|DSCN-B|DSCN-W|USED_UBLK|USED_UREC|LOG_IO|PHY_IO|CR_GET|CR_CHANGE|START_DATE|DSCN_BASE|DSCN_WRAP|START_SCN|DEPENDENT_SCN|XID|PRV_XID|PTX_XID
> 00000049B0922F28|11|9|19495922|689|2956218|20397|3|ACTIVE|07/29/14
> 17:20:59|674640805|1253|1022|763|174002|18950|12|0000004A01E0CC50|3587|NO|NO|NO|NO|null|0|0|0|0|0|0|0|0|9573651|51059468|204093255|4708220|104290716|45873068|29-Jul-2014
> 5:20:59
> PM|0|0|5382268662693|0|0B000900F27B2901|0000000000000000|0000000000000000
>
>

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

Received on Thu Jul 31 2014 - 23:44:05 CEST